MDEV-15739 sql_mode=ORACLE: Make LPAD and RPAD return NULL instead of empty string10.3-MDEV-11953

1 files changed, 23 insertions, 32 deletions

diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result
index a1c6e86a129..fd18b74be32 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -705,6 +705,9 @@ FOR UPDATE;
 1
 1
 SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
+PROCEDURE ANALYSE();
+ERROR HY000: Can't use ORDER clause with this procedure
+SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 PROCEDURE ANALYSE() FOR UPDATE;
 ERROR HY000: Can't use ORDER clause with this procedure
 SELECT 1 FROM
@@ -715,7 +718,7 @@ FOR UPDATE) a;
 SELECT 1 FROM
 (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 PROCEDURE ANALYSE() FOR UPDATE) a;
-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 'PROCEDURE ANALYSE() FOR UPDATE) a' at line 3
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 SELECT 1 FROM t1
 WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 FOR UPDATE);
@@ -723,7 +726,7 @@ FOR UPDATE);
 SELECT 1 FROM t1
 WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 PROCEDURE ANALYSE() FOR UPDATE);
-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 'PROCEDURE ANALYSE() FOR UPDATE)' at line 3
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 SELECT 1 FROM t1
 UNION
 SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
@@ -734,7 +737,7 @@ SELECT 1 FROM t1
 UNION
 SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 PROCEDURE ANALYSE() FOR UPDATE;
-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 'PROCEDURE ANALYSE() FOR UPDATE' at line 4
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 SELECT 1 FROM DUAL PROCEDURE ANALYSE() 
 UNION
 SELECT 1 FROM t1;
@@ -750,11 +753,11 @@ FOR UPDATE);
 UNION 
 (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 PROCEDURE ANALYSE() FOR UPDATE);
-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 'PROCEDURE ANALYSE() FOR UPDATE)' at line 4
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 # "FOR UPDATE" tests
 SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
 1
-SELECT 1 FROM t1 FOR UPDATE UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
+(SELECT 1 FROM t1 FOR UPDATE) UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
 1
 SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 FOR UPDATE;
 1
@@ -769,10 +772,10 @@ Warnings:
 Warning	1329	No data - zero rows fetched, selected, or processed
 SELECT 1 INTO @var17727401 FROM DUAL;
 SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2;
-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 'INTO @var17727401_2' at line 1
+ERROR 42000: Incorrect usage/placement of 'INTO'
 SELECT 1 INTO @var17727401_1 FROM DUAL
 INTO @var17727401_2;
-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 'INTO @var17727401_2' at line 2
+ERROR 42000: Incorrect usage/placement of 'INTO'
 SELECT 1 INTO @var17727401 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
 Warnings:
 Warning	1329	No data - zero rows fetched, selected, or processed
@@ -784,41 +787,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 SELECT 1 INTO @var17727401_1
 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1
 INTO @var17727401_2;
-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 'INTO @var17727401_2' at line 3
+ERROR 42000: Incorrect usage/placement of 'INTO'
 SELECT (SELECT 1 FROM t1 INTO @var17727401);
-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 'INTO @var17727401)' at line 1
+ERROR 42000: Incorrect usage/placement of 'INTO'
 SELECT 1 FROM (SELECT 1 FROM t1 INTO @var17727401) a;
-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 'INTO @var17727401) a' at line 1
+ERROR 42000: Incorrect usage/placement of 'INTO'
 SELECT EXISTS(SELECT 1 FROM t1 INTO @var17727401);
-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 'INTO @var17727401)' at line 1
+ERROR 42000: Incorrect usage/placement of 'INTO'
 SELECT 1 FROM t1 INTO @var17727401 UNION SELECT 1 FROM t1 INTO t1;
 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 'UNION SELECT 1 FROM t1 INTO t1' at line 1
 (SELECT 1 FROM t1 INTO @var17727401) UNION (SELECT 1 FROM t1 INTO t1);
-ERROR HY000: Incorrect usage of UNION and INTO
+ERROR 42000: Undeclared variable: t1
 SELECT 1 FROM t1 UNION SELECT 1 FROM t1 INTO @var17727401;
 Warnings:
 Warning	1329	No data - zero rows fetched, selected, or processed
 SELECT 1 INTO @var17727401 FROM t1 PROCEDURE ANALYSE();
-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 'PROCEDURE ANALYSE()' at line 1
+ERROR 42000: Incorrect usage/placement of 'INTO'
 SELECT 1 FROM t1 PROCEDURE ANALYSE() INTO @var17727401;
-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 'INTO @var17727401' at line 1
+ERROR 42000: Incorrect usage/placement of 'INTO'
 # ORDER and LIMIT clause combinations
-(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
-1
-(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;
-1
-((SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1) ORDER BY 1;
-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 'ORDER BY 1) ORDER BY 1' at line 1
-((SELECT 1 FROM t1 LIMIT 1) LIMIT 1) LIMIT 1;
-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 'LIMIT 1) LIMIT 1' at line 1
-(SELECT 1 FROM t1 ORDER BY 1) LIMIT 1;
-1
-(SELECT 1 FROM t1 LIMIT 1) ORDER BY 1;
-1
 ((SELECT 1 FROM t1 ORDER BY 1) LIMIT 1) ORDER BY 1);
-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 'LIMIT 1) ORDER BY 1)' at line 1
+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 ')' at line 1
 ((SELECT 1 FROM t1 LIMIT 1) ORDER BY 1) LIMIT 1);
-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 'ORDER BY 1) LIMIT 1)' at line 1
+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 ')' at line 1
 SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1;
 1
 SELECT (SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1);
@@ -1265,19 +1256,19 @@ CREATE TABLE t1 (i INT);
 (SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
 UNION
 (SELECT * FROM t1 PROCEDURE ANALYSE(10, 10));
-ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 (SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
 UNION
 SELECT * FROM t1 PROCEDURE ANALYSE(10, 10);
-ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 (SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
 UNION
 (SELECT 1);
-ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 (SELECT * FROM t1 PROCEDURE ANALYSE(10, 10))
 UNION
 SELECT 1;
-ERROR HY000: Incorrect usage of UNION and SELECT ... PROCEDURE ANALYSE()
+ERROR 42000: Incorrect usage/placement of 'PROCEDURE'
 SELECT * FROM t1 PROCEDURE ANALYSE(10, 10)
 UNION
 (SELECT * FROM t1 PROCEDURE ANALYSE(10, 10));