[MDEV-20820] SHOW CREATE TABLE does not parenthesize default expressions Created: 2019-10-13  Updated: 2019-10-16  Resolved: 2019-10-14

Status: Closed
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.2.23, 10.1.40, 10.3.14, 10.4.6, 10.4.8
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Jonathan Shapiro Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

MacOS, Windows


 Description   

When printing the CREATE TABLE to reconstruct a table, the SHOW CREATE TABLE command does not wrap default expressions with parenthesis.

I'd enter this as 'trivial', except that it breaks the DDL parsers for a surprisingly large number of GUI-style management tools, including SQL Workbench, SEQUEL Pro, and a surprising number of others. Wrapping the expression with parens on output should be non-breaking, as parens are always permitted in this context. A widespread practical impact is that tables defined with a text-to-binary16 UUID initializer

Example from MariaDB 10.4.6: note the output for the guid column in:

| station | CREATE TABLE `station` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(45) NOT NULL,
 
  `where` varchar(45) NOT NULL,
 
  `description` varchar(255) DEFAULT NULL,
 
  `guid` binary(16) NOT NULL DEFAULT unhex(replace(uuid(),'-','')),
 
  PRIMARY KEY (`id`,`where`),
 
  UNIQUE KEY `name_UNIQUE` (`name`),
 
  UNIQUE KEY `ID_UNIQUE` (`id`),
 
  UNIQUE KEY `guid_UNIQUE` (`guid`)
 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |



 Comments   
Comment by Sergei Golubchik [ 2019-10-14 ]

It looks like neither MariaDB, nor PostgreSQL, not SQL Server require parentheses there. Neither does SQL Standard (in all that limited subset of expressions that it allows in DEFAULT).

Only MySQL wants parentheses in DEFAULT. But MySQL was the last to add this feature, it appeared only in 8.0.13, years after other databases got it. And this MySQL requirement causes weird behavior like this:

this works

 
CREATE TABLE t2 (b CHAR(5) DEFAULT 'abc');

but this doesn't

 
CREATE TABLE t2 (b TEXT DEFAULT 'abc');

while this inexplicably works again

 
CREATE TABLE t2 (b TEXT DEFAULT ('abc'));

So, I'd think that GUI tools should support the mainstream syntax, and MySQL may want to remove this illogical limitation.

Comment by Jonathan Shapiro [ 2019-10-15 ]

Sergei:

The issue that actually concerns me here is that all of the GUI tools that talk to the databases botch the expression parsing in DEFAULT if the parens are not emitted. The consequence is that they are unable to read the table structure. When I belatedly discovered this was a problem in MySQL Workbench, I went looking for an alternative tool that could handle the un-parenthesized output syntax. HeidiSQL, MySQL Workbench, Sequel Pro, DBeaver, and every other frontend tool that I tried appear to get the expression parsing wrong. Several have other amusing mishandlings of DEFAULT expressions in their respective editors. The practical consequence is that none of them can read the SHOW CREATE TABLE output. The other practical consequence is that anyone who uses any sort of interesting default initializer is driven off of the tools. In my case, the initializer in question was attempting to convert a UUID to a binary(16) in the obvious way. None of the GUI tools can handle the resulting DEFAULT expression.

And it's every bit as weird as you say, because ... DEFAULT UUID() appears to work in a bunch of the tools while ... DEFAULT UNHEX(REPLACE(UUID(),'-','')) does not.

From a purist standpoint, I agree with you that the correct thing to do is to tell people to fix the tools. As a PL and OS engineer and researcher, I lean that way as well. But from a "help people get the job done" standpoint, emitting the parens in the SHOW CREATE TABLE output is both correct and harmless.

Not sure if that's reason to re-open the issue, but I at least wanted to be clear about what the problem was, how far it extends, and the impact for those who prefer GUI tools to native SQL statements.

Comment by Sergei Golubchik [ 2019-10-16 ]

May be, report bugs for those tools with a link to this issue? Hoping they at least explain the logic they've implemented.

I'd very interested to know the justification for

DEFAULT UUID() appears to work in a bunch of the tools while DEFAULT UNHEX(REPLACE(UUID(),'-','')) does not.

and for

TEXT DEFAULT ('abc') works, but TEXT DEFAULT 'abc' does not, while CHAR(100) DEFAULT 'abc' does, again

(I know the internal technical reason for that, I'm just saying it makes no sense from the user point of view).

Generated at Thu Feb 08 09:02:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.