1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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'
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 `t1`.`a` AS `a`,`__3`.`b` AS `b` from (`t1` join (select max(`t2`.`a`) AS `b` from `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 utf8mb4_uca1400_ai_ci
drop view v1;
drop procedure p1;
drop table t1,t2;
set session sql_mode=@save_sql_mode;
|
