Debian Codegraph @

mysql-test/suite/compat/oracle/r/anonymous_derived.result

raw
1 #
2 # MDEV-19162: anonymous derived tables part
3 #
4 set @save_sql_mode=@@sql_mode;
5 set session sql_mode=ORACLE;
6 SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL);
7 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
8 def __2 1 1 3 1 1 N 32769 0 63
9 def __3 2 2 3 1 1 N 32769 0 63
10 1 2
11 1 2
12 create table t1 (a int);
13 insert into t1 values (2),(3);
14 create table t2 (a int);
15 insert into t2 values (2),(3);
16 select t1.a from t1, (select * from t2 where t2.a<= 2);
17 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
18 def test t1 t1 a a 3 11 1 Y 32768 0 63
19 a
20 2
21 3
22 select t1.a, b from t1, (select a as b from t2 where t2.a<= 2);
23 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
24 def test t1 t1 a a 3 11 1 Y 32768 0 63
25 def test __2 __2 b b 3 11 1 Y 32768 0 63
26 a b
27 2 2
28 3 2
29 select t1.a, b from t1, (select max(a) as b from t2);
30 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
31 def test t1 t1 a a 3 11 1 Y 32768 0 63
32 def t2 __2 b b 3 11 1 Y 32768 0 63
33 a b
34 2 3
35 3 3
36 explain extended
37 select t1.a, b from t1, (select max(a) as b from t2);
38 id select_type table type possible_keys key key_len ref rows filtered Extra
39 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
40 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
41 2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00
42 Warnings:
43 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"
44 select * from (select tt.* from (select * from t1) as tt) where tt.a > 0;
45 ERROR 42S22: Unknown column 'tt.a' in 'WHERE'
46 select * from (select tt.* from (select * from t1) as tt) where a > 0;
47 a
48 2
49 3
50 create view v1 as select t1.a, b from t1, (select max(a) as b from t2);
51 select * from v1;
52 a b
53 2 3
54 3 3
55 create procedure p1
56 as
57 begin
58 select t1.a, b from t1, (select max(a) as b from t2);
59 end/
60 call p1;
61 a b
62 2 3
63 3 3
64 SET sql_mode=default;
65 select * from v1;
66 a b
67 2 3
68 3 3
69 show create view v1;
70 View Create View character_set_client collation_connection
71 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`__3`.`b` AS `b` from (`t1` join (select max(`t2`.`a`) AS `b` from `t2`) `__3`) latin1 latin1_swedish_ci
72 call p1;
73 a b
74 2 3
75 3 3
76 show create procedure p1;
77 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
78 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"()
79 as
80 begin
81 select t1.a, b from t1, (select max(a) as b from t2);
82 end latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
83 drop view v1;
84 drop procedure p1;
85 drop table t1,t2;
86 set session sql_mode=@save_sql_mode;