join.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

# Other clauses refer to NJ columns.
select a,b,c from (t1 natural join t2) natural join (t3 natural join t4)
where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a;
select * from (t1 natural join t2) natural left join (t3 natural join t4)
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;
select * from (t3 natural join t4) natural right join (t1 natural join t2)
where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y;

# Qualified column references to NJ columns.
select * from t1 natural join t2 where t1.c > t2.a;
select * from t1 natural join t2 where t1.b > t2.b;
select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL;

# Nested 'join ... on' - name resolution of ON conditions
select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
select * from t1 natural join (t2 join t4 on b + 1 = y);
select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);

# MySQL extension - 'join ... on' over nested comma operator
select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3;

# MySQL extension - refererence qualified coalesced columns
select * from t1 natural join t2 where t1.b > 0;
select * from t1 natural join (t4 natural join t5) where t4.y > 7;
select * from (t4 natural join t5) natural join t1 where t4.y > 7;
select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;

# MySQL extension - select qualified columns of NJ columns
select t1.*, t2.* from t1 natural join t2;
select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4);

# Queries over subselects in the FROM clause
select * from (select * from t1 natural join t2) as t12
              natural join
              (select * from t3 natural join t4) as t34;
select * from (select * from t1 natural join t2) as t12
              natural left join
              (select * from t3 natural join t4) as t34;
select * from (select * from t3 natural join t4) as t34
              natural right join
              (select * from t1 natural join t2) as t12;

# Queries over views
select * from v1a;
select * from v1b;
select * from v1c;
select * from v1d;
select * from v2a;
select * from v2b;
select * from v3a;
select * from v3b;
select * from v4;
select * from v1a natural join v2a;
select v2a.* from v1a natural join v2a;
select * from v1b join v2a on v1b.b = v2a.c;
select * from v1c join v2a on v1c.b = v2a.c;
select * from v1d join v2a on v1d.a = v2a.c;
select * from v1a join (t3 natural join t4) on a = y;

# TODO: add tests with correlated subqueries for natural join/join on.
# related to BUG#15269


#--------------------------------------------------------------------
# Negative tests (tests for errors)
#--------------------------------------------------------------------
# works in Oracle - bug
-- error 1052
select * from t1 natural join (t3 cross join t4);
# works in Oracle - bug
-- error 1052
select * from (t3 cross join t4) natural join t1;
-- error 1052
select * from t1 join (t2, t3) using (b);
-- error 1052
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
-- error 1052
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
-- error 1052
select * from t6 natural join ((t1 natural join t2),  (t3 natural join t4));
-- error 1052
select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);
-- error 1052
select * from  (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b);
# this one is OK, the next equivalent one is incorrect (bug in Oracle)
-- error 1052
select * from (t3 join (t4 natural join t5) on (b < z))
              natural join
              (t1 natural join t2);
-- error 1052
select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));

-- error 1054
select t1.b from v1a;
-- error 1054
select * from v1a join v1b on t1.b = t2.b;

#
# Bug #17523 natural join and information_schema
#
# We mask out the Privileges column because it differs with embedded server
--replace_column 31 #
select * from information_schema.statistics join information_schema.columns
              using(table_name,column_name) where table_name='user';

drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t6;

drop view v1a;
drop view v1b;
drop view v1c;
drop view v1d;
drop view v2a;
drop view v2b;
drop view v3a;
drop view v3b;
drop view v4;

#
# BUG#15229 - columns of nested joins that are not natural joins incorrectly
# materialized
#
create table t1 (a1 int, a2 int);
create table t2 (a1 int, b int);
create table t3 (c1 int, c2 int);
create table t4 (c2 int);

insert into t1 values (1,1);
insert into t2 values (1,1);
insert into t3 values (1,1);
insert into t4 values (1);

select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2);
select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4;

drop table t1,t2,t3,t4;

#
# BUG#15355: Common natural join column not resolved in prepared statement nested query
#
create table t1 (c int, b int);
create table t2 (a int, b int);
create table t3 (b int, c int);
create table t4 (y int, c int);
create table t5 (y int, z int);

insert into t1 values (3,2);
insert into t2 values (1,2);
insert into t3 values (2,3);
insert into t4 values (1,3);
insert into t5 values (1,4);

# this fails
prepare stmt1 from "select * from ((t3 natural join (t1 natural join t2))
natural join t4) natural join t5";
execute stmt1;

# this works
select * from ((t3 natural join (t1 natural join t2)) natural join t4)
  natural join t5;
drop table t1, t2, t3, t4, t5;

# End of tests for WL#2486 - natural/using join

#
# BUG#25106: A USING clause in combination with a VIEW results in column 
#            aliases ignored
#
CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50));
CREATE TABLE t2 (Test_ID INTEGER);
CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1;

CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2
 USING (Test_ID);
DESCRIBE tv1;
CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
 ON v1.Test_ID = t2.Test_ID;
DESCRIBE tv2;

DROP VIEW v1;
DROP TABLE t1,t2,tv1,tv2;


# BUG#27939: Early NULLs filtering doesn't work for eq_ref access
create table t1 (a int, b int);
insert into t1 values 
  (NULL, 1),
  (NULL, 2),
  (NULL, 3),
  (NULL, 4);

create table t2 (a int not null, primary key(a));
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t3 (a int not null, primary key(a));
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

flush status;
select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
--echo We expect rnd_next=5, and read_key must be 0 because of short-cutting:
show status like 'Handler_read%'; 
drop table t1, t2, t3;


--echo End of 5.0 tests.