unicode whitespace is removed from unquoted elements of text[] columns

[kevinkyyro]

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/PgArray.java

Lines 489 to 491 in b1b1afb

	} else if (!insideString && Character.isWhitespace(chars[i])) {
	// white space
	continue;

Versions

PgJDBC driver: 42.2.2
PostgreSQL server: 10.4 (Debian 10.4-2.pgdg90+1)
Java: 1.8.0_161-b12

Context

I have a table with a text[] column and wrote some property tests to verify selecting after inserting resulted in the same value, and I came across a strange issue.

For some reason, the server returns text[] columns without quoting elements that only contain non-ASCII UTF-8 whitespace (ex: \u2001). As a result, those elements have all their whitespace (UTF-8 or otherwise) stripped out at the linked line, which I identified by stepping through with the debugger.

I am reporting this as a driver bug because it seems fixable there, but it may also be undesirable behavior for the server.

SQL Example

# insert into test_strings (strings)  values (U&'{a\2001b, a b}'::varchar[]);
INSERT 0 1
# select strings from test_strings;
         strings         
-------------------------
 {a b,"a b"}

Code example

Here's an example based on my setup, which is using jooq (3.11.0).

It should be a pretty straight forward conversion to java 8, but in either case running this would require either wrapping a main method or throwing it into a script and getting jooq on the classpath. I'm happy to help with that, but I assume you will have a preferred way of doing that.

import org.jooq.impl.DSL
import org.jooq.DSLContext
import org.jooq.impl.SQLDataType
import org.jooq.SQLDialect
import java.sql.DriverManager

val username: String = "insight"
val password: String = "local"
val table: String = "example" // replace me

Class.forName("org.postgresql.Driver") // force class-loading
val db: DSLContext = DSL.using(
  DriverManager.getConnection("jdbc:postgresql://localhost:5432/insight", username, password),
  SQLDialect.POSTGRES)

db.dropTableIfExists(table).execute()

db.createTable(table)
  .as(db.select(
    DSL.value(
      Array("a\u2001b", "a b"),
      DSL.field("strings", SQLDataType.VARCHAR.getArrayDataType()))))
  .execute()

println(db.select().from(table).fetch())

The output should be as follows, demonstrating that the whitespace on the first array element has been removed (the printing format here varies from psql, so neither are quoted, but that's not a significant detail):

+---------+
|varchar  |
+---------+
|[ab, a b]|
+---------+

[vlsi]

@kevinkyyro , nice catch.

it may also be undesirable behavior for the server.

+1

I'm happy to help with that, but I assume you will have a preferred way of doing that.

Could you share the tests?
We are fine to have tests written in Scala, etc.

For instance: https://github.com/pgjdbc/pgjdbc/tree/master/test-anorm-sbt

[vlsi]

Here's relevant code from the backend: https://github.com/postgres/postgres/blob/f2c587067a8eb9cf1c8f009262381a6576ba3dd0/src/backend/utils/adt/arrayfuncs.c#L421-L438

[bokken]

The comments there indicate there used to be different behavior, possibly similar to what java is doing now.
Should java driver now be updated to just consider the 6 specific characters as white space?

[bokken]

#1194 Includes unit tests for arrays. Adding some cases including non ascii whitespace should be fairly easy.

[davecramer]

Here's the comments from the commit on the backend.

array_in discards unquoted leading and trailing whitespace in array values,
while array_out is careful to quote array elements that contain whitespace.
This is problematic when the definition of "whitespace" varies between
locales: array_in could drop characters that were meant to be part of the
value.  To avoid that, lock down "whitespace" to mean only the traditional
six ASCII space characters.

I think we either stick with the backend behaviour. Unless of course we have a convincing argument to change that as well ?

[bokken]

@vlsi I would propose that we change the java driver to only look for the 6 specific chars (PR #1266).

Would it also make sense to have the back end more liberally use quotes?
While we certainly do not want locale dependent behavior, the unicode whitespace definition is not locale dependent. Does the backend have easy access to the unicode definitions, specifically:

• Spaces - General category "Zs" in the Unicode specification.
• New lines - General category "Zl" in the Unicode specification.
• Paragraphs - General category "Zp" in the Unicode specification.

Is there any downside to erring on side of more liberal quoting when returning values?

[kevinkyyro]

Just want to point out that regardless of the set of characters considered whitespace, it says that it only trims leading and trailing whitespace on input. The driver should certainly not be doing anything to non-leading-or-trailing, non-escape characters, correct? It seems likely that no whitespace should be dropped at all on output, since it was already dropped on input. Looking at my example above, the delimiter seems to be a comma without any added space (hard to check for sure on my phone).