MDEV-19162 anonymous derived tables partbb-10.6-MDEV-19162-anonymous_derived

The idea of Alexander Barkov: to add name derived as a slect id.

2 files changed, 142 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/anonymous_derived.result b/mysql-test/suite/compat/oracle/r/anonymous_derived.result
new file mode 100644
index 00000000000..85868794130
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/anonymous_derived.result
@@ -0,0 +1,86 @@
+#
+# MDEV-19162: anonymous derived tables part
+#
+set @save_sql_mode=@@sql_mode;
+set session sql_mode=ORACLE;
+SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL);
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def			__2	1	1	3	1	1	N	32769	0	63
+def			__3	2	2	3	1	1	N	32769	0	63
+1	2
+1	2
+create table t1 (a int);
+insert into t1 values (2),(3);
+create table t2 (a int);
+insert into t2 values (2),(3);
+select t1.a from t1, (select * from t2 where t2.a<= 2);
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	t1	a	a	3	11	1	Y	32768	0	63
+a
+2
+3
+select t1.a, b from t1, (select a as b from t2 where t2.a<= 2);
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	t1	a	a	3	11	1	Y	32768	0	63
+def	test	__2	__2	b	b	3	11	1	Y	32768	0	63
+a	b
+2	2
+3	2
+select t1.a, b from t1, (select max(a) as b from t2);
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	t1	a	a	3	11	1	Y	32768	0	63
+def		t2	__2	b	b	3	11	1	Y	32768	0	63
+a	b
+2	3
+3	3
+explain extended
+select t1.a, b from t1, (select max(a) as b from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","__2"."b" AS "b" from "test"."t1" join (/* select#2 */ select max("test"."t2"."a") AS "b" from "test"."t2") "__2"
+select * from (select tt.* from (select * from t1) as tt) where tt.a > 0;
+ERROR 42S22: Unknown column 'tt.a' in 'where clause'
+select * from (select tt.* from (select * from t1) as tt) where a > 0;
+a
+2
+3
+create view v1 as select t1.a, b from t1, (select max(a) as b from t2);
+select * from v1;
+a	b
+2	3
+3	3
+create procedure p1
+as
+begin
+select t1.a, b from t1, (select max(a) as b from t2);
+end/
+call p1;
+a	b
+2	3
+3	3
+SET sql_mode=default;
+select * from v1;
+a	b
+2	3
+3	3
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a`,`__3`.`b` AS `b` from (`test`.`t1` join (select max(`test`.`t2`.`a`) AS `b` from `test`.`t2`) `__3`)	latin1	latin1_swedish_ci
+call p1;
+a	b
+2	3
+3	3
+show create procedure p1;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+p1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
+as
+begin
+select t1.a, b from t1, (select max(a) as b from t2);
+end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop view v1;
+drop procedure p1;
+drop table t1,t2;
+set session sql_mode=@save_sql_mode;
diff --git a/mysql-test/suite/compat/oracle/t/anonymous_derived.test b/mysql-test/suite/compat/oracle/t/anonymous_derived.test
new file mode 100644
index 00000000000..7a9ee2161ee
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/anonymous_derived.test
@@ -0,0 +1,56 @@
+--echo #
+--echo # MDEV-19162: anonymous derived tables part
+--echo #
+
+set @save_sql_mode=@@sql_mode;
+set session sql_mode=ORACLE;
+
+--disable_ps_protocol
+--enable_metadata
+SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL);
+--disable_metadata
+--enable_ps_protocol
+create table t1 (a int);
+insert into t1 values (2),(3);
+create table t2 (a int);
+insert into t2 values (2),(3);
+--disable_ps_protocol
+--enable_metadata
+select t1.a from t1, (select * from t2 where t2.a<= 2);
+select t1.a, b from t1, (select a as b from t2 where t2.a<= 2);
+select t1.a, b from t1, (select max(a) as b from t2);
+--disable_metadata
+--enable_ps_protocol
+explain extended
+select t1.a, b from t1, (select max(a) as b from t2);
+--error ER_BAD_FIELD_ERROR
+select * from (select tt.* from (select * from t1) as tt) where tt.a > 0;
+select * from (select tt.* from (select * from t1) as tt) where a > 0;
+
+create view v1 as select t1.a, b from t1, (select max(a) as b from t2);
+
+select * from v1;
+
+
+DELIMITER /;
+create procedure p1
+as
+begin
+  select t1.a, b from t1, (select max(a) as b from t2);
+end/
+DELIMITER ;/
+
+call p1;
+
+SET sql_mode=default;
+
+select * from v1;
+show create view v1;
+
+call p1;
+show create procedure p1;
+
+drop view v1;
+drop procedure p1;
+drop table t1,t2;
+set session sql_mode=@save_sql_mode;