join_nested.test

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

       t3
       ON t2.b=t3.b;

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
  FROM t1, t2 
       LEFT JOIN              
       t3
       ON t2.b=t3.b
    WHERE t1.a <= 2;

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b
  FROM t1, t3 
       RIGHT JOIN              
       t2
       ON t2.b=t3.b
    WHERE t1.a <= 2;

SELECT t3.a,t3.b,t4.a,t4.b
  FROM t3,t4;

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t2 
       LEFT JOIN              
       (t3, t4)
       ON t3.a=1 AND t2.b=t4.b;

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t1, t2 
       LEFT JOIN              
       (t3, t4)
       ON t3.a=1 AND t2.b=t4.b
    WHERE t1.a <= 2;

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t1, (t3, t4) 
       RIGHT JOIN              
       t2
       ON t3.a=1 AND t2.b=t4.b
    WHERE t1.a <= 2;

SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t1, (t3, t4)
       RIGHT JOIN              
       t2
       ON t3.a=1 AND t2.b=t4.b
    WHERE t1.a <= 2;

EXPLAIN EXTENDED
SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t1, (t3, t4)
       RIGHT JOIN
       t2
       ON t3.a=1 AND t2.b=t4.b
    WHERE t1.a <= 2;

CREATE INDEX idx_b ON t2(b);

EXPLAIN EXTENDED
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM (t3,t4)
       LEFT JOIN              
       (t1,t2)
       ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM (t3,t4)
       LEFT JOIN              
       (t1,t2)
       ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;

EXPLAIN EXTENDED
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2),
       t9
     WHERE t0.a=1 AND
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL) AND
           (t3.a < 5 OR t3.c IS NULL) AND
           (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
           (t5.a >=2 OR t5.c IS NULL) AND
           (t6.a >=4 OR t6.c IS NULL) AND
           (t7.a <= 2 OR t7.c IS NULL) AND
           (t8.a < 1 OR t8.c IS NULL) AND
           (t8.b=t9.b OR t8.c IS NULL) AND
           (t9.a=1); 

CREATE INDEX idx_b ON t4(b);
CREATE INDEX idx_b ON t5(b);

EXPLAIN EXTENDED
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2),
       t9
     WHERE t0.a=1 AND
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL) AND
           (t3.a < 5 OR t3.c IS NULL) AND
           (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
           (t5.a >=2 OR t5.c IS NULL) AND
           (t6.a >=4 OR t6.c IS NULL) AND
           (t7.a <= 2 OR t7.c IS NULL) AND
           (t8.a < 1 OR t8.c IS NULL) AND
           (t8.b=t9.b OR t8.c IS NULL) AND
           (t9.a=1); 

CREATE INDEX idx_b ON t8(b);

EXPLAIN EXTENDED
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2),
       t9
     WHERE t0.a=1 AND
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL) AND
           (t3.a < 5 OR t3.c IS NULL) AND
           (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
           (t5.a >=2 OR t5.c IS NULL) AND
           (t6.a >=4 OR t6.c IS NULL) AND
           (t7.a <= 2 OR t7.c IS NULL) AND
           (t8.a < 1 OR t8.c IS NULL) AND
           (t8.b=t9.b OR t8.c IS NULL) AND
           (t9.a=1); 

CREATE INDEX idx_b ON t1(b);
CREATE INDEX idx_a ON t0(a);

EXPLAIN EXTENDED
SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2),
       t9
     WHERE t0.a=1 AND
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL) AND
           (t3.a < 5 OR t3.c IS NULL) AND
           (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
           (t5.a >=2 OR t5.c IS NULL) AND
           (t6.a >=4 OR t6.c IS NULL) AND
           (t7.a <= 2 OR t7.c IS NULL) AND
           (t8.a < 1 OR t8.c IS NULL) AND
           (t8.b=t9.b OR t8.c IS NULL) AND
           (t9.a=1); 

SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
       t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
  FROM t0,t1
       LEFT JOIN                
       ( 
         t2
         LEFT JOIN              
         (t3, t4)
         ON t3.a=1 AND t2.b=t4.b,
         t5 
         LEFT JOIN 
         ( 
           (t6, t7)
           LEFT JOIN 
           t8
           ON t7.b=t8.b AND t6.b < 10
         )
         ON t6.b >= 2 AND t5.b=t7.b 
       )
       ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
          (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
          (t1.a != 2),
       t9
     WHERE t0.a=1 AND
           t0.b=t1.b AND          
           (t2.a >= 4 OR t2.c IS NULL) AND
           (t3.a < 5 OR t3.c IS NULL) AND
           (t3.b=t4.b OR t3.c IS NULL OR t4.c IS NULL) AND
           (t5.a >=2 OR t5.c IS NULL) AND
           (t6.a >=4 OR t6.c IS NULL) AND
           (t7.a <= 2 OR t7.c IS NULL) AND
           (t8.a < 1 OR t8.c IS NULL) AND
           (t8.b=t9.b OR t8.c IS NULL) AND
           (t9.a=1); 

SELECT t2.a,t2.b
  FROM t2;

SELECT t3.a,t3.b
  FROM t3;

SELECT t2.a,t2.b,t3.a,t3.b
  FROM t2 LEFT JOIN t3 ON t2.b=t3.b
    WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);

SELECT t2.a,t2.b,t3.a,t3.b
  FROM t2 LEFT JOIN (t3) ON t2.b=t3.b
    WHERE t2.a = 4 OR (t2.a > 4 AND t3.a IS NULL);

ALTER TABLE t3
  CHANGE COLUMN a a1 int,
  CHANGE COLUMN c c1 int; 

SELECT t2.a,t2.b,t3.a1,t3.b
  FROM t2 LEFT JOIN t3 ON t2.b=t3.b
    WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);

SELECT t2.a,t2.b,t3.a1,t3.b
  FROM t2 NATURAL LEFT JOIN t3
    WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);

DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;

CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int);

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (2);
INSERT INTO t1 VALUES (2);

#check proper syntax for nested outer joins

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a;

#must be equivalent to:

SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;

#check that everything is al right when all tables contain not more than 1 row
#(bug #4922)

DELETE FROM t1 WHERE a=2;
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;
DELETE FROM t2;
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a;

DROP TABLE t1,t2,t3;

#on expression for a nested outer join does not depend on the outer table
#bug #4976

CREATE TABLE t1(a int, key (a));                                              
CREATE TABLE t2(b int, key (b));                                               
CREATE TABLE t3(c int, key (c));                                               

INSERT INTO t1 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);

INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);

INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);
 
EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

DELETE FROM t3;
EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;

DROP TABLE t1,t2,t3;

#
# Test for bug #11284: empty table in a nested left join
# 

CREATE TABLE t1 (c11 int);
CREATE TABLE t2 (c21 int);
CREATE TABLE t3 (c31 int);

INSERT INTO t1 VALUES (4), (5);

SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;

DROP TABLE t1,t2,t3;

#
# Bug #12154: creation of temp table for a query with nested outer join
# 

CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
INSERT INTO t1 VALUES (23, 2340), (26, 9900);

CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2));
INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr');

create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL);
INSERT INTO t3 VALUES (3,23), (6,26);

CREATE TABLE t4 (groupid int(12));
INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6);

SELECT * FROM
(SELECT DISTINCT gl.groupid, gp.price
  FROM t4 gl 
       LEFT JOIN
       (t3 g INNER JOIN t2 p ON g.goodsid = p.goods 
             INNER JOIN t1 gp ON p.goods = gp.goods)
       ON gl.groupid = g.groupid and p.shop = 'fr') t;

CREATE VIEW v1 AS
SELECT g.groupid groupid, p.goods goods,  
       p.name name, p.shop shop, 
       gp.price price
  FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods
            INNER JOIN t1 gp on p.goods = gp.goods;

CREATE VIEW v2 AS
SELECT DISTINCT g.groupid, fr.price
  FROM t4 g
       LEFT JOIN
       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr';

SELECT * FROM v2;

SELECT * FROM 
(SELECT DISTINCT g.groupid, fr.price
  FROM t4 g
       LEFT JOIN
       v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t;

DROP VIEW v1,v2;