sql: lib/pq confused by unicode characters in text[], while psql is OK with them

[dmkret]

russian character \u0422 'Т' (capital te) not saved well if it's inside of a string array.

To Reproduce

import "github.com/lib/pq"
...
rows, err := db.Query("SELECT ARRAY ['У, 'Л', 'Т']")
for rows.Next() {
    var value pq.StringArray
    rows.Scan(&value)
    fmt.Println(value)
}

output for cockroachdb> {"У", "Л" ,"\u0422"}
output for postgresql> {"У", "Л", "Т"}

Environment:

• CockroachDB version: 2.0.6
• Client app: cockroach sql, golang: pq

Add any other context about the problem here.

[knz]

Thanks Anton for your problem report.
The problem is strictly in the Go driver lib/pq not in CockroachDB (note: cockroach sql also uses lib/pq and thus is affected equally by its bugs).

If you use the psql command from PostgreSQL and connect it to a CockroachDB server, you will see the right output:

kena@kenabook ~/cockroach % psql -U root -h localhost -p 26257
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04), server 9.5.0)
Type "help" for help.

root=# SELECT ARRAY ['У', 'Л', 'Т'];
     array
---------------
 {"У","Л","Т"}
(1 row)

Note also that we are aware of numerous problems with lib/pq. At this time we recommend using pgx instead, although the particular Unicode situation you are facing may not be handled by pgx either (you'd need to check)

[dmkret]

@knz, thanks for reply!

I have the same issue with psql. Probably it is a store processing problem.

Reproduce

Create table with data

root@81142ab6c98d:/cockroach# ./cockroach sql --insecure           
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Server version: CockroachDB CCL v2.0.6 (x86_64-unknown-linux-gnu, built 2018/10/01 13:59:40, go1.10) (same version as client)
# Cluster ID: b60deba7-37ed-43f6-986f-b3e20e62ef54
#
# Enter \? for a brief introduction.
#
warning: no current database set. Use SET database = <dbname> to change, CREATE DATABASE to make a new database.
root@:26257/> create database test;
CREATE DATABASE

Time: 10.19165ms

root@:26257/> set database = test;
SET

Time: 295.408µs

root@:26257/test> create table test (test text[] NOT NULL);
CREATE TABLE

Time: 13.422543ms

root@:26257/test> insert into test values ('Т');
pq: value type string doesn't match type ARRAY of column "test"
root@:26257/test> insert into test values (ARRAY ['Т']);
INSERT 1

Time: 2.62925ms

root@:26257/test> select * from test;
+------------+
|    test    |
+------------+
| {"\u0422"} |
+------------+
(1 row)

Time: 3.549855ms

Select data using psql

root@81142ab6c98d:/cockroach# psql -h 127.0.0.1 -d test -p 26257
psql (9.4.19, server 9.5.0)
WARNING: psql major version 9.4, server major version 9.5.
         Some psql features might not work.
Type "help" for help.

test=> select * from test;
    test    
------------
 {"\u0422"}
(1 row)

Also I have the same issue with golang: pgx driver.

[knz]

1. That's something different, your previous example was using an array[] expression only and not storing anything in the table.

Nevertheless, you can verify that the issue is only in the client/server protocol and not in the CockroachDB store: select length(test[1]) from test should still return 1.

2. The particular client/server protocol problem revealed in your test is a known limitation of CockroachDB 2.0 and has been fixed in the upcoming 2.1 release.

[dmkret]

Yes select length(test[1]) from test returning 1.

root@:26257/test> select length(test[1]) from test;
+--------+
| length |
+--------+
|      1 |
+--------+
(1 row)

Time: 1.664317ms

[dmkret]

Since CockroachDB version: 2.1.0 it's ok with lib/pq.

[knz]

Thank you for confirming.

@mjibson btw this is an example of what we were discussing last week, encoding behavior is different but is valid.