[MDEV-29561] SHOW CREATE TABLE produces syntactically incorrect structure Created: 2022-09-17  Updated: 2022-12-17  Resolved: 2022-09-21

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: N/A
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.0

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Problem/Incident
is caused by MDEV-29446 Change SHOW CREATE TABLE to display d... Closed

 Description   

Set affected versions to 10.3-10.5 because the commit hasn't been merged up yet.

create table t (a enum ('x') character set binary);
 
show create table t;
 
let $def= query_get_value(show create table t,'Create Table',1);
 
drop table t;
 
eval $def;
 
 
 
# Cleanup
 
drop table t;

10.3 32bab2ce

 
show create table t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `a` enum('x') CHARACTER SET binary COLLATE binary DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
drop table t;
 
CREATE TABLE `t` (
 
  `a` enum('x') CHARACTER SET binary COLLATE binary DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
 
bug.t2                                   [ fail ]
 
        Test ended at 2022-09-17 18:54:54
 
 
 
CURRENT_TEST: bug.t2
 
mysqltest: At line 5: query '$def' failed: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'binary DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swe...' at line 2

The failure started happening after this commit in 10.3:

commit f1544424de2b8c9d1c3faefbbdd15543db7dfd12
 
Author: Alexander Barkov
 
Date:   Fri Sep 2 17:32:14 2022 +0400
 
 
 
    MDEV-29446 Change SHOW CREATE TABLE to display default collation



 Comments   
Comment by Alexander Barkov [ 2022-09-18 ]

This script:

DROP TABLE t1;
 
CREATE TABLE `t1` (a INT ) CHARSET=binary;
 
SHOW CREATE TABLE t1;

also produces a syntactically bad output:

CREATE TABLE `t1` (
 
  `a` int(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=binary COLLATE=binary

Comment by Alexander Barkov [ 2022-09-18 ]

So does this:

DROP DATABASE IF EXIST db1;
 
CREATE DATABASE db1 CHARACTER SET BINARY;
 
SHOW CREATE DATABASE db1;


CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET binary COLLATE binary */;

Comment by Alexander Barkov [ 2022-09-20 ]

So does this:

MariaDB [test]> CREATE OR REPLACE FUNCTION f1() RETURNS ENUM('a') CHARACTER SET binary RETURN NULL; 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [test]> SHOW CREATE FUNCTION f1;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'binary
 
RETURN NULL' at line 1
 
MariaDB [test]> SELECT returns FROM mysql.proc WHERE name='f1';
 
+-----------------------------------------+
 
| returns                                 |
 
+-----------------------------------------+
 
| enum('a') CHARSET binary COLLATE binary |
 
+-----------------------------------------+
 
1 row in set (0.001 sec)

Notice, the erroneous syntax gets written to mysql.proc, so SHOW CREATE FUNCTION can't parse the syntax and returns an error.

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