tiup sql client corrupts SQL column names

[morgo]

Bug Report

Credit for this bug report goes to @Alkaagr81

1. What did you do?

create table t1 (c1 int, c2 char(6), c3 int);
create table t2 (c1 int, c2 char(6));
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
drop table t1, t2;

2. What did you expect to see?

Using mysql client, no error:

tidb> create table t1 (c1 int, c2 char(6), c3 int);
Query OK, 0 rows affected (0.06 sec)

tidb> create table t2 (c1 int, c2 char(6));
Query OK, 0 rows affected (0.07 sec)

tidb> insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

tidb> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

tidb> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

tidb> drop table t1, t2;

3. What did you see instead?

Using Tiup SQL client:

my:[email protected]:4000=> use test;
USE
my:[email protected]:4000=> create table t1 (c1 int, c2 char(6), c3 int);
CREATE TABLE
my:[email protected]:4000=> create table t2 (c1 int, c2 char(6));
CREATE TABLE
my:[email protected]:4000=> insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
error: mysql: 1054: Unknown column 't1c2-1' in 'field list'
my:[email protected]:4000=> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
error: mysql: 1054: Unknown column 't2c2-1' in 'field list'
my:[email protected]:4000=> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
error: mysql: 1054: Unknown column 't2c2-1' in 'field list'
my:[email protected]:4000=> drop table t1, t2;

4. What version of TiUP are you using (tiup --version)?

morgo@ubuntu:~/go/src/github.com/mysql/mysql-server$ tiup --version
1.4.0 tiup
Go Version: go1.16.2
Git Ref: v1.4.0
GitHash: eb335e6ad9d0dc19d1c43db422cd409d62bc3cea

[dveeden]

The reason for this is that usql set the SQL Mode to "ANSI" by default to more closely mimic the behavior of psql.

https://github.com/xo/usql/blob/7132de43273ea9b6fbe695ea3229d25d8e977bbc/drivers/mysql/mysql.go#L30

my:[email protected]:4000=> USE test;
USE
my:[email protected]:4000=> \conninfo
Connected with driver mysql (root@tcp(127.0.0.1:4000)/?loc=Local&parseTime=true&sql_mode=ansi)
my:[email protected]:4000=> SELECT @@session.sql_mode;
                               @@session.sql_mode                               
--------------------------------------------------------------------------------
 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI 
(1 row)

my:[email protected]:4000=> create table t1 (c1 int, c2 char(6), c3 int);
CREATE TABLE
my:[email protected]:4000=> create table t2 (c1 int, c2 char(6));
CREATE TABLE
my:[email protected]:4000=> insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
error: mysql: 1054: Unknown column 't1c2-1' in 'field list'
my:[email protected]:4000=> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
error: mysql: 1054: Unknown column 't2c2-1' in 'field list'
my:[email protected]:4000=> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
error: mysql: 1054: Unknown column 't2c2-1' in 'field list'
my:[email protected]:4000=> drop table t1, t2;
DROP TABLE
my:[email protected]:4000=> SET sql_mode='';
SET
my:[email protected]:4000=> create table t1 (c1 int, c2 char(6), c3 int);
CREATE TABLE
my:[email protected]:4000=> create table t2 (c1 int, c2 char(6));
CREATE TABLE
my:[email protected]:4000=> insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
INSERT 2
my:[email protected]:4000=> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
UPDATE
my:[email protected]:4000=> update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
UPDATE
my:[email protected]:4000=> drop table t1, t2;
DROP TABLE

Other differences between MySQL Client and usql are:

• No timing info by default (\timing to enable this)
• Output formatting differs (\pset border 2 to change this)
• Different time formatting (\pset time "2006-01-02 15:04:05.000000" to change this)
• Different SQL Mode (SET sql_mode='' or something like \c mysql root@tcp(127.0.0.1:4000)/test?sql_mode=STRICT_ALL_TABLES

my:[email protected]:4000=> SELECT NOW(), 'foo' || 'bar';
           NOW()           | 'foo' || 'bar' 
---------------------------+----------------
 2021-12-29T08:26:01+01:00 | foobar 
(1 row)

my:[email protected]:4000=> \timing
Timing is on.
my:[email protected]:4000=> \pset border 2
Border style is 2.
my:[email protected]:4000=> \pset time "2006-01-02 15:04:05.000000"
Time display is "2006-01-02 15:04:05.000000" ("2006-01-02 15:04:05.000000").
my:[email protected]:4000=> SET sql_mode='';
SET
my:[email protected]:4000=> SELECT NOW(), 'foo' || 'bar';
+----------------------------+----------------+
|           NOW()            | 'foo' || 'bar' |
+----------------------------+----------------+
| 2021-12-29 08:26:33.000000 |              0 |
+----------------------------+----------------+
(1 row)

Time: [18.896] ms

Another case that seems problematic:

(mysql)=> SELECT CAST("2020-11-02" AS DATE);
error: mysql: sql: Scan error on column index 0, name "CAST(\"2020-11-02\" AS DATE)": unsupported Scan, storing driver.Value type []uint8 into type *time.Time
(mysql)=> SELECT CAST("2020-11-02" AS DATE) + 0;
 CAST("2020-11-02" AS DATE) + 0 
--------------------------------
                       20201102 
(1 row)

[dveeden]

Please check https://github.com/xo/usql/issues/309 to see why this is happening and why changing the SQL mode might cause other issues with usql.