CREATE TABLE statements use format unsupported on MySQL < 8.x

[arthurschreiber]

Overview of the Issue

In #9011, the SQL generated for CREATE TABLE statements was changed to generate expression statements for default values:

create table t2 (b blob default 'abc');

was changed to:

create table t2 (b blob default ('abc'));

Unfortunately, this syntax is only supported on MySQL 8.0 and later, and is unsupported on earlier MySQL versions like 5.7.

mysql> create table t2 (b blob default ('abc'));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('abc'))' at line 1

Vitess should not translate between one or the other, and just pass through whatever syntax the user is passing in.

Reproduction Steps

Steps to reproduce this issue:

1. Run a version of Vitess that includes the changes from Added brackets in default value if column is of type TEXT, BLOB, GEOMETRY or JSON #9011 on MySQL 5.7:

2. Try creating the following table:

   create table t2 (b blob default 'abc');

[arthurschreiber]

/cc @ritwizsinha @GuptaManan100 For visibility.

[harshit-gangal]

@arthurschreiber in 5.7, it fails on both the statements. what is the expected result?

mysql [localhost:5729] {msandbox} (test) > create table t2 (b blob default 'abc');
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value

mysql [localhost:5729] {msandbox} (test) > create table t2 (b blob default ('abc'));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('abc'))' at line 1

[arthurschreiber]

@harshit-gangal Ah, I'm sorry. This example was taken from the Vitess sql parser tests and I didn't check if it was actually valid.

The case we're running into is this:

create table t2 (b JSON DEFAULT NULL);

gets turned into

create table t2 (b JSON DEFAULT (null));

and the latter is only valid on MySQL 5.7 MySQL 8.0.

[harshit-gangal]

create table t2 (b JSON DEFAULT (null));

got it, I think you mean the later is only valid in MySQL 8.0

[arthurschreiber]

Yeah. 😅 Updated my previous comment accordingly!