Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.1.67, 5.2.14, 5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL)
Description
Conditional statement execution using special comment syntax
link comment syntax
does not work as expected: It is ok if the commented statement is not executed et all. But if executed, some parts of the command may be ignored, in particular CONSTRAINT attributes are not honored.
|
|
MariaDB [bugreport]> select version(); |
+----------------+ |
| version() |
|
+----------------+ |
| 5.5.38-MariaDB |
|
+----------------+ |
1 row in set (0.00 sec) |
MariaDB [bugreport]> # Show that comment execution is honored: |
MariaDB [bugreport]> /*!60101 select 1 */; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [bugreport]> /*!50101 select 1 */; |
+---+ |
| 1 |
|
+---+ |
| 1 |
|
+---+ |
1 row in set (0.00 sec) |
|
|
MariaDB [bugreport]> # Prepare for bug demo: |
MariaDB [bugreport]> # (1) unconditionally:
|
MariaDB [bugreport]> create table t1 (id int not null primary key auto_increment) engine = innodb ; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [bugreport]> # (2) conditionally via executable comment:
|
MariaDB [bugreport]> /*!50101 create table t2 (id int not null primary key auto_increment) engine = innodb */ ; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [bugreport]> show create table t1\G |
*************************** 1. row ***************************
|
Table: t1 |
Create Table: CREATE TABLE `t1` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec) |
|
|
MariaDB [bugreport]> show create table t2\G |
*************************** 1. row ***************************
|
Table: t2 |
Create Table: CREATE TABLE `t2` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec) |
|
|
MariaDB [bugreport]> # All fine for now. |
MariaDB [bugreport]> # Now for tables that refer to t1.ID: |
MariaDB [bugreport]> # (1) unconditionally:
|
MariaDB [bugreport]> create table t3 (id int not null primary key auto_increment, rid int not null, CONSTRAINT a FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb ; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [bugreport]> # (2) conditionally via executable comment:
|
MariaDB [bugreport]> /*!50101 create table t4 (id int not null primary key auto_increment, rid int not null, CONSTRAINT b FOREIGN KEY (rid) REFERENCES t1(id)) engine = innodb */; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [bugreport]> show create table t3\G |
*************************** 1. row ***************************
|
Table: t3 |
Create Table: CREATE TABLE `t3` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`rid` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
KEY `a` (`rid`), |
CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec) |
|
|
MariaDB [bugreport]> show create table t4\G |
*************************** 1. row ***************************
|
Table: t4 |
Create Table: CREATE TABLE `t4` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`rid` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
KEY `b` (`rid`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec) |
|
|
MariaDB [bugreport]> # BUG: Missing CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`) in table t4! |
MariaDB [bugreport]> # Trying to add it: |
MariaDB [bugreport]> # (1) conditionally via executable comment:
|
MariaDB [bugreport]> /*!50101 alter table t4 add FOREIGN KEY (rid) REFERENCES t1(id) */ ; |
Query OK, 0 rows affected (0.01 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [bugreport]> show create table t4\G |
*************************** 1. row ***************************
|
Table: t4 |
Create Table: CREATE TABLE `t4` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`rid` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
KEY `rid` (`rid`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec) |
|
|
MariaDB [bugreport]> # BUG: Missing CONSTRAINT `a` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`) in table t4! |
MariaDB [bugreport]> # Trying to add it (2) unconditionally: |
MariaDB [bugreport]> alter table t4 add FOREIGN KEY (rid) REFERENCES t1(id) ; |
Query OK, 0 rows affected (0.01 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [bugreport]> show create table t4\G |
*************************** 1. row ***************************
|
Table: t4 |
Create Table: CREATE TABLE `t4` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`rid` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
KEY `rid` (`rid`), |
CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `t1` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec) |
|
|
# CONSTRAINT are only created if statement is not executed within /*!#### */ |