1 # 2002 May 24

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.

    12 #

    13 # This file implements tests for joins, including outer joins.

    14 #

    15 # $Id: join.test,v 1.25 2008/08/14 00:19:49 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 do_test join-1.1 {

    21   execsql {

    22     CREATE TABLE t1(a,b,c);

    23     INSERT INTO t1 VALUES(1,2,3);

    24     INSERT INTO t1 VALUES(2,3,4);

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

    26     SELECT * FROM t1;

    27   }  

    28 } {1 2 3 2 3 4 3 4 5}

    29 do_test join-1.2 {

    30   execsql {

    31     CREATE TABLE t2(b,c,d);

    32     INSERT INTO t2 VALUES(1,2,3);

    33     INSERT INTO t2 VALUES(2,3,4);

    34     INSERT INTO t2 VALUES(3,4,5);

    35     SELECT * FROM t2;

    36   }  

    37 } {1 2 3 2 3 4 3 4 5}

    38 

    39 do_test join-1.3 {

    40   execsql2 {

    41     SELECT * FROM t1 NATURAL JOIN t2;

    42   }

    43 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}

    44 do_test join-1.3.1 {

    45   execsql2 {

    46     SELECT * FROM t2 NATURAL JOIN t1;

    47   }

    48 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}

    49 do_test join-1.3.2 {

    50   execsql2 {

    51     SELECT * FROM t2 AS x NATURAL JOIN t1;

    52   }

    53 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}

    54 do_test join-1.3.3 {

    55   execsql2 {

    56     SELECT * FROM t2 NATURAL JOIN t1 AS y;

    57   }

    58 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}

    59 do_test join-1.3.4 {

    60   execsql {

    61     SELECT b FROM t1 NATURAL JOIN t2;

    62   }

    63 } {2 3}

    64 do_test join-1.4.1 {

    65   execsql2 {

    66     SELECT * FROM t1 INNER JOIN t2 USING(b,c);

    67   }

    68 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}

    69 do_test join-1.4.2 {

    70   execsql2 {

    71     SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);

    72   }

    73 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}

    74 do_test join-1.4.3 {

    75   execsql2 {

    76     SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);

    77   }

    78 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}

    79 do_test join-1.4.4 {

    80   execsql2 {

    81     SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);

    82   }

    83 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}

    84 do_test join-1.4.5 {

    85   execsql {

    86     SELECT b FROM t1 JOIN t2 USING(b);

    87   }

    88 } {2 3}

    89 do_test join-1.5 {

    90   execsql2 {

    91     SELECT * FROM t1 INNER JOIN t2 USING(b);

    92   }

    93 } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}

    94 do_test join-1.6 {

    95   execsql2 {

    96     SELECT * FROM t1 INNER JOIN t2 USING(c);

    97   }

    98 } {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}

    99 do_test join-1.7 {

   100   execsql2 {

   101     SELECT * FROM t1 INNER JOIN t2 USING(c,b);

   102   }

   103 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}

   104 

   105 do_test join-1.8 {

   106   execsql {

   107     SELECT * FROM t1 NATURAL CROSS JOIN t2;

   108   }

   109 } {1 2 3 4 2 3 4 5}

   110 do_test join-1.9 {

   111   execsql {

   112     SELECT * FROM t1 CROSS JOIN t2 USING(b,c);

   113   }

   114 } {1 2 3 4 2 3 4 5}

   115 do_test join-1.10 {

   116   execsql {

   117     SELECT * FROM t1 NATURAL INNER JOIN t2;

   118   }

   119 } {1 2 3 4 2 3 4 5}

   120 do_test join-1.11 {

   121   execsql {

   122     SELECT * FROM t1 INNER JOIN t2 USING(b,c);

   123   }

   124 } {1 2 3 4 2 3 4 5}

   125 do_test join-1.12 {

   126   execsql {

   127     SELECT * FROM t1 natural inner join t2;

   128   }

   129 } {1 2 3 4 2 3 4 5}

   130 

   131 ifcapable subquery {

   132   do_test join-1.13 {

   133     execsql2 {

   134       SELECT * FROM t1 NATURAL JOIN 

   135         (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3

   136     }

   137   } {a 1 b 2 c 3 d 4 e 5}

   138   do_test join-1.14 {

   139     execsql2 {

   140       SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'

   141           NATURAL JOIN t1

   142     }

   143   } {c 3 d 4 e 5 a 1 b 2}

   144 }

   145 

   146 do_test join-1.15 {

   147   execsql {

   148     CREATE TABLE t3(c,d,e);

   149     INSERT INTO t3 VALUES(2,3,4);

   150     INSERT INTO t3 VALUES(3,4,5);

   151     INSERT INTO t3 VALUES(4,5,6);

   152     SELECT * FROM t3;

   153   }  

   154 } {2 3 4 3 4 5 4 5 6}

   155 do_test join-1.16 {

   156   execsql {

   157     SELECT * FROM t1 natural join t2 natural join t3;

   158   }

   159 } {1 2 3 4 5 2 3 4 5 6}

   160 do_test join-1.17 {

   161   execsql2 {

   162     SELECT * FROM t1 natural join t2 natural join t3;

   163   }

   164 } {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}

   165 do_test join-1.18 {

   166   execsql {

   167     CREATE TABLE t4(d,e,f);

   168     INSERT INTO t4 VALUES(2,3,4);

   169     INSERT INTO t4 VALUES(3,4,5);

   170     INSERT INTO t4 VALUES(4,5,6);

   171     SELECT * FROM t4;

   172   }  

   173 } {2 3 4 3 4 5 4 5 6}

   174 do_test join-1.19.1 {

   175   execsql {

   176     SELECT * FROM t1 natural join t2 natural join t4;

   177   }

   178 } {1 2 3 4 5 6}

   179 do_test join-1.19.2 {

   180   execsql2 {

   181     SELECT * FROM t1 natural join t2 natural join t4;

   182   }

   183 } {a 1 b 2 c 3 d 4 e 5 f 6}

   184 do_test join-1.20 {

   185   execsql {

   186     SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1

   187   }

   188 } {1 2 3 4 5}

   189 

   190 do_test join-2.1 {

   191   execsql {

   192     SELECT * FROM t1 NATURAL LEFT JOIN t2;

   193   }

   194 } {1 2 3 4 2 3 4 5 3 4 5 {}}

   195 do_test join-2.2 {

   196   execsql {

   197     SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;

   198   }

   199 } {1 2 3 {} 2 3 4 1 3 4 5 2}

   200 do_test join-2.3 {

   201   catchsql {

   202     SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;

   203   }

   204 } {1 {RIGHT and FULL OUTER JOINs are not currently supported}}

   205 do_test join-2.4 {

   206   execsql {

   207     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d

   208   }

   209 } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}

   210 do_test join-2.5 {

   211   execsql {

   212     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1

   213   }

   214 } {2 3 4 {} {} {} 3 4 5 1 2 3}

   215 do_test join-2.6 {

   216   execsql {

   217     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1

   218   }

   219 } {1 2 3 {} {} {} 2 3 4 {} {} {}}

   220 

   221 do_test join-3.1 {

   222   catchsql {

   223     SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;

   224   }

   225 } {1 {a NATURAL join may not have an ON or USING clause}}

   226 do_test join-3.2 {

   227   catchsql {

   228     SELECT * FROM t1 NATURAL JOIN t2 USING(b);

   229   }

   230 } {1 {a NATURAL join may not have an ON or USING clause}}

   231 do_test join-3.3 {

   232   catchsql {

   233     SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);

   234   }

   235 } {1 {cannot have both ON and USING clauses in the same join}}

   236 do_test join-3.4.1 {

   237   catchsql {

   238     SELECT * FROM t1 JOIN t2 USING(a);

   239   }

   240 } {1 {cannot join using column a - column not present in both tables}}

   241 do_test join-3.4.2 {

   242   catchsql {

   243     SELECT * FROM t1 JOIN t2 USING(d);

   244   }

   245 } {1 {cannot join using column d - column not present in both tables}}

   246 do_test join-3.5 {

   247   catchsql {

   248     SELECT * FROM t1 USING(a);

   249   }

   250 } {0 {1 2 3 2 3 4 3 4 5}}

   251 do_test join-3.6 {

   252   catchsql {

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

   254   }

   255 } {1 {no such column: t3.a}}

   256 do_test join-3.7 {

   257   catchsql {

   258     SELECT * FROM t1 INNER OUTER JOIN t2;

   259   }

   260 } {1 {unknown or unsupported join type: INNER OUTER}}

   261 do_test join-3.8 {

   262   catchsql {

   263     SELECT * FROM t1 INNER OUTER CROSS JOIN t2;

   264   }

   265 } {1 {unknown or unsupported join type: INNER OUTER CROSS}}

   266 do_test join-3.9 {

   267   catchsql {

   268     SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;

   269   }

   270 } {1 {unknown or unsupported join type: OUTER NATURAL INNER}}

   271 do_test join-3.10 {

   272   catchsql {

   273     SELECT * FROM t1 LEFT BOGUS JOIN t2;

   274   }

   275 } {1 {unknown or unsupported join type: LEFT BOGUS}}

   276 do_test join-3.11 {

   277   catchsql {

   278     SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;

   279   }

   280 } {1 {unknown or unsupported join type: INNER BOGUS CROSS}}

   281 do_test join-3.12 {

   282   catchsql {

   283     SELECT * FROM t1 NATURAL AWK SED JOIN t2;

   284   }

   285 } {1 {unknown or unsupported join type: NATURAL AWK SED}}

   286 

   287 do_test join-4.1 {

   288   execsql {

   289     BEGIN;

   290     CREATE TABLE t5(a INTEGER PRIMARY KEY);

   291     CREATE TABLE t6(a INTEGER);

   292     INSERT INTO t6 VALUES(NULL);

   293     INSERT INTO t6 VALUES(NULL);

   294     INSERT INTO t6 SELECT * FROM t6;

   295     INSERT INTO t6 SELECT * FROM t6;

   296     INSERT INTO t6 SELECT * FROM t6;

   297     INSERT INTO t6 SELECT * FROM t6;

   298     INSERT INTO t6 SELECT * FROM t6;

   299     INSERT INTO t6 SELECT * FROM t6;

   300     COMMIT;

   301   }

   302   execsql {

   303     SELECT * FROM t6 NATURAL JOIN t5;

   304   }

   305 } {}

   306 do_test join-4.2 {

   307   execsql {

   308     SELECT * FROM t6, t5 WHERE t6.a<t5.a;

   309   }

   310 } {}

   311 do_test join-4.3 {

   312   execsql {

   313     SELECT * FROM t6, t5 WHERE t6.a>t5.a;

   314   }

   315 } {}

   316 do_test join-4.4 {

   317   execsql {

   318     UPDATE t6 SET a='xyz';

   319     SELECT * FROM t6 NATURAL JOIN t5;

   320   }

   321 } {}

   322 do_test join-4.6 {

   323   execsql {

   324     SELECT * FROM t6, t5 WHERE t6.a<t5.a;

   325   }

   326 } {}

   327 do_test join-4.7 {

   328   execsql {

   329     SELECT * FROM t6, t5 WHERE t6.a>t5.a;

   330   }

   331 } {}

   332 do_test join-4.8 {

   333   execsql {

   334     UPDATE t6 SET a=1;

   335     SELECT * FROM t6 NATURAL JOIN t5;

   336   }

   337 } {}

   338 do_test join-4.9 {

   339   execsql {

   340     SELECT * FROM t6, t5 WHERE t6.a<t5.a;

   341   }

   342 } {}

   343 do_test join-4.10 {

   344   execsql {

   345     SELECT * FROM t6, t5 WHERE t6.a>t5.a;

   346   }

   347 } {}

   348 

   349 do_test join-5.1 {

   350   execsql {

   351     BEGIN;

   352     create table centros (id integer primary key, centro);

   353     INSERT INTO centros VALUES(1,'xxx');

   354     create table usuarios (id integer primary key, nombre, apellidos,

   355     idcentro integer);

   356     INSERT INTO usuarios VALUES(1,'a','aa',1);

   357     INSERT INTO usuarios VALUES(2,'b','bb',1);

   358     INSERT INTO usuarios VALUES(3,'c','cc',NULL);

   359     create index idcentro on usuarios (idcentro);

   360     END;

   361     select usuarios.id, usuarios.nombre, centros.centro from

   362     usuarios left outer join centros on usuarios.idcentro = centros.id;

   363   }

   364 } {1 a xxx 2 b xxx 3 c {}}

   365 

   366 # A test for ticket #247.

   367 #

   368 do_test join-7.1 {

   369   execsql {

   370     CREATE TABLE t7 (x, y);

   371     INSERT INTO t7 VALUES ("pa1", 1);

   372     INSERT INTO t7 VALUES ("pa2", NULL);

   373     INSERT INTO t7 VALUES ("pa3", NULL);

   374     INSERT INTO t7 VALUES ("pa4", 2);

   375     INSERT INTO t7 VALUES ("pa30", 131);

   376     INSERT INTO t7 VALUES ("pa31", 130);

   377     INSERT INTO t7 VALUES ("pa28", NULL);

   378 

   379     CREATE TABLE t8 (a integer primary key, b);

   380     INSERT INTO t8 VALUES (1, "pa1");

   381     INSERT INTO t8 VALUES (2, "pa4");

   382     INSERT INTO t8 VALUES (3, NULL);

   383     INSERT INTO t8 VALUES (4, NULL);

   384     INSERT INTO t8 VALUES (130, "pa31");

   385     INSERT INTO t8 VALUES (131, "pa30");

   386 

   387     SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;

   388   }

   389 } {1 999 999 2 131 130 999}

   390 

   391 # Make sure a left join where the right table is really a view that

   392 # is itself a join works right.  Ticket #306.

   393 #

   394 ifcapable view {

   395 do_test join-8.1 {

   396   execsql {

   397     BEGIN;

   398     CREATE TABLE t9(a INTEGER PRIMARY KEY, b);

   399     INSERT INTO t9 VALUES(1,11);

   400     INSERT INTO t9 VALUES(2,22);

   401     CREATE TABLE t10(x INTEGER PRIMARY KEY, y);

   402     INSERT INTO t10 VALUES(1,2);

   403     INSERT INTO t10 VALUES(3,3);    

   404     CREATE TABLE t11(p INTEGER PRIMARY KEY, q);

   405     INSERT INTO t11 VALUES(2,111);

   406     INSERT INTO t11 VALUES(3,333);    

   407     CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;

   408     COMMIT;

   409     SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );

   410   }

   411 } {1 11 1 111 2 22 {} {}}

   412 ifcapable subquery {

   413   do_test join-8.2 {

   414     execsql {

   415       SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)

   416            ON( a=x);

   417     }

   418   } {1 11 1 111 2 22 {} {}}

   419 }

   420 do_test join-8.3 {

   421   execsql {

   422     SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );

   423   }

   424 } {1 111 1 11 3 333 {} {}}

   425 ifcapable subquery {

   426   # Constant expressions in a subquery that is the right element of a

   427   # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not

   428   # match.  Ticket #3300

   429   do_test join-8.4 {

   430     execsql {

   431       SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a

   432     }

   433   } {1 11 {} {} {} 2 22 44 2 111}

   434 }

   435 } ;# ifcapable view

   436 

   437 # Ticket #350 describes a scenario where LEFT OUTER JOIN does not

   438 # function correctly if the right table in the join is really

   439 # subquery.

   440 #

   441 # To test the problem, we generate the same LEFT OUTER JOIN in two

   442 # separate selects but with on using a subquery and the other calling

   443 # the table directly.  Then connect the two SELECTs using an EXCEPT.

   444 # Both queries should generate the same results so the answer should

   445 # be an empty set.

   446 #

   447 ifcapable compound {

   448 do_test join-9.1 {

   449   execsql {

   450     BEGIN;

   451     CREATE TABLE t12(a,b);

   452     INSERT INTO t12 VALUES(1,11);

   453     INSERT INTO t12 VALUES(2,22);

   454     CREATE TABLE t13(b,c);

   455     INSERT INTO t13 VALUES(22,222);

   456     COMMIT;

   457   }

   458 } {}

   459 

   460 ifcapable subquery {

   461   do_test join-9.1.1 {

   462     execsql {

   463       SELECT * FROM t12 NATURAL LEFT JOIN t13

   464       EXCEPT

   465       SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);

   466     }

   467   } {}

   468 }

   469 ifcapable view {

   470   do_test join-9.2 {

   471     execsql {

   472       CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;

   473       SELECT * FROM t12 NATURAL LEFT JOIN t13

   474         EXCEPT

   475         SELECT * FROM t12 NATURAL LEFT JOIN v13;

   476     }

   477   } {}

   478 } ;# ifcapable view

   479 } ;# ifcapable compound

   480 

   481 ifcapable subquery {

   482   # Ticket #1697:  Left Join WHERE clause terms that contain an

   483   # aggregate subquery.

   484   #

   485   do_test join-10.1 {

   486     execsql {

   487       CREATE TABLE t21(a,b,c);

   488       CREATE TABLE t22(p,q);

   489       CREATE INDEX i22 ON t22(q);

   490       SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=

   491          (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);

   492     }  

   493   } {}

   494 

   495   # Test a LEFT JOIN when the right-hand side of hte join is an empty

   496   # sub-query. Seems fine.

   497   #

   498   do_test join-10.2 {

   499     execsql {

   500       CREATE TABLE t23(a, b, c);

   501       CREATE TABLE t24(a, b, c);

   502       INSERT INTO t23 VALUES(1, 2, 3);

   503     }

   504     execsql {

   505       SELECT * FROM t23 LEFT JOIN t24;

   506     }

   507   } {1 2 3 {} {} {}}

   508   do_test join-10.3 {

   509     execsql {

   510       SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24);

   511     }

   512   } {1 2 3 {} {} {}}

   513 

   514 } ;# ifcapable subquery

   515 

   516 finish_test