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 involving

    14 # virtual tables. The test cases in this file are copied from the file

    15 # join.test, and some of the comments still reflect that.

    16 #

    17 # $Id: vtab6.test,v 1.4 2008/07/12 14:52:21 drh Exp $

    18 

    19 set testdir [file dirname $argv0]

    20 source $testdir/tester.tcl

    21 

    22 ifcapable !vtab {

    23   finish_test

    24   return

    25 }

    26 

    27 register_echo_module [sqlite3_connection_pointer db]

    28 

    29 execsql {

    30   CREATE TABLE real_t1(a,b,c);

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

    32   CREATE TABLE real_t3(c,d,e);

    33   CREATE TABLE real_t4(d,e,f);

    34   CREATE TABLE real_t5(a INTEGER PRIMARY KEY);

    35   CREATE TABLE real_t6(a INTEGER);

    36   CREATE TABLE real_t7 (x, y);

    37   CREATE TABLE real_t8 (a integer primary key, b);

    38   CREATE TABLE real_t9(a INTEGER PRIMARY KEY, b);

    39   CREATE TABLE real_t10(x INTEGER PRIMARY KEY, y);

    40   CREATE TABLE real_t11(p INTEGER PRIMARY KEY, q);

    41   CREATE TABLE real_t12(a,b);

    42   CREATE TABLE real_t13(b,c);

    43   CREATE TABLE real_t21(a,b,c);

    44   CREATE TABLE real_t22(p,q);

    45 }

    46 foreach t [list t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13 t21 t22] {

    47   execsql "CREATE VIRTUAL TABLE $t USING echo(real_$t)"

    48 }

    49 

    50 do_test vtab6-1.1 {

    51   execsql {

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

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

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

    55     SELECT * FROM t1;

    56   }  

    57 } {1 2 3 2 3 4 3 4 5}

    58 do_test vtab6-1.2 {

    59   execsql {

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

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

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

    63     SELECT * FROM t2;

    64   }  

    65 } {1 2 3 2 3 4 3 4 5}

    66 

    67 do_test vtab6-1.3 {

    68   execsql2 {

    69     SELECT * FROM t1 NATURAL JOIN t2;

    70   }

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

    72 do_test vtab6-1.3.1 {

    73   execsql2 {

    74     SELECT * FROM t2 NATURAL JOIN t1;

    75   }

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

    77 do_test vtab6-1.3.2 {

    78   execsql2 {

    79     SELECT * FROM t2 AS x NATURAL JOIN t1;

    80   }

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

    82 do_test vtab6-1.3.3 {

    83   execsql2 {

    84     SELECT * FROM t2 NATURAL JOIN t1 AS y;

    85   }

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

    87 do_test vtab6-1.3.4 {

    88   execsql {

    89     SELECT b FROM t1 NATURAL JOIN t2;

    90   }

    91 } {2 3}

    92 do_test vtab6-1.4.1 {

    93   execsql2 {

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

    95   }

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

    97 do_test vtab6-1.4.2 {

    98   execsql2 {

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

   100   }

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

   102 do_test vtab6-1.4.3 {

   103   execsql2 {

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

   105   }

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

   107 do_test vtab6-1.4.4 {

   108   execsql2 {

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

   110   }

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

   112 do_test vtab6-1.4.5 {

   113   execsql {

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

   115   }

   116 } {2 3}

   117 do_test vtab6-1.5 {

   118   execsql2 {

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

   120   }

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

   122 do_test vtab6-1.6 {

   123   execsql2 {

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

   125   }

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

   127 do_test vtab6-1.7 {

   128   execsql2 {

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

   130   }

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

   132 

   133 do_test vtab6-1.8 {

   134   execsql {

   135     SELECT * FROM t1 NATURAL CROSS JOIN t2;

   136   }

   137 } {1 2 3 4 2 3 4 5}

   138 do_test vtab6-1.9 {

   139   execsql {

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

   141   }

   142 } {1 2 3 4 2 3 4 5}

   143 do_test vtab6-1.10 {

   144   execsql {

   145     SELECT * FROM t1 NATURAL INNER JOIN t2;

   146   }

   147 } {1 2 3 4 2 3 4 5}

   148 do_test vtab6-1.11 {

   149   execsql {

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

   151   }

   152 } {1 2 3 4 2 3 4 5}

   153 do_test vtab6-1.12 {

   154   execsql {

   155     SELECT * FROM t1 natural inner join t2;

   156   }

   157 } {1 2 3 4 2 3 4 5}

   158 

   159 ifcapable subquery {

   160   do_test vtab6-1.13 {

   161     execsql2 {

   162       SELECT * FROM t1 NATURAL JOIN 

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

   164     }

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

   166   do_test vtab6-1.14 {

   167     execsql2 {

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

   169           NATURAL JOIN t1

   170     }

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

   172 }

   173 

   174 do_test vtab6-1.15 {

   175   execsql {

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

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

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

   179     SELECT * FROM t3;

   180   }  

   181 } {2 3 4 3 4 5 4 5 6}

   182 do_test vtab6-1.16 {

   183   execsql {

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

   185   }

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

   187 do_test vtab6-1.17 {

   188   execsql2 {

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

   190   }

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

   192 do_test vtab6-1.18 {

   193   execsql {

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

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

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

   197     SELECT * FROM t4;

   198   }  

   199 } {2 3 4 3 4 5 4 5 6}

   200 do_test vtab6-1.19.1 {

   201   execsql {

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

   203   }

   204 } {1 2 3 4 5 6}

   205 do_test vtab6-1.19.2 {

   206   execsql2 {

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

   208   }

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

   210 do_test vtab6-1.20 {

   211   execsql {

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

   213   }

   214 } {1 2 3 4 5}

   215 

   216 do_test vtab6-2.1 {

   217   execsql {

   218     SELECT * FROM t1 NATURAL LEFT JOIN t2;

   219   }

   220 } {1 2 3 4 2 3 4 5 3 4 5 {}}

   221 do_test vtab6-2.2 {

   222   execsql {

   223     SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;

   224   }

   225 } {1 2 3 {} 2 3 4 1 3 4 5 2}

   226 do_test vtab6-2.3 {

   227   catchsql {

   228     SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;

   229   }

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

   231 do_test vtab6-2.4 {

   232   execsql {

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

   234   }

   235 } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}

   236 do_test vtab6-2.5 {

   237   execsql {

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

   239   }

   240 } {2 3 4 {} {} {} 3 4 5 1 2 3}

   241 do_test vtab6-2.6 {

   242   execsql {

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

   244   }

   245 } {1 2 3 {} {} {} 2 3 4 {} {} {}}

   246 

   247 do_test vtab6-3.1 {

   248   catchsql {

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

   250   }

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

   252 do_test vtab6-3.2 {

   253   catchsql {

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

   255   }

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

   257 do_test vtab6-3.3 {

   258   catchsql {

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

   260   }

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

   262 do_test vtab6-3.4 {

   263   catchsql {

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

   265   }

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

   267 do_test vtab6-3.5 {

   268   catchsql {

   269     SELECT * FROM t1 USING(a);

   270   }

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

   272 do_test vtab6-3.6 {

   273   catchsql {

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

   275   }

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

   277 do_test vtab6-3.7 {

   278   catchsql {

   279     SELECT * FROM t1 INNER OUTER JOIN t2;

   280   }

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

   282 do_test vtab6-3.7 {

   283   catchsql {

   284     SELECT * FROM t1 LEFT BOGUS JOIN t2;

   285   }

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

   287 

   288 do_test vtab6-4.1 {

   289   execsql {

   290     BEGIN;

   291     INSERT INTO t6 VALUES(NULL);

   292     INSERT INTO t6 VALUES(NULL);

   293     INSERT INTO t6 SELECT * FROM t6;

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

   300   }

   301   execsql {

   302     SELECT * FROM t6 NATURAL JOIN t5;

   303   }

   304 } {}

   305 do_test vtab6-4.2 {

   306   execsql {

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

   308   }

   309 } {}

   310 do_test vtab6-4.3 {

   311   execsql {

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

   313   }

   314 } {}

   315 do_test vtab6-4.4 {

   316   execsql {

   317     UPDATE t6 SET a='xyz';

   318     SELECT * FROM t6 NATURAL JOIN t5;

   319   }

   320 } {}

   321 do_test vtab6-4.6 {

   322   execsql {

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

   324   }

   325 } {}

   326 do_test vtab6-4.7 {

   327   execsql {

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

   329   }

   330 } {}

   331 do_test vtab6-4.8 {

   332   execsql {

   333     UPDATE t6 SET a=1;

   334     SELECT * FROM t6 NATURAL JOIN t5;

   335   }

   336 } {}

   337 do_test vtab6-4.9 {

   338   execsql {

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

   340   }

   341 } {}

   342 do_test vtab6-4.10 {

   343   execsql {

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

   345   }

   346 } {}

   347 

   348 # A test for ticket #247.

   349 #

   350 do_test vtab6-7.1 {

   351   execsql {

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

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

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

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

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

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

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

   359 

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

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

   362     INSERT INTO t8 VALUES (3, NULL);

   363     INSERT INTO t8 VALUES (4, NULL);

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

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

   366 

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

   368   }

   369 } {1 999 999 2 131 130 999}

   370 

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

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

   373 #

   374 ifcapable view {

   375 do_test vtab6-8.1 {

   376   execsql {

   377     BEGIN;

   378     INSERT INTO t9 VALUES(1,11);

   379     INSERT INTO t9 VALUES(2,22);

   380     INSERT INTO t10 VALUES(1,2);

   381     INSERT INTO t10 VALUES(3,3);    

   382     INSERT INTO t11 VALUES(2,111);

   383     INSERT INTO t11 VALUES(3,333);    

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

   385     COMMIT;

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

   387   }

   388 } {1 11 1 111 2 22 {} {}}

   389 ifcapable subquery {

   390   do_test vtab6-8.2 {

   391     execsql {

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

   393            ON( a=x);

   394     }

   395   } {1 11 1 111 2 22 {} {}}

   396 }

   397 do_test vtab6-8.3 {

   398   execsql {

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

   400   }

   401 } {1 111 1 11 3 333 {} {}}

   402 } ;# ifcapable view

   403 

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

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

   406 # subquery.

   407 #

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

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

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

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

   412 # be an empty set.

   413 #

   414 ifcapable compound {

   415 do_test vtab6-9.1 {

   416   execsql {

   417     BEGIN;

   418     INSERT INTO t12 VALUES(1,11);

   419     INSERT INTO t12 VALUES(2,22);

   420     INSERT INTO t13 VALUES(22,222);

   421     COMMIT;

   422   }

   423 } {}

   424 

   425 ifcapable subquery {

   426   do_test vtab6-9.1.1 {

   427     execsql {

   428       SELECT * FROM t12 NATURAL LEFT JOIN t13

   429       EXCEPT

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

   431     }

   432   } {}

   433 }

   434 ifcapable view {

   435   do_test vtab6-9.2 {

   436     execsql {

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

   438       SELECT * FROM t12 NATURAL LEFT JOIN t13

   439         EXCEPT

   440         SELECT * FROM t12 NATURAL LEFT JOIN v13;

   441     }

   442   } {}

   443 } ;# ifcapable view

   444 } ;# ifcapable compound

   445 

   446 ifcapable subquery {

   447 do_test vtab6-10.1 {

   448   execsql {

   449     CREATE INDEX i22 ON real_t22(q);

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

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

   452   }  

   453 } {}

   454 } ;# ifcapable subquery

   455 

   456 do_test vtab6-11.1.0 {

   457   execsql {

   458     CREATE TABLE ab_r(a, b);

   459     CREATE TABLE bc_r(b, c);

   460 

   461     CREATE VIRTUAL TABLE ab USING echo(ab_r); 

   462     CREATE VIRTUAL TABLE bc USING echo(bc_r); 

   463 

   464     INSERT INTO ab VALUES(1, 2);

   465     INSERT INTO bc VALUES(2, 3);

   466   }

   467 } {}

   468 

   469 do_test vtab6-11.1.1 {

   470   execsql {

   471     SELECT a, b, c FROM ab NATURAL JOIN bc;

   472   }

   473 } {1 2 3}

   474 do_test vtab6-11.1.2 {

   475   execsql {

   476     SELECT a, b, c FROM bc NATURAL JOIN ab;

   477   }

   478 } {1 2 3}

   479 

   480 set ::echo_module_cost 1.0

   481 

   482 do_test vtab6-11.1.3 {

   483   execsql {

   484     SELECT a, b, c FROM ab NATURAL JOIN bc;

   485   }

   486 } {1 2 3}

   487 do_test vtab6-11.1.4 {

   488   execsql {

   489     SELECT a, b, c FROM bc NATURAL JOIN ab;

   490   }

   491 } {1 2 3}

   492 

   493 

   494 do_test vtab6-11.2.0 {

   495   execsql {

   496     CREATE INDEX ab_i ON ab_r(b);

   497   }

   498 } {}

   499 

   500 unset ::echo_module_cost

   501 

   502 do_test vtab6-11.2.1 {

   503   execsql {

   504     SELECT a, b, c FROM ab NATURAL JOIN bc;

   505   }

   506 } {1 2 3}

   507 do_test vtab6-11.2.2 {

   508   execsql {

   509     SELECT a, b, c FROM bc NATURAL JOIN ab;

   510   }

   511 } {1 2 3}

   512 

   513 set ::echo_module_cost 1.0

   514 

   515 do_test vtab6-11.2.3 {

   516   execsql {

   517     SELECT a, b, c FROM ab NATURAL JOIN bc;

   518   }

   519 } {1 2 3}

   520 do_test vtab6-11.2.4 {

   521   execsql {

   522     SELECT a, b, c FROM bc NATURAL JOIN ab;

   523   }

   524 } {1 2 3}

   525 

   526 unset ::echo_module_cost

   527 db close

   528 sqlite3 db test.db

   529 register_echo_module [sqlite3_connection_pointer db]

   530 

   531 do_test vtab6-11.3.1 {

   532   execsql {

   533     SELECT a, b, c FROM ab NATURAL JOIN bc;

   534   }

   535 } {1 2 3}

   536 

   537 do_test vtab6-11.3.2 {

   538   execsql {

   539     SELECT a, b, c FROM bc NATURAL JOIN ab;

   540   }

   541 } {1 2 3}

   542 

   543 set ::echo_module_cost 1.0

   544 

   545 do_test vtab6-11.3.3 {

   546   execsql {

   547     SELECT a, b, c FROM ab NATURAL JOIN bc;

   548   }

   549 } {1 2 3}

   550 do_test vtab6-11.3.4 {

   551   execsql {

   552     SELECT a, b, c FROM bc NATURAL JOIN ab;

   553   }

   554 } {1 2 3}

   555 

   556 unset ::echo_module_cost

   557 

   558 set ::echo_module_ignore_usable 1

   559 db cache flush

   560 

   561 do_test vtab6-11.4.1 {

   562   catchsql {

   563     SELECT a, b, c FROM ab NATURAL JOIN bc;

   564   }

   565 } {1 {table ab: xBestIndex returned an invalid plan}}

   566 do_test vtab6-11.4.2 {

   567   catchsql {

   568     SELECT a, b, c FROM bc NATURAL JOIN ab;

   569   }

   570 } {1 {table ab: xBestIndex returned an invalid plan}}

   571 

   572 unset ::echo_module_ignore_usable

   573 

   574 finish_test