diff options
-rw-r--r-- | mysql-test/suite/compat/oracle/r/anonymous_derived.result | 86 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/t/anonymous_derived.test | 56 | ||||
-rw-r--r-- | sql/sql_yacc.yy | 10 |
3 files changed, 152 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; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 607a68caf43..78e2a8f1fe5 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11743,6 +11743,16 @@ table_primary_derived: if (!($$= Lex->parsed_derived_table($1->master_unit(), $2, $3))) MYSQL_YYABORT; } +/* Start SQL_MODE_ORACLE_SPECIFIC + | subquery + opt_for_system_time_clause + { + LEX_CSTRING alias; + if ($1->make_unique_derived_name(thd, &alias) || + !($$= Lex->parsed_derived_table($1->master_unit(), $2, &alias))) + MYSQL_YYABORT; + } +End SQL_MODE_ORACLE_SPECIFIC */ ; opt_outer: |