join_nested.test

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

--disable_warnings
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings

CREATE TABLE t0 (a int, b int, c int);
CREATE TABLE t1 (a int, b int, c int);
CREATE TABLE t2 (a int, b int, c int);
CREATE TABLE t3 (a int, b int, c int);
CREATE TABLE t4 (a int, b int, c int);
CREATE TABLE t5 (a int, b int, c int);
CREATE TABLE t6 (a int, b int, c int);
CREATE TABLE t7 (a int, b int, c int);
CREATE TABLE t8 (a int, b int, c int);
CREATE TABLE t9 (a int, b int, c int);

INSERT INTO t0 VALUES (1,1,0), (1,2,0), (2,2,0);
INSERT INTO t1 VALUES (1,3,0), (2,2,0), (3,2,0);
INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
INSERT INTO t3 VALUES (1,2,0), (2,2,0);
INSERT INTO t4 VALUES (3,2,0), (4,2,0);
INSERT INTO t5 VALUES (3,1,0), (2,2,0), (3,3,0);
INSERT INTO t6 VALUES (3,2,0), (6,2,0), (6,1,0);
INSERT INTO t7 VALUES (1,1,0), (2,2,0);
INSERT INTO t8 VALUES (0,2,0), (1,2,0);
INSERT INTO t9 VALUES (1,1,0), (1,2,0), (3,3,0);


SELECT t2.a,t2.b
  FROM t2;

SELECT t3.a,t3.b
  FROM t3;

SELECT t4.a,t4.b
  FROM t4;

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 t2.b=t4.b;

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;

EXPLAIN EXTENDED
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t2.b=t4.b
    WHERE t3.a=1 OR t3.c IS NULL;

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
  FROM t2
       LEFT JOIN              
       (t3, t4)
       ON t2.b=t4.b
    WHERE t3.a=1 OR t3.c IS NULL;

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

SELECT t5.a,t5.b
  FROM t5;

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

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

EXPLAIN EXTENDED
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
  FROM t2
       LEFT JOIN              
       (t3, t4, t5)
       ON t2.b=t4.b
    WHERE t3.a>1 OR t3.c IS NULL;

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
  FROM t2
       LEFT JOIN              
       (t3, t4, t5)
       ON t2.b=t4.b
    WHERE t3.a>1 OR t3.c IS NULL;

EXPLAIN EXTENDED
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
  FROM t2
       LEFT JOIN              
       (t3, t4, t5)
       ON t2.b=t4.b
    WHERE (t3.a>1 OR t3.c IS NULL) AND 
          (t5.a<3 OR t5.c IS NULL);

SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
  FROM t2
       LEFT JOIN              
       (t3, t4, t5)
       ON t2.b=t4.b
    WHERE (t3.a>1 OR t3.c IS NULL) AND 
          (t5.a<3 OR t5.c IS NULL);

SELECT t6.a,t6.b
  FROM t6;

SELECT t7.a,t7.b
  FROM t7;

SELECT t6.a,t6.b,t7.a,t7.b
  FROM t6,t7;

SELECT t8.a,t8.b
  FROM t8;

EXPLAIN EXTENDED
SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM (t6, t7)
       LEFT JOIN 
       t8
       ON t7.b=t8.b AND t6.b < 10;

SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM (t6, t7)
       LEFT JOIN 
       t8
       ON t7.b=t8.b AND t6.b < 10;

SELECT t5.a,t5.b
  FROM t5;

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM 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;

SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
  FROM 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 AND
          (t8.a < 1 OR t8.c IS NULL);

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 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
  FROM 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; 

SELECT 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
  FROM 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
    WHERE t2.a > 3 AND
          (t6.a < 6 OR t6.c IS NULL);

SELECT t1.a,t1.b
  FROM t1;

SELECT 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
  FROM 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);

SELECT 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
  FROM 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)
    WHERE (t2.a >= 4 OR t2.c IS NULL);
   
SELECT t0.a,t0.b
  FROM t0;

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
  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)
    WHERE t0.a=1 AND
          t0.b=t1.b AND          
          (t2.a >= 4 OR t2.c IS NULL); 

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
  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)
    WHERE t0.a=1 AND
          t0.b=t1.b AND          
          (t2.a >= 4 OR t2.c IS NULL); 

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 t9.a,t9.b
  FROM t9;

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 t1.a,t1.b
  FROM t1;

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