Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

10 files changed, 90 insertions, 90 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-inout.result b/mysql-test/suite/compat/oracle/r/sp-inout.result
index fa6f5076..bfaa7572 100644
--- a/mysql-test/suite/compat/oracle/r/sp-inout.result
+++ b/mysql-test/suite/compat/oracle/r/sp-inout.result
@@ -684,7 +684,7 @@ END;
 END;
 $$
 set @a = 4;
-INSERT INTO Persons SELECT 4, 'DDD', PKG2.func(@a);
+INSERT INTO Persons SELECT 4, 'DDD', pkg2.func(@a);
 SELECT * FROM Persons;
 ID	Name	Age
 1	AAA	10
@@ -726,8 +726,8 @@ ID	Name	Age
 2	BBB	20
 3	CCC	30
 set @a = 0;
-INSERT INTO Persons SELECT 5, 'EEE', PKG2.func(@a);
-ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here
+INSERT INTO Persons SELECT 5, 'EEE', pkg2.func(@a);
+ERROR HY000: OUT or INOUT argument 1 for function pkg2.func is not allowed here
 DROP TABLE Persons;
 DROP PACKAGE pkg2;
 # 
@@ -764,7 +764,7 @@ ID	Name	Age
 3	CCC	30
 4	DDD	40
 set @a = 4;
-DELETE FROM Persons WHERE ID = PKG2.func(@a);
+DELETE FROM Persons WHERE ID = pkg2.func(@a);
 SELECT * FROM Persons;
 ID	Name	Age
 1	AAA	10
@@ -807,8 +807,8 @@ ID	Name	Age
 3	CCC	30
 4	DDD	40
 set @a = 0;
-DELETE FROM Persons WHERE ID = PKG2.func(@a);
-ERROR HY000: OUT or INOUT argument 1 for function PKG2.func is not allowed here
+DELETE FROM Persons WHERE ID = pkg2.func(@a);
+ERROR HY000: OUT or INOUT argument 1 for function pkg2.func is not allowed here
 DROP TABLE Persons;
 DROP PACKAGE pkg2;
 # 
diff --git a/mysql-test/suite/compat/oracle/r/sp-package-innodb.result b/mysql-test/suite/compat/oracle/r/sp-package-innodb.result
index 0ac357df..50eb2dc6 100644
--- a/mysql-test/suite/compat/oracle/r/sp-package-innodb.result
+++ b/mysql-test/suite/compat/oracle/r/sp-package-innodb.result
@@ -23,8 +23,6 @@ a:=a+1;
 INSERT INTO t1 VALUES (a,'pkg1 initialization');
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL pkg1.p1;
 SELECT * FROM t1 ORDER BY a;
 a	routine
diff --git a/mysql-test/suite/compat/oracle/r/sp-package-mysqldump.result b/mysql-test/suite/compat/oracle/r/sp-package-mysqldump.result
index 508aea0e..7b423e85 100644
--- a/mysql-test/suite/compat/oracle/r/sp-package-mysqldump.result
+++ b/mysql-test/suite/compat/oracle/r/sp-package-mysqldump.result
@@ -45,7 +45,7 @@ $$
 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+/*M!100616 SET @OLD_NOTE_VERBOSITY=@@NOTE_VERBOSITY, NOTE_VERBOSITY=0 */;
 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 /*!50003 SET sql_mode              = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT' */ ;
 /*!50003 DROP PROCEDURE IF EXISTS `p1` */;
@@ -137,7 +137,7 @@ DELIMITER ;
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+/*M!100616 SET NOTE_VERBOSITY=@OLD_NOTE_VERBOSITY */;
 
 <?xml version="1.0"?>
 <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result
index ee17c048..2372cfea 100644
--- a/mysql-test/suite/compat/oracle/r/sp-package.result
+++ b/mysql-test/suite/compat/oracle/r/sp-package.result
@@ -444,7 +444,7 @@ CREATE PACKAGE BODY test2 AS
 PROCEDURE p2 AS BEGIN NULL; END;
 END;
 $$
-ERROR HY000: Subroutine 'test.test2.p1' is declared in the package specification but is not defined in the package body
+ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
 DROP PACKAGE test2;
 CREATE PACKAGE test2 AS
 FUNCTION f1 RETURN INT;
@@ -454,7 +454,7 @@ CREATE PACKAGE BODY test2 AS
 FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
 END;
 $$
-ERROR HY000: Subroutine 'test.test2.f1' is declared in the package specification but is not defined in the package body
+ERROR HY000: FUNCTION `test.test2.f1` is declared in the package specification but is not defined in the package body
 DROP PACKAGE test2;
 CREATE PACKAGE test2 AS
 PROCEDURE p1;
@@ -464,7 +464,7 @@ CREATE PACKAGE BODY test2 AS
 FUNCTION p1 RETURN INT AS BEGIN RETURN 10; END;
 END;
 $$
-ERROR HY000: Subroutine 'test.test2.p1' is declared in the package specification but is not defined in the package body
+ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
 DROP PACKAGE test2;
 CREATE PACKAGE test2 AS
 PROCEDURE p1;
@@ -474,7 +474,7 @@ CREATE PACKAGE BODY test2 AS
 PROCEDURE p1(a INT) AS BEGIN NULL; END; -- Notice different prototype
 END;
 $$
-ERROR HY000: Subroutine 'test.test2.p1' is declared in the package specification but is not defined in the package body
+ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
 DROP PACKAGE test2;
 #
 # Forward declarations in CREATE PACKAGE BODY with missing implementations
@@ -488,13 +488,13 @@ PROCEDURE p1 AS BEGIN NULL; END;
 PROCEDURE p2;
 END;
 $$
-ERROR HY000: Subroutine 'test.test2.p2' has a forward declaration but is not defined
+ERROR HY000: PROCEDURE `test.test2.p2` has a forward declaration but is not defined
 CREATE PACKAGE BODY test2 AS
 FUNCTION f1 RETURN INT;
 PROCEDURE p1 AS BEGIN NULL; END;
 END;
 $$
-ERROR HY000: Subroutine 'test.test2.f1' has a forward declaration but is not defined
+ERROR HY000: FUNCTION `test.test2.f1` has a forward declaration but is not defined
 DROP PACKAGE test2;
 #
 # Creating a new package
@@ -2092,8 +2092,6 @@ $$
 CALL p1.p1();
 @a
 11
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1.p1();
 @a
 12
@@ -2125,8 +2123,6 @@ BEGIN
 SELECT MAX(a) FROM t1 INTO @a;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1.p1();
 @a
 11
@@ -2160,8 +2156,6 @@ BEGIN
 SELECT 1 FROM t1 INTO @a;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1.p1();
 ERROR 42S02: Table 'test.t1' doesn't exist
 SELECT p1.f1();
@@ -2720,9 +2714,6 @@ SELECT * FROM t1 INTO b;
 SELECT b.a, b.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1.p1;
 b.a	b.b
 10	b
@@ -3157,7 +3148,24 @@ collation_connection	latin1_swedish_ci
 DROP VIEW v_test;
 SET sql_mode=DEFAULT;
 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
-ERROR 42000: FUNCTION test1.f_test does not exist
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View	v_test
+Create View	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL  where 1 = `test`.`test1`.`f_test`()
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+SET sql_mode=ORACLE;
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View	v_test
+Create View	CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL  where 1 = "test"."test1"."f_test"()
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+DROP VIEW v_test;
 SET sql_mode=ORACLE;
 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
 SELECT * FROM v_test;
diff --git a/mysql-test/suite/compat/oracle/r/sp-row.result b/mysql-test/suite/compat/oracle/r/sp-row.result
index 0b23f303..b3a0ae15 100644
--- a/mysql-test/suite/compat/oracle/r/sp-row.result
+++ b/mysql-test/suite/compat/oracle/r/sp-row.result
@@ -2835,8 +2835,6 @@ SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 ERROR 21000: The used SELECT statements have a different number of columns
 DROP TABLE t1;
@@ -2851,8 +2849,6 @@ SELECT * FROM t1 INTO rec1, rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 ERROR 21000: Operand should contain 2 column(s)
 DROP TABLE t1;
@@ -2867,8 +2863,6 @@ SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 rec1.a	rec1.b
 10	b10
@@ -2884,8 +2878,6 @@ SELECT 10,'a','b' FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 ERROR 21000: The used SELECT statements have a different number of columns
 DROP TABLE t1;
@@ -2900,8 +2892,6 @@ SELECT 10,'a' FROM t1 INTO rec1, rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 ERROR 21000: Operand should contain 2 column(s)
 DROP TABLE t1;
@@ -2916,8 +2906,6 @@ SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 rec1.a	rec1.b
 10	b10
@@ -2934,8 +2922,6 @@ SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 ERROR 21000: The used SELECT statements have a different number of columns
 DROP TABLE t1;
@@ -2951,8 +2937,6 @@ SELECT * FROM t1 INTO rec1, rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 ERROR 21000: Operand should contain 2 column(s)
 DROP TABLE t1;
@@ -2968,8 +2952,6 @@ SELECT * FROM t1 INTO rec1;
 SELECT rec1.a, rec1.b;
 END;
 $$
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 CALL p1();
 rec1.a	rec1.b
 10	b10
diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result
index af071433..65e31761 100644
--- a/mysql-test/suite/compat/oracle/r/table_value_constr.result
+++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result
@@ -565,12 +565,12 @@ where t1.a=t2.a and st<3
 select * from t2;
 a	b	st
 1	1	1
-1	2	2
 1	1	2
-1	2	3
-1	2	3
 1	1	3
 1	1	3
+1	2	2
+1	2	3
+1	2	3
 # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements)
 with recursive fact(n,f) as
 (
@@ -741,21 +741,19 @@ a	b
 explain extended select * from t1
 where a in (values (1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<derived2>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
 explain extended select * from t1
 where a in (select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
 select * from t1
 where a in (values (1) union select 2);
@@ -774,7 +772,7 @@ explain extended select * from t1
 where a in (values (1) union select 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+4	DEPENDENT SUBQUERY	<derived2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union4,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
@@ -785,7 +783,7 @@ where a in (select * from (values (1)) as tvc_0 union
 select 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+2	DEPENDENT SUBQUERY	<derived3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 4	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
@@ -810,7 +808,7 @@ where a in (select 2 union values (1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+4	DEPENDENT UNION	<derived3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
@@ -821,7 +819,7 @@ select * from (values (1)) tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	1	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
@@ -846,7 +844,7 @@ explain extended select * from t1
 where a in (values (1) union all select b from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+4	DEPENDENT SUBQUERY	<derived2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	DEPENDENT UNION	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
@@ -856,7 +854,7 @@ where a in (select * from (values (1)) as tvc_0 union all
 select b from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+2	DEPENDENT SUBQUERY	<derived3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 4	DEPENDENT UNION	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
@@ -878,18 +876,18 @@ explain extended select * from t1
 where a not in (values (1),(2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DEPENDENT SUBQUERY	<derived2>	unique_subquery	distinct_key	distinct_key	4	func	1	100.00	Using where; Full scan on NULL key
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (/* select#3 */ select "tvc_0"."1" from (values (1),(2)) "tvc_0" ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key where "test"."t1"."a" = "<subquery3>"."1"))))
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1")))))
 explain extended select * from t1 
 where a not in (select * from (values (1),(2)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DEPENDENT SUBQUERY	<derived3>	unique_subquery	distinct_key	distinct_key	4	func	1	100.00	Using where; Full scan on NULL key
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (/* select#2 */ select "tvc_0"."1" from (values (1),(2)) "tvc_0" ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key where "test"."t1"."a" = "<subquery2>"."1"))))
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(<primary_index_lookup>(<cache>("test"."t1"."a") in <temporary table> on distinct_key where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1")))))
 # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place
 select * from t1
 where a not in (values (1) union select 2);
@@ -976,21 +974,19 @@ a	b
 explain extended select * from t1 
 where a = any (values (1),(2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<derived2>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1),(2)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
 explain extended select * from t1 
 where a = any (select * from (values (1),(2)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<derived3>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from (values (1),(2)) "tvc_0" join "test"."t1" where "tvc_0"."1" = "test"."t1"."a"
 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
 select * from t1 
 where a = any (values (1) union select 2);
@@ -1009,7 +1005,7 @@ explain extended select * from t1
 where a = any (values (1) union select 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+4	DEPENDENT SUBQUERY	<derived2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union4,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
@@ -1020,7 +1016,7 @@ where a = any (select * from (values (1)) as tvc_0 union
 select 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+2	DEPENDENT SUBQUERY	<derived3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 4	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
@@ -1045,7 +1041,7 @@ where a = any (select 2 union values (1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+4	DEPENDENT UNION	<derived3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
@@ -1056,7 +1052,7 @@ select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	1	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
@@ -1138,7 +1134,7 @@ where a = any (select 1 union values (1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+4	DEPENDENT UNION	<derived3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
@@ -1149,7 +1145,7 @@ select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	1	100.00	
 4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
diff --git a/mysql-test/suite/compat/oracle/r/update_innodb.result b/mysql-test/suite/compat/oracle/r/update_innodb.result
index 1dae643e..0c9922fe 100644
--- a/mysql-test/suite/compat/oracle/r/update_innodb.result
+++ b/mysql-test/suite/compat/oracle/r/update_innodb.result
@@ -6,8 +6,6 @@ CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) engine=innodb;
 INSERT INTO t1 VALUES (1);
 START TRANSACTION;
 SELECT a AS a_con1 FROM t1 INTO @a FOR UPDATE;
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 connect con2,localhost,root,,;
 SET sql_mode='ORACLE';
 START TRANSACTION;
@@ -16,8 +14,6 @@ connection default;
 UPDATE t1 SET a=a+100;
 COMMIT;
 connection con2;
-Warnings:
-Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
 SELECT a AS con2 FROM t1;
 con2
 101
diff --git a/mysql-test/suite/compat/oracle/t/sp-inout.test b/mysql-test/suite/compat/oracle/t/sp-inout.test
index d605be64..e62a9436 100644
--- a/mysql-test/suite/compat/oracle/t/sp-inout.test
+++ b/mysql-test/suite/compat/oracle/t/sp-inout.test
@@ -730,7 +730,7 @@ $$
 DELIMITER ;$$
 
 set @a = 4;
-INSERT INTO Persons SELECT 4, 'DDD', PKG2.func(@a);
+INSERT INTO Persons SELECT 4, 'DDD', pkg2.func(@a);
 SELECT * FROM Persons;
 DROP TABLE Persons;
 DROP PACKAGE pkg2;
@@ -770,7 +770,7 @@ DELIMITER ;$$
 SELECT * FROM Persons;
 set @a = 0;
 --error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED
-INSERT INTO Persons SELECT 5, 'EEE', PKG2.func(@a);
+INSERT INTO Persons SELECT 5, 'EEE', pkg2.func(@a);
 DROP TABLE Persons;
 DROP PACKAGE pkg2;
 
@@ -808,7 +808,7 @@ DELIMITER ;$$
 
 SELECT * FROM Persons;
 set @a = 4;
-DELETE FROM Persons WHERE ID = PKG2.func(@a);
+DELETE FROM Persons WHERE ID = pkg2.func(@a);
 SELECT * FROM Persons;
 DROP TABLE Persons;
 DROP PACKAGE pkg2;
@@ -849,7 +849,7 @@ DELIMITER ;$$
 SELECT * FROM Persons;
 set @a = 0;
 --error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED
-DELETE FROM Persons WHERE ID = PKG2.func(@a);
+DELETE FROM Persons WHERE ID = pkg2.func(@a);
 DROP TABLE Persons;
 DROP PACKAGE pkg2;
 
diff --git a/mysql-test/suite/compat/oracle/t/sp-package.test b/mysql-test/suite/compat/oracle/t/sp-package.test
index b6310eed..aefede41 100644
--- a/mysql-test/suite/compat/oracle/t/sp-package.test
+++ b/mysql-test/suite/compat/oracle/t/sp-package.test
@@ -2854,13 +2854,18 @@ END test1;
 $$
 DELIMITER ;$$
 
-
+#
+# A VIEW created with sql_mode=ORACLE, calling a package routine
+#
 SET sql_mode=ORACLE;
 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
 SELECT * FROM v_test;
 --vertical_results
 SHOW CREATE VIEW v_test;
 --horizontal_results
+#
+# It also works with sql_mode=DEFALT
+#
 SET sql_mode=DEFAULT;
 SELECT * FROM v_test;
 --vertical_results
@@ -2868,10 +2873,24 @@ SHOW CREATE VIEW v_test;
 --horizontal_results
 DROP VIEW v_test;
 
-
+#
+# A VIEW created with sql_mode=DEFAULT, calling a package routine
+#
 SET sql_mode=DEFAULT;
---error ER_SP_DOES_NOT_EXIST
 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+#
+# It also works with sql_mode=ORACLE
+#
+--horizontal_results
+SET sql_mode=ORACLE;
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+DROP VIEW v_test;
 
 
 SET sql_mode=ORACLE;
diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test
index ca3c40bb..7c1463e2 100644
--- a/mysql-test/suite/compat/oracle/t/table_value_constr.test
+++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test
@@ -424,6 +424,7 @@ select * from t2;
 
 --echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL
 
+--sorted_result
 with recursive t2(a,b,st) as
 (
   values(1,1,1)