MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT

5 files changed, 370 insertions, 1 deletions

diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result
index d9b8d911ef4..231da5a86b6 100644
--- a/mysql-test/r/keywords.result
+++ b/mysql-test/r/keywords.result
@@ -348,3 +348,5 @@ CREATE TABLE varchar2 (varchar2 int);
 DROP TABLE varchar2;
 CREATE TABLE decode (decode int);
 DROP TABLE decode;
+CREATE TABLE rowcount (rowcount int);
+DROP TABLE rowcount;
diff --git a/mysql-test/suite/compat/oracle/r/func_decode.result b/mysql-test/suite/compat/oracle/r/func_decode.result
index a7c0ffdc264..c4bfb713e61 100644
--- a/mysql-test/suite/compat/oracle/r/func_decode.result
+++ b/mysql-test/suite/compat/oracle/r/func_decode.result
@@ -28,6 +28,6 @@ EXPLAIN EXTENDED SELECT DECODE(12,10,'x10',11,'x11','def');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select (case 12 when 10 then 'x10' when 11 then 'x11' else 'def' end) AS "DECODE(12,10,'x10',11,'x11','def')"
+Note	1003	select case 12 when 10 then 'x10' when 11 then 'x11' else 'def' end AS "DECODE(12,10,'x10',11,'x11','def')"
 CREATE TABLE decode (decode int);
 DROP TABLE decode;
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index ab6211939bb..893d9ff2225 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -1066,3 +1066,177 @@ END$$
 CALL p1;
 ERROR 42S22: Unknown column 'unknown_ident' in 'field list'
 DROP PROCEDURE p1;
+#
+# MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
+#
+EXPLAIN EXTENDED SELECT sql%rowcount;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select SQL%%ROWCOUNT AS "sql%rowcount"
+CREATE TABLE t1 AS SELECT SQL%ROWCOUNT;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "SQL%ROWCOUNT" bigint(21) NOT NULL
+)
+DROP TABLE t1;
+#
+# UPDATE
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+BEGIN
+UPDATE t1 SET a=30;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+0
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+BEGIN
+UPDATE t1 SET a=30;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+2
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# DELETE
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+BEGIN
+DELETE FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+0
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+BEGIN
+DELETE FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+2
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# SELECT ... INTO var FROM ... - one row found
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+va INT;
+BEGIN
+SELECT a INTO va FROM t1 LIMIT 1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+1
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# SELECT ... INTO var FROM ... - no rows found
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+va INT;
+BEGIN
+SELECT a INTO va FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+0
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+va INT;
+BEGIN
+SELECT a INTO va FROM t1;
+SELECT SQL%ROWCOUNT;
+EXCEPTION
+WHEN NO_DATA_FOUND THEN SELECT SQL%ROWCOUNT||' (EXCEPTION)';
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT||' (EXCEPTION)'
+0 (EXCEPTION)
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# SELECT ... INTO var FROM ... - multiple rows found
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+va INT:=1;
+BEGIN
+SELECT a INTO va FROM t1;
+SELECT SQL%ROWCOUNT;
+EXCEPTION
+WHEN TOO_MANY_ROWS THEN SELECT SQL%ROWCOUNT||' (EXCEPTION) va='||va;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT||' (EXCEPTION) va='||va
+1 (EXCEPTION) va=10
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# INSERT INTO t2 SELECT ...
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+BEGIN
+INSERT INTO t2 SELECT * FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+2
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+#
+# End of MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
+#
diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test
index 3bdc1782a1a..e2298be3668 100644
--- a/mysql-test/suite/compat/oracle/t/sp.test
+++ b/mysql-test/suite/compat/oracle/t/sp.test
@@ -1158,3 +1158,193 @@ DELIMITER ;$$
 --error ER_BAD_FIELD_ERROR
 CALL p1;
 DROP PROCEDURE p1;
+
+--echo #
+--echo # MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
+--echo #
+
+EXPLAIN EXTENDED SELECT sql%rowcount;
+CREATE TABLE t1 AS SELECT SQL%ROWCOUNT;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # UPDATE
+--echo #
+
+CREATE TABLE t1 (a INT);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+  UPDATE t1 SET a=30;
+  SELECT SQL%ROWCOUNT;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+  UPDATE t1 SET a=30;
+  SELECT SQL%ROWCOUNT;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # DELETE
+--echo #
+
+CREATE TABLE t1 (a INT);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+  DELETE FROM t1;
+  SELECT SQL%ROWCOUNT;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+  DELETE FROM t1;
+  SELECT SQL%ROWCOUNT;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SELECT ... INTO var FROM ... - one row found
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  va INT;
+BEGIN
+  SELECT a INTO va FROM t1 LIMIT 1;
+  SELECT SQL%ROWCOUNT;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SELECT ... INTO var FROM ... - no rows found
+--echo #
+CREATE TABLE t1 (a INT);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  va INT;
+BEGIN
+  SELECT a INTO va FROM t1;
+  SELECT SQL%ROWCOUNT;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  va INT;
+BEGIN
+  SELECT a INTO va FROM t1;
+  SELECT SQL%ROWCOUNT;
+EXCEPTION
+  WHEN NO_DATA_FOUND THEN SELECT SQL%ROWCOUNT||' (EXCEPTION)';
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # SELECT ... INTO var FROM ... - multiple rows found
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  va INT:=1;
+BEGIN
+  SELECT a INTO va FROM t1;
+  SELECT SQL%ROWCOUNT;
+EXCEPTION
+  WHEN TOO_MANY_ROWS THEN SELECT SQL%ROWCOUNT||' (EXCEPTION) va='||va;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+
+--echo #
+--echo # INSERT INTO t2 SELECT ...
+--echo #
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+  INSERT INTO t2 SELECT * FROM t1;
+  SELECT SQL%ROWCOUNT;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # End of MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
+--echo #
diff --git a/mysql-test/t/keywords.test b/mysql-test/t/keywords.test
index b9e52ab8308..de97aa58fd5 100644
--- a/mysql-test/t/keywords.test
+++ b/mysql-test/t/keywords.test
@@ -244,3 +244,6 @@ DROP TABLE varchar2;
 
 CREATE TABLE decode (decode int);
 DROP TABLE decode;
+
+CREATE TABLE rowcount (rowcount int);
+DROP TABLE rowcount;