[MDEV-31123] Stored programs does not validate SQLs when using certain SQL modes - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Stored routines, Triggers
Labels:
None

Description

sql_mode=ALLOW_INVALID_DATES does not validate when inserting date through trigger/procedure

10.4.29-opt>SET sql_mode = '';

Query OK, 0 rows affected (0.000 sec)

10.4.29-opt>CREATE TABLE t1 (a INT);

Query OK, 0 rows affected (0.011 sec)

10.4.29-opt>CREATE TABLE t2 (a datetime);

Query OK, 0 rows affected (0.014 sec)

10.4.29-opt>CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES('2023-02-31');

Query OK, 0 rows affected (0.006 sec)

10.4.29-opt>INSERT INTO t1 (a) VALUES (1);

Query OK, 1 row affected (0.003 sec)

10.4.29-opt>SELECT * FROM t2;

+---------------------+

| a                   |

+---------------------+

| 0000-00-00 00:00:00 |

+---------------------+

1 row in set (0.000 sec)

10.4.29-opt>SET sql_mode = 'ALLOW_INVALID_DATES';

Query OK, 0 rows affected (0.000 sec)

10.4.29-opt>INSERT INTO t1 (a) VALUES (2);

Query OK, 1 row affected (0.003 sec)

10.4.29-opt>SELECT * FROM t2;

+---------------------+

| a                   |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

+---------------------+

2 rows in set (0.005 sec)

10.4.29-opt>

Expected result

10.11.3-opt>select * from t2;

+---------------------+

| a                   |

+---------------------+

| 0000-00-00 00:00:00 |

| 2023-02-31 00:00:00 |

+---------------------+

2 rows in set (0.000 sec)

10.11.3-opt>

test case for sp

SET sql_mode = '';

DROP TABLE t1;

CREATE TABLE t1(a datetime);

delimiter //

CREATE PROCEDURE p1()

BEGIN

  INSERT INTO t1 VALUES ('2023-02-31');

END //

delimiter ;

call p1();

SELECT * FROM t1;

SET sql_mode = 'ALLOW_INVALID_DATES';

call p1();

SELECT * FROM t1;

Attachments

Issue Links

relates to

MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements

Closed

Activity

Ascending order - Click to sort in descending order

Ramesh Sivaraman added a comment - 2023-04-25 10:49

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>

Ramesh Sivaraman added a comment - 2023-04-25 10:49 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>

Ramesh Sivaraman added a comment - 2023-04-27 09:18

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>

Ramesh Sivaraman added a comment - 2023-04-27 09:18 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>

People

Assignee:: Dmitry Shulga

Reporter:: Ramesh Sivaraman

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-04-25 06:42

Updated:: 2024-09-10 15:34

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration