join.test

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

= '1'  AND t1.Column_ID=t3.Column_ID AND 
t1.Language_ID=t3.Language_ID AND ( 
t3.Contractor_ID = '4'  OR 
t3.Contractor_ID = '999999'  OR 
t3.Contractor_ID = '1') AND 
t3.CanRead='1' AND t3.Active='1';
drop table t1,t2,t3;

#
# Bug when doing full join and NULL fields.
#

CREATE TABLE t1 (
  t1_id int(11) default NULL,
  t2_id int(11) default NULL,
  type enum('Cost','Percent') default NULL,
  cost_unit enum('Cost','Unit') default NULL,
  min_value double default NULL,
  max_value double default NULL,
  t3_id int(11) default NULL,
  item_id int(11) default NULL
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1);
CREATE TABLE t2 (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(255) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
select t1.*, t2.*  from t1, t2 where t2.id=t1.t2_id limit 2;
drop table t1,t2;

#
# Bug in range optimiser with MAYBE_KEY
#

CREATE TABLE t1 (
  siteid varchar(25) NOT NULL default '',
  emp_id varchar(30) NOT NULL default '',
  rate_code varchar(10) default NULL,
  UNIQUE KEY site_emp (siteid,emp_id),
  KEY siteid (siteid)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust');
CREATE TABLE t2 (
  siteid varchar(25) NOT NULL default '',
  rate_code varchar(10) NOT NULL default '',
  base_rate float NOT NULL default '0',
  PRIMARY KEY  (siteid,rate_code),
  FULLTEXT KEY rate_code (rate_code)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('rivercats','cust',20);
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
drop table t1,t2;

#
# Problem with internal list handling when reducing WHERE
#

CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
INSERT INTO t1 VALUES (1, 'A');
INSERT INTO t2 VALUES (1, 'B');

SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
drop table t1,t2;

#
# dummy natural join (no common columns) Bug #4807
#

CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
CREATE TABLE t3 (c int);
SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
DROP TABLE t1, t2, t3;

#
# Test combination of join methods
#

create table t1 (i int);
create table t2 (i int);
create table t3 (i int);
insert into t1 values(1),(2);
insert into t2 values(2),(3);
insert into t3 values (2),(4);

select * from t1 natural left join t2;
select * from t1 left join t2 on (t1.i=t2.i);
select * from t1 natural left join t2 natural left join t3;
select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);

select * from t3 natural right join t2;
select * from t3 right join t2 on (t3.i=t2.i);
select * from t3 natural right join t2 natural right join t1;
select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);

select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;

select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
drop table t1,t2,t3;

#
# Bug #27531: Query performance degredation in 4.1.22 and greater
#
CREATE TABLE t1 (a int, b int default 0, c int default 1);

INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO t1 (a) SELECT a + 8 FROM t1;
INSERT INTO t1 (a) SELECT a + 16 FROM t1;

CREATE TABLE t2 (a int, d int, e int default 0);

INSERT INTO t2 (a, d) VALUES (1,1),(2,2),(3,3),(4,4);
INSERT INTO t2 (a, d) SELECT a+4, a+4 FROM t2;
INSERT INTO t2 (a, d) SELECT a+8, a+8 FROM t2;

# should use join cache
EXPLAIN
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
  ORDER BY t1.b, t1.c;
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
  ORDER BY t1.b, t1.c;

DROP TABLE t1,t2;

# End of 4.1 tests

#
#  Tests for WL#2486 Natural/using join according to SQL:2003.
#
#  NOTICE:
#  - The tests are designed so that all statements, except MySQL
#    extensions run on any SQL server. Please do no change.
#  - Tests marked with TODO will be submitted as bugs.
#

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);
create table t6 (a int, c int);

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

# Views with simple natural join.
create algorithm=merge view v1a as
select * from t1 natural join t2;
# as above, but column names are cross-renamed: a->c, c->b, b->a
create algorithm=merge view v1b(a,b,c) as
select * from t1 natural join t2;
# as above, but column names are aliased: a->c, c->b, b->a
create algorithm=merge view v1c as
select b as a, c as b, a as c from t1 natural join t2;
#  as above, but column names are cross-renamed, and aliased
#  a->c->b, c->b->a, b->a->c
create algorithm=merge view v1d(b, a, c) as
select a as c, c as b, b as a from t1 natural join t2;

# Views with JOIN ... ON
create algorithm=merge view v2a as
select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;
create algorithm=merge view v2b as
select t1.c as b, t1.b as a, t2.a as c
from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c;

# Views with bigger natural join
create algorithm=merge view v3a as
select * from t1 natural join t2 natural join t3;
create algorithm=merge view v3b as
select * from t1 natural join (t2 natural join t3);

# View over views with mixed natural join and join ... on
create algorithm=merge view v4 as
select * from v2a natural join v3a;

# Nested natural/using joins.
select * from (t1 natural join t2) natural join (t3 natural join t4);
select * from (t1 natural join t2) natural left join (t3 natural join t4);
select * from (t3 natural join t4) natural right join (t1 natural join t2);
select * from (t1 natural left join t2) natural left join (t3 natural left join t4);
select * from (t4 natural right join t3) natural right join (t2 natural right join t1);
select * from t1 natural join t2 natural join t3 natural join t4;
select * from ((t1 natural join t2) natural join t3) natural join t4;
select * from t1 natural join (t2 natural join (t3 natural join t4));
# BUG#15355: this query fails in 'prepared statements' mode
# select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5;
# select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5;
select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3));
select * from (t1 natural join t2), (t3 natural join t4);
# MySQL extension - nested comma ',' operator instead of cross join.
select * from t5 natural join ((t1 natural join t2), (t3 natural join t4));
select * from  ((t1 natural join t2),  (t3 natural join t4)) natural join t5;
select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4));
select * from  ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5;

select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c);
select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c));