1 # 2007 January 03

     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 to verify that ticket #2141 has been

    14 # fixed.  

    15 #

    16 #

    17 # $Id: tkt2141.test,v 1.2 2007/09/12 17:01:45 danielk1977 Exp $

    18 

    19 set testdir [file dirname $argv0]

    20 source $testdir/tester.tcl

    21 

    22 ifcapable !subquery {

    23   finish_test

    24   return

    25 }

    26 

    27 do_test tkt2141-1.1 {

    28   execsql {

    29       CREATE TABLE tab1 (t1_id integer PRIMARY KEY, t1_desc);

    30       INSERT INTO tab1 VALUES(1,'rec 1 tab 1');

    31       CREATE TABLE tab2 (t2_id integer PRIMARY KEY, t2_id_t1, t2_desc);

    32       INSERT INTO tab2 VALUES(1,1,'rec 1 tab 2');

    33       CREATE TABLE tab3 (t3_id integer PRIMARY KEY, t3_id_t2, t3_desc);

    34       INSERT INTO tab3 VALUES(1,1,'aa');

    35       SELECT *

    36       FROM tab1 t1 LEFT JOIN tab2 t2 ON t1.t1_id = t2.t2_id_t1

    37       WHERE t2.t2_id IN

    38            (SELECT t2_id FROM tab2, tab3 ON t2_id = t3_id_t2

    39              WHERE t3_id IN (1,2) GROUP BY t2_id);

    40   }

    41 } {1 {rec 1 tab 1} 1 1 {rec 1 tab 2}}

    42 do_test tkt2141-1.2 {

    43   execsql {

    44       SELECT *

    45       FROM tab1 t1 LEFT JOIN tab2 t2 ON t1.t1_id = t2.t2_id_t1

    46       WHERE t2.t2_id IN

    47            (SELECT t2_id FROM tab2, tab3 ON t2_id = t3_id_t2

    48              WHERE t3_id IN (1,2));

    49   }

    50 } {1 {rec 1 tab 1} 1 1 {rec 1 tab 2}}

    51 do_test tkt2141-1.3 {

    52   execsql {

    53       SELECT *

    54       FROM tab1 t1 LEFT JOIN tab2 t2

    55       WHERE t2.t2_id IN

    56            (SELECT t2_id FROM tab2, tab3 ON t2_id = t3_id_t2

    57              WHERE t3_id IN (1,2));

    58   }

    59 } {1 {rec 1 tab 1} 1 1 {rec 1 tab 2}}

    60 

    61 finish_test