join.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 861 行 · 第 1/3 页

3	2	11	4
select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
c	b	a	b	y	c
3	1	2	1	2	3
3	2	2	1	2	3
select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c;
a	b	y	c	c	b
2	1	2	3	3	1
2	1	2	3	3	2
select * from t1 natural join (t2 join t4 on b + 1 = y);
c	b	a	y
3	1	2	2
select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c);
c	b	a	b	b	c	y	c
10	1	2	1	1	3	11	3
10	1	2	1	1	10	11	3
3	1	2	1	1	3	11	3
3	1	2	1	1	10	11	3
3	2	2	1	1	3	11	3
3	2	2	1	1	10	11	3
select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c);
c	b	a	b	b	c	y	c
10	1	2	1	1	3	11	3
10	1	2	1	1	10	11	3
3	1	2	1	1	3	11	3
3	1	2	1	1	10	11	3
3	2	2	1	1	3	11	3
3	2	2	1	1	10	11	3
select * from (t1 natural join t2) join (t3 natural join t4) on a = y;
b	c	a	c	b	y
1	10	2	3	1	2
1	3	2	3	1	2
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;
b	c	c	b	a	b	y	c	y	z
1	3	10	1	2	1	11	3	11	4
1	10	10	1	2	1	11	3	11	4
1	3	3	1	2	1	11	3	11	4
1	10	3	1	2	1	11	3	11	4
select * from t1 natural join t2 where t1.b > 0;
b	c	a
1	10	2
1	3	2
select * from t1 natural join (t4 natural join t5) where t4.y > 7;
c	b	y	z
3	1	11	4
3	2	11	4
select * from (t4 natural join t5) natural join t1 where t4.y > 7;
c	y	z	b
3	11	4	1
3	11	4	2
select * from t1 natural left join (t4 natural join t5) where t4.y > 7;
c	b	y	z
3	1	11	4
3	2	11	4
select * from (t4 natural join t5) natural right join t1 where t4.y > 7;
c	b	y	z
3	1	11	4
3	2	11	4
select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b;
b	c	a	c	b	y
1	10	2	3	1	11
1	10	2	3	1	2
1	3	2	3	1	11
1	3	2	3	1	2
select t1.*, t2.* from t1 natural join t2;
c	b	a	b
10	1	2	1
3	1	2	1
select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4);
c	b	a	b	b	c	y	c
3	1	2	1	1	3	11	3
3	1	2	1	1	3	2	3
select * from (select * from t1 natural join t2) as t12
natural join
(select * from t3 natural join t4) as t34;
b	c	a	y
1	3	2	11
1	3	2	2
select * from (select * from t1 natural join t2) as t12
natural left join
(select * from t3 natural join t4) as t34;
b	c	a	y
1	10	2	NULL
1	3	2	11
1	3	2	2
select * from (select * from t3 natural join t4) as t34
natural right join
(select * from t1 natural join t2) as t12;
b	c	a	y
1	10	2	NULL
1	3	2	11
1	3	2	2
select * from v1a;
b	c	a
1	10	2
1	3	2
select * from v1b;
a	b	c
1	10	2
1	3	2
select * from v1c;
a	b	c
1	10	2
1	3	2
select * from v1d;
b	a	c
2	10	1
2	3	1
select * from v2a;
c	b	a
3	1	2
3	2	2
select * from v2b;
b	a	c
3	1	2
3	2	2
select * from v3a;
b	c	a
1	10	2
1	3	2
select * from v3b;
c	b	a
10	1	2
3	1	2
select * from v4;
c	b	a
3	1	2
select * from v1a natural join v2a;
b	c	a
1	3	2
select v2a.* from v1a natural join v2a;
c	b	a
3	1	2
select * from v1b join v2a on v1b.b = v2a.c;
a	b	c	c	b	a
1	3	2	3	1	2
1	3	2	3	2	2
select * from v1c join v2a on v1c.b = v2a.c;
a	b	c	c	b	a
1	3	2	3	1	2
1	3	2	3	2	2
select * from v1d join v2a on v1d.a = v2a.c;
b	a	c	c	b	a
2	3	1	3	1	2
2	3	1	3	2	2
select * from v1a join (t3 natural join t4) on a = y;
b	c	a	c	b	y
1	10	2	3	1	2
1	3	2	3	1	2
select * from t1 natural join (t3 cross join t4);
ERROR 23000: Column 'c' in from clause is ambiguous
select * from (t3 cross join t4) natural join t1;
ERROR 23000: Column 'c' in from clause is ambiguous
select * from t1 join (t2, t3) using (b);
ERROR 23000: Column 'b' in from clause is ambiguous
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
ERROR 23000: Column 'c' in from clause is ambiguous
select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6;
ERROR 23000: Column 'c' in from clause is ambiguous
select * from t6 natural join ((t1 natural join t2),  (t3 natural join t4));
ERROR 23000: Column 'c' in from clause is ambiguous
select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4);
ERROR 23000: Column 'b' in from clause is ambiguous
select * from  (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b);
ERROR 23000: Column 'b' in from clause is ambiguous
select * from (t3 join (t4 natural join t5) on (b < z))
natural join
(t1 natural join t2);
ERROR 23000: Column 'c' in from clause is ambiguous
select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z));
ERROR 23000: Column 'c' in from clause is ambiguous
select t1.b from v1a;
ERROR 42S22: Unknown column 't1.b' in 'field list'
select * from v1a join v1b on t1.b = t2.b;
ERROR 42S22: Unknown column 't1.b' in 'on clause'
select * from information_schema.statistics join information_schema.columns
using(table_name,column_name) where table_name='user';
TABLE_NAME	COLUMN_NAME	TABLE_CATALOG	TABLE_SCHEMA	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	TABLE_CATALOG	TABLE_SCHEMA	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT
user	Host	NULL	mysql	0	mysql	PRIMARY	1	A	NULL	NULL	NULL		BTREE		NULL	mysql	1		NO	char	60	180	NULL	NULL	utf8	utf8_bin	char(60)	PRI		#	
user	User	NULL	mysql	0	mysql	PRIMARY	2	A	3	NULL	NULL		BTREE		NULL	mysql	2		NO	char	16	48	NULL	NULL	utf8	utf8_bin	char(16)	PRI		#	
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;
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);
c2	a1	a2	b	c1
1	1	1	1	1
select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
c2	c1	a1	a2	b
1	1	1	1	1
select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2);
a2
1
select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2);
a2
1
select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2);
a2
1
select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4;
a2
1
drop table t1,t2,t3,t4;
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);
prepare stmt1 from "select * from ((t3 natural join (t1 natural join t2))
natural join t4) natural join t5";
execute stmt1;
y	c	b	a	z
1	3	2	1	4
select * from ((t3 natural join (t1 natural join t2)) natural join t4)
natural join t5;
y	c	b	a	z
1	3	2	1	4
drop table t1, t2, t3, t4, t5;
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;
Field	Type	Null	Key	Default	Extra
Name	varchar(50)	YES		NULL	
CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2
ON v1.Test_ID = t2.Test_ID;
DESCRIBE tv2;
Field	Type	Null	Key	Default	Extra
Name	varchar(50)	YES		NULL	
DROP VIEW v1;
DROP TABLE t1,t2,tv1,tv2;
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;
a	b	a	a
explain select * from t1, t2, t3 where t3.a=t1.a and t2.a=t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
We expect rnd_next=5, and read_key must be 0 because of short-cutting:
show status like 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	5
drop table t1, t2, t3;
End of 5.0 tests.