INSERT with empty VALUES, found in Python sqlalchemy

[mcronce]

Overview of the Issue

MySQL allows you to specify an INSERT with empty VALUES as long as all columns have a valid default. For example, the following is valid:

mysql> CREATE TABLE customer (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255)
);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO customer () VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM customer;
+----+------+-------------+
| id | name | description |
+----+------+-------------+
|  1 | NULL | NULL        |
+----+------+-------------+
1 row in set (0.00 sec)

But the following is not:

mysql> CREATE TABLE customer (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255)
);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO customer () VALUES ();
ERROR 1364 (HY000): Field 'name' doesn't have a default value

Connected to Vitess, we get a syntax error with the same INSERT:

ERROR 1105 (HY000): vtgate: http://mc-test1-mc1local-vtgate-afe64d24-747ddd7f88-mmd45:15000/: syntax error at position 24

Reproduction Steps

Fire up a MySQL CLI connected to Vitess, and run the following:

CREATE TABLE customer (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255)
);
INSERT INTO customer () VALUES ();

Binary version

Version: f315cb35e (Git branch 'master') built on Fri Aug 28 13:21:19 UTC 2020

Operating system and Environment details

In the vtgate container:

vitess@mc-test1-mc1local-vtgate-afe64d24-747ddd7f88-fhwrb:/$ cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
NAME="Debian GNU/Linux"
VERSION_ID="9"
VERSION="9 (stretch)"
VERSION_CODENAME=stretch
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"
vitess@mc-test1-mc1local-vtgate-afe64d24-747ddd7f88-fhwrb:/$ uname -sr
Linux 4.19.107
vitess@mc-test1-mc1local-vtgate-afe64d24-747ddd7f88-fhwrb:/$ uname -m
x86_64

On the host, where I'm running the client:

[mc@mc-thinkpad todos]$ cat /etc/os-release 
NAME="Manjaro Linux"
ID=manjaro
ID_LIKE=arch
BUILD_ID=rolling
PRETTY_NAME="Manjaro Linux"
ANSI_COLOR="32;2;24;144;200"
HOME_URL="https://manjaro.org/"
DOCUMENTATION_URL="https://wiki.manjaro.org/"
SUPPORT_URL="https://manjaro.org/"
BUG_REPORT_URL="https://bugs.manjaro.org/"
LOGO=manjarolinux
[mc@mc-thinkpad todos]$ uname -sr
Linux 5.6.16-1-MANJARO
[mc@mc-thinkpad todos]$ uname -m
x86_64

Log Fragments

I get the following log messages each time I run an unpatched sqlx migrate run:

I0828 17:49:48.430018       1 set.go:250] Ignored inapplicable SET time_zone = '+00:00'
E0828 17:49:48.436744       1 vtgate.go:452] Execute: empty statement, request: map[BindVariables:map[] Session:shard_sessions:<target:<keyspace:"test1" shard:"-" tablet_type:MASTER > tablet_alias:<cell:"mc1local" uid:1662884826 > reserved_id:1598629919380903914 > autocommit:true target_string:"test1" options:<included_fields:ALL client_found_rows:true > system_variables:<key:"sql_mode" value:"'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ENGINE_SUBSTITUTION'" > row_count:-1 in_reserved_conn:true Sql:]

[systay]

Guessing you switched around the two CREATE TABLE statements, right? The first one should allow name to have a null value, is my guess

[mcronce]

Yeah, the CREATE TABLE with NOT NULL was just to highlight a totally separate problem (which is obviously not a Vitess bug at all) that I had ruled out :)