mirror of
https://github.com/MariaDB/server.git
synced 2025-04-15 11:45:32 +02:00
2b6d241ee4
The crash happened with an indexed virtual column whose value is evaluated using a function that has a different meaning in sql_mode='' vs sql_mode=ORACLE: - DECODE() - LTRIM() - RTRIM() - LPAD() - RPAD() - REPLACE() - SUBSTR() For example: CREATE TABLE t1 ( b VARCHAR(1), g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, KEY g(g) ); So far we had replacement XXX_ORACLE() functions for all mentioned function, e.g. SUBSTR_ORACLE() for SUBSTR(). So it was possible to correctly re-parse SUBSTR_ORACLE() even in sql_mode=''. But it was not possible to re-parse the MariaDB version of SUBSTR() after switching to sql_mode=ORACLE. It was erroneously mis-interpreted as SUBSTR_ORACLE(). As a result, this combination worked fine: SET sql_mode=ORACLE; CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...; INSERT ... FLUSH TABLES; SET sql_mode=''; INSERT ... But the other way around it crashed: SET sql_mode=''; CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...; INSERT ... FLUSH TABLES; SET sql_mode=ORACLE; INSERT ... At CREATE time, SUBSTR was instantiated as Item_func_substr and printed in the FRM file as substr(). At re-open time with sql_mode=ORACLE, "substr()" was erroneously instantiated as Item_func_substr_oracle. Fix: The fix proposes a symmetric solution. It provides a way to re-parse reliably all sql_mode dependent functions to their original CREATE TABLE time meaning, no matter what the open-time sql_mode is. We take advantage of the same idea we previously used to resolve sql_mode dependent data types. Now all sql_mode dependent functions are printed by SHOW using a schema qualifier when the current sql_mode differs from the function sql_mode: SET sql_mode=''; CREATE TABLE t1 ... SUBSTR(a,b,c) ..; SET sql_mode=ORACLE; SHOW CREATE TABLE t1; -> mariadb_schema.substr(a,b,c) SET sql_mode=ORACLE; CREATE TABLE t2 ... SUBSTR(a,b,c) ..; SET sql_mode=''; SHOW CREATE TABLE t1; -> oracle_schema.substr(a,b,c) Old replacement names like substr_oracle() are still understood for backward compatibility and used in FRM files (for downgrade compatibility), but they are not printed by SHOW any more.
264 lines
7 KiB
Text
264 lines
7 KiB
Text
SET sql_mode=ORACLE;
|
|
#
|
|
# MDEV-10801 sql_mode: dynamic SQL placeholders
|
|
#
|
|
SET @a=10, @b=20;
|
|
PREPARE stmt FROM 'SELECT ?,?';
|
|
EXECUTE stmt USING @a, @b;
|
|
? ?
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :a,:b';
|
|
EXECUTE stmt USING @a, @b;
|
|
:a :b
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :aaa,:bbb';
|
|
EXECUTE stmt USING @a, @b;
|
|
:aaa :bbb
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :"a",:"b"';
|
|
EXECUTE stmt USING @a, @b;
|
|
:"a" :"b"
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :"aaa",:"bbb"';
|
|
EXECUTE stmt USING @a, @b;
|
|
:"aaa" :"bbb"
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :1,:2';
|
|
EXECUTE stmt USING @a, @b;
|
|
:1 :2
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :222,:111';
|
|
EXECUTE stmt USING @a, @b;
|
|
:222 :111
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :0,:65535';
|
|
EXECUTE stmt USING @a, @b;
|
|
:0 :65535
|
|
10 20
|
|
PREPARE stmt FROM 'SELECT :65535,:0';
|
|
EXECUTE stmt USING @a, @b;
|
|
:65535 :0
|
|
10 20
|
|
#
|
|
# MDEV-10709 Expressions as parameters to Dynamic SQL
|
|
#
|
|
#
|
|
# Testing disallowed expressions in USING
|
|
#
|
|
PREPARE stmt FROM 'SELECT :1 FROM DUAL';
|
|
EXECUTE stmt USING (SELECT 1);
|
|
ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
|
|
DEALLOCATE PREPARE stmt;
|
|
CREATE FUNCTION f1() RETURN VARCHAR
|
|
AS
|
|
BEGIN
|
|
RETURN 'test';
|
|
END;
|
|
$$
|
|
PREPARE stmt FROM 'SELECT ? FROM DUAL';
|
|
EXECUTE stmt USING f1();
|
|
ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP FUNCTION f1;
|
|
#
|
|
# Using a user variable as a EXECUTE..USING out parameter
|
|
#
|
|
CREATE PROCEDURE p1(a OUT INT)
|
|
AS
|
|
BEGIN
|
|
a:= 10;
|
|
END;
|
|
/
|
|
SET @a=1;
|
|
CALL p1(@a);
|
|
SELECT @a;
|
|
@a
|
|
10
|
|
SET @a=2;
|
|
PREPARE stmt FROM 'CALL p1(?)';
|
|
EXECUTE stmt USING @a;
|
|
SELECT @a;
|
|
@a
|
|
10
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# Using an SP variable as a EXECUTE..USING out parameter
|
|
#
|
|
CREATE PROCEDURE p1 (a OUT INT)
|
|
AS
|
|
BEGIN
|
|
a:=10;
|
|
END;
|
|
/
|
|
CREATE PROCEDURE p2 (a OUT INT)
|
|
AS
|
|
BEGIN
|
|
PREPARE stmt FROM 'CALL p1(?)';
|
|
EXECUTE stmt USING a;
|
|
END;
|
|
/
|
|
SET @a= 1;
|
|
CALL p2(@a);
|
|
SELECT @a;
|
|
@a
|
|
10
|
|
DROP PROCEDURE p2;
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# Using a trigger field as a EXECUTE..USING out parameter
|
|
#
|
|
CREATE PROCEDURE p1 (a OUT INT)
|
|
AS
|
|
BEGIN
|
|
a:= 10;
|
|
END;
|
|
/
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1(:NEW.a);
|
|
INSERT INTO t1 VALUES (1);
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
DROP TABLE t1;
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# Testing re-prepare on a table metadata update between PREPARE and EXECUTE
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE PROCEDURE p1(a IN INT)
|
|
AS
|
|
BEGIN
|
|
INSERT INTO t1 VALUES (a);
|
|
END;
|
|
/
|
|
PREPARE stmt FROM 'CALL p1(?)';
|
|
EXECUTE stmt USING 10;
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW NEW.a:=NEW.a+1;
|
|
EXECUTE stmt USING 20;
|
|
SELECT * FROM t1;
|
|
a
|
|
10
|
|
21
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
#
|
|
# End of MDEV-10709 Expressions as parameters to Dynamic SQL
|
|
#
|
|
#
|
|
# MDEV-10585 EXECUTE IMMEDIATE statement
|
|
#
|
|
#
|
|
# Testing disallowed expressions in USING
|
|
#
|
|
EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING (SELECT 1);
|
|
ERROR 42000: EXECUTE..USING does not support subqueries or stored functions
|
|
CREATE FUNCTION f1() RETURN VARCHAR
|
|
AS
|
|
BEGIN
|
|
RETURN 'test';
|
|
END;
|
|
$$
|
|
EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1();
|
|
ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions
|
|
DROP FUNCTION f1;
|
|
#
|
|
# Testing simple expressions
|
|
#
|
|
EXECUTE IMMEDIATE 'SELECT :1 FROM DUAL' USING 10;
|
|
:1
|
|
10
|
|
#
|
|
# MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
|
|
#
|
|
#
|
|
# Testing erroneous and diallowed prepare source
|
|
#
|
|
EXECUTE IMMEDIATE _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL';
|
|
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
|
|
PREPARE stmt FROM _latin1'SELECT 1 AS c FROM ' || _latin2 'DUAL';
|
|
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
|
|
EXECUTE IMMEDIATE (SELECT 'SELECT 1');
|
|
ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions
|
|
PREPARE stmt FROM (SELECT 'SELECT 1');
|
|
ERROR 42000: PREPARE..FROM does not support subqueries or stored functions
|
|
EXECUTE IMMEDIATE a;
|
|
ERROR 42S22: Unknown column 'a' in 'field list'
|
|
PREPARE stmt FROM a;
|
|
ERROR 42S22: Unknown column 'a' in 'field list'
|
|
EXECUTE IMMEDIATE NULL;
|
|
ERROR 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 'NULL' at line 1
|
|
PREPARE stmt FROM NULL;
|
|
ERROR 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 'NULL' at line 1
|
|
EXECUTE IMMEDIATE COALESCE(NULL);
|
|
ERROR 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 'NULL' at line 1
|
|
PREPARE stmt FROM COALESCE(NULL);
|
|
ERROR 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 'NULL' at line 1
|
|
CREATE FUNCTION f1() RETURN VARCHAR
|
|
AS
|
|
BEGIN
|
|
RETURN 't1';
|
|
END;
|
|
$$
|
|
EXECUTE IMMEDIATE f1();
|
|
ERROR 42000: EXECUTE IMMEDIATE does not support subqueries or stored functions
|
|
PREPARE stmt FROM f1();
|
|
ERROR 42000: PREPARE..FROM does not support subqueries or stored functions
|
|
DROP FUNCTION f1;
|
|
#
|
|
# Testing user variables in prepare source
|
|
#
|
|
SET @table_name='DUAL';
|
|
EXECUTE IMMEDIATE 'SELECT 1 AS a FROM ' || @table_name;
|
|
a
|
|
1
|
|
PREPARE stmt FROM 'SELECT 1 AS a FROM ' || @table_name;
|
|
EXECUTE stmt;
|
|
a
|
|
1
|
|
DEALLOCATE PREPARE stmt;
|
|
#
|
|
# Testing SP parameters and variables in prepare source
|
|
#
|
|
CREATE PROCEDURE p1(table_name VARCHAR)
|
|
AS
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'SELECT 1 AS c FROM '|| table_name;
|
|
END;
|
|
$$
|
|
CALL p1('DUAL');
|
|
c
|
|
1
|
|
DROP PROCEDURE p1;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
table_name VARCHAR(64):='DUAL';
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'SELECT 1 AS c FROM ' || table_name;
|
|
END;
|
|
$$
|
|
CALL p1();
|
|
c
|
|
1
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
|
|
#
|
|
#
|
|
# MDEV-12846 sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash
|
|
#
|
|
SELECT ? FROM DUAL;
|
|
ERROR 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 '? FROM DUAL' at line 1
|
|
SELECT :a FROM DUAL;
|
|
ERROR 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 ':a FROM DUAL' at line 1
|
|
SELECT :1 FROM DUAL;
|
|
ERROR 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 ':1 FROM DUAL' at line 1
|
|
SELECT 1+? FROM DUAL;
|
|
ERROR 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 '? FROM DUAL' at line 1
|
|
SELECT 1+:a FROM DUAL;
|
|
ERROR 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 ':a FROM DUAL' at line 1
|
|
SELECT 1+:1 FROM DUAL;
|
|
ERROR 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 ':1 FROM DUAL' at line 1
|