|
built-in function is not validating when using sql_mode=IGNORE_SPACE
Actual result
11.0.2-opt>delimiter //
|
11.0.2-opt>CREATE PROCEDURE p1()
|
-> BEGIN
|
-> CREATE TABLE count (i INT);
|
-> END //
|
Query OK, 0 rows affected (0.007 sec)
|
|
11.0.2-opt>delimiter ;
|
11.0.2-opt>SET sql_mode = 'IGNORE_SPACE';
|
Query OK, 0 rows affected (0.000 sec)
|
|
11.0.2-opt>call p1();
|
Query OK, 0 rows affected (0.013 sec)
|
|
11.0.2-opt>
|
11.0.2-opt>drop table count;
|
Query OK, 0 rows affected (0.013 sec)
|
Expected result
11.0.2-opt>CREATE TABLE count (i INT);
|
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 'count (i INT)' at line 1
|
11.0.2-opt>
|
|
|
Some more test cases
sql_mode=EMPTY_STRING_IS_NULL
SET sql_mode=DEFAULT;
|
delimiter //
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
SELECT '' AS empty;
|
END //
|
delimiter ;
|
SET sql_mode = 'EMPTY_STRING_IS_NULL';
|
call p1();
|
Actual result
11.0.2-opt>call p1();
|
+-------+
|
| empty |
|
+-------+
|
| |
|
+-------+
|
1 row in set (0.000 sec)
|
|
Query OK, 0 rows affected (0.000 sec)
|
|
11.0.2-opt>
|
Expected result
11.0.2-opt>SELECT '' AS empty;
|
+-------+
|
| empty |
|
+-------+
|
| NULL |
|
+-------+
|
1 row in set (0.000 sec)
|
|
11.0.2-opt>
|
sql_mode=HIGH_NOT_PRECEDENCE
SET sql_mode=DEFAULT;
|
delimiter //
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
SELECT NOT 1 BETWEEN -5 AND 5;
|
END //
|
delimiter ;
|
SET sql_mode = 'HIGH_NOT_PRECEDENCE';
|
call p1();
|
Actual result
11.0.2-opt>call p1();
|
+------------------------+
|
| NOT 1 BETWEEN -5 AND 5 |
|
+------------------------+
|
| 0 |
|
+------------------------+
|
1 row in set (0.000 sec)
|
|
Query OK, 0 rows affected (0.000 sec)
|
11.0.2-opt>
|
Expected result
11.0.2-opt>SELECT NOT 1 BETWEEN -5 AND 5;
|
+------------------------+
|
| NOT 1 BETWEEN -5 AND 5 |
|
+------------------------+
|
| 1 |
|
+------------------------+
|
1 row in set (0.000 sec)
|
|
11.0.2-opt>
|
sql_mode=NO_AUTO_VALUE_ON_ZERO
SET sql_mode=DEFAULT;
|
delimiter //
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
CREATE OR REPLACE TABLE t1 (i INT AUTO_INCREMENT, KEY(i));
|
INSERT INTO t1 VALUES(0);
|
END //
|
delimiter ;
|
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
|
call p1();
|
SELECT * FROM t1;
|
Actual result
11.0.2-opt>call p1();
|
Query OK, 1 row affected (0.016 sec)
|
|
11.0.2-opt>SELECT * FROM t1;
|
+---+
|
| i |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.000 sec)
|
|
11.0.2-opt>
|
Expected result
11.0.2-opt>truncate t1;
|
Query OK, 0 rows affected (0.028 sec)
|
|
11.0.2-opt>INSERT INTO t1 VALUES(0);
|
Query OK, 1 row affected (0.002 sec)
|
|
11.0.2-opt>SELECT * FROM t1;
|
+---+
|
| i |
|
+---+
|
| 0 |
|
+---+
|
1 row in set (0.000 sec)
|
|
11.0.2-opt>
|
sql_mode=NO_BACKSLASH_ESCAPES
SET sql_mode=DEFAULT;
|
delimiter //
|
CREATE OR REPLACEv PROCEDURE p1()
|
BEGIN
|
CREATE OR REPLACE TABLE t1 (a VARCHAR(20));
|
INSERT INTO t1 VALUES ("\\");
|
END //
|
delimiter ;
|
SET sql_mode = 'NO_BACKSLASH_ESCAPES';
|
call p1();
|
SELECT * FROM t1;
|
Actual result
11.0.2-opt>call p1();
|
Query OK, 1 row affected (0.011 sec)
|
|
11.0.2-opt>SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| \ |
|
+------+
|
1 row in set (0.000 sec)
|
|
11.0.2-opt>
|
Expected result
11.0.2-opt>truncate t1;
|
Query OK, 0 rows affected (0.006 sec)
|
|
11.0.2-opt>INSERT INTO t1 VALUES ("\\");
|
Query OK, 1 row affected (0.001 sec)
|
|
11.0.2-opt>SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| \\ |
|
+------+
|
1 row in set (0.000 sec)
|
|
11.0.2-opt>
|
sql_mode='NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'
SET sql_mode=DEFAULT;
|
CREATE OR REPLACE TABLE t1 (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name varchar(2), key(name) comment 'string') ;
|
delimiter //
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
SHOW CREATE TABLE t1;
|
END //
|
delimiter ;
|
SET sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
|
call p1();
|
Actual result
11.0.2-opt>call p1();
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`i` int(11) NOT NULL AUTO_INCREMENT,
|
`name` varchar(2) DEFAULT NULL,
|
PRIMARY KEY (`i`),
|
KEY `name` (`name`) COMMENT 'string'
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
Query OK, 0 rows affected (0.001 sec)
|
|
11.0.2-opt>
|
Expected result
11.0.2-opt>SHOW CREATE TABLE t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`i` int(11) NOT NULL,
|
`name` varchar(2) DEFAULT NULL,
|
PRIMARY KEY (`i`),
|
KEY `name` (`name`)
|
)
|
1 row in set (0.000 sec)
|
|
11.0.2-opt>
|
|