summaryrefslogtreecommitdiff
path: root/mysql-test/suite/compat
diff options
context:
space:
mode:
authorOleksandr Byelkin <sanja@mariadb.com>2020-11-17 17:52:08 +0100
committerOleksandr Byelkin <sanja@mariadb.com>2020-11-17 20:11:39 +0100
commitc3d4e5718262c0e385f641fbed055b00397fcbf6 (patch)
treea6179c340de7ccdd78698514b808d4f37bc977e4 /mysql-test/suite/compat
parent10aa5764835ba5bc5fa49a1051e1cc647c0f301b (diff)
downloadmariadb-git-c3d4e5718262c0e385f641fbed055b00397fcbf6.tar.gz
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.
Diffstat (limited to 'mysql-test/suite/compat')
-rw-r--r--mysql-test/suite/compat/oracle/r/anonymous_derived.result86
-rw-r--r--mysql-test/suite/compat/oracle/t/anonymous_derived.test56
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;