sql_mode='ANSI' doesn't allow ANSI syntax

[mcronce]

Overview of the Issue

Found while testing Drupal. All indications were that they were trying to submit a query that looked like CREATE TABLE {table_name} ... (which made no sense) until I pulled a packet capture - turns out that the actual query going over the wire (and failing) is:

CREATE TABLE "drupal_install_test" (id int NOT NULL PRIMARY KEY)

Preceded by:

SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY'

which reports success.

As an aside: Should this be a feature request instead of a bug?

Reproduction Steps

SET sql_mode = 'ANSI';
-- Query OK, 0 rows affected
CREATE TABLE "drupal_install_test" (id int NOT NULL PRIMARY KEY);
-- ERROR 1105 (HY000): syntax error at position 35 near 'drupal_install_test'

Binary version

For vttestserver:mysql57 and vttestserver:mysql80, respectively:

$ vtcombo --version
Version: 10.0.0-SNAPSHOT (Git revision 415f3765a branch 'master') built on Wed Mar 31 08:35:37 UTC 2021 by vitess@7a1cb8a24287 using go1.15.6 linux/amd64

$ vtcombo --version
Version: 10.0.0-SNAPSHOT (Git revision 415f3765a branch 'master') built on Wed Mar 31 13:48:43 UTC 2021 by vitess@00c264007ba8 using go1.15.6 linux/amd64

Operating system and Environment details

In one of the vttestserver containers:

$ cat /etc/os-release 
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

$ uname -sr
Linux 5.10.23-1-MANJARO

$ uname -m
x86_64

On the host:

$ cat /etc/os-release
NAME="Manjaro Linux"
ID=manjaro
ID_LIKE=arch
BUILD_ID=rolling
PRETTY_NAME="Manjaro Linux"
ANSI_COLOR="32;1;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

$ uname -sr
Linux 5.10.23-1-MANJARO

$ uname -m
x86_64

Log Fragments

Nothing from when these queries are executed

[mcronce]

PS: I have pcaps I can attach if it helps, but given how easy it is to reproduce, I doubt they'll be needed :)

[aquarapid]

Note that ANSI is a special mode that expands to the following individual sql_mode values: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY

The one that matters in this case is ANSI_QUOTES

[chriscct7]

We ran into this same thing while trying to migrate a project that used the Medoo PHP PDO wrapper library, see catfan/Medoo#1044