1 # 2005 November 26

     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 #1537 is

    14 # fixed.  

    15 #

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 do_test tkt1537-1.1 {

    21   execsql {

    22     CREATE TABLE t1(id, a1, a2);

    23     INSERT INTO t1 VALUES(1, NULL, NULL);

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

    25     CREATE TABLE t2(id, b);

    26     INSERT INTO t2 VALUES(3, 1);

    27     INSERT INTO t2 VALUES(4, NULL);

    28     SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=+b;

    29   }

    30 } {1 {} {} {} {} 2 1 3 3 1}

    31 do_test tkt1537-1.2 {

    32   execsql {

    33     SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;

    34   }

    35 } {1 {} {} {} {} 2 1 3 3 1}

    36 do_test tkt1537-1.3 {

    37   execsql {

    38     SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;

    39   }

    40 } {3 1 2 1 3 4 {} {} {} {}}

    41 ifcapable subquery {

    42   do_test tkt1537-1.4 {

    43     execsql {

    44       SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);

    45     }

    46   } {1 {} {} {} {} 2 1 3 3 1}

    47   do_test tkt1537-1.5 {

    48     execsql {

    49       SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);

    50     }

    51   } {3 1 2 1 3 4 {} {} {} {}}

    52 }

    53 do_test tkt1537-1.6 {

    54   execsql {

    55     CREATE INDEX t1a1 ON t1(a1);

    56     CREATE INDEX t1a2 ON t1(a2);

    57     CREATE INDEX t2b ON t2(b);

    58     SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;

    59   }

    60 } {1 {} {} {} {} 2 1 3 3 1}

    61 do_test tkt1537-1.7 {

    62   execsql {

    63     SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;

    64   }

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

    66 

    67 ifcapable subquery {

    68   do_test tkt1537-1.8 {

    69     execsql {

    70       SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);

    71     }

    72   } {1 {} {} {} {} 2 1 3 3 1}

    73   do_test tkt1537-1.9 {

    74     execsql {

    75       SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);

    76     }

    77   } {3 1 2 1 3 4 {} {} {} {}}

    78 }

    79 

    80 execsql {

    81   DROP INDEX t1a1;

    82   DROP INDEX t1a2;

    83   DROP INDEX t2b;

    84 }

    85 

    86 do_test tkt1537-2.1 {

    87   execsql {

    88     SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;

    89   }

    90 } {1 {} {} {} {} 2 1 3 3 1}

    91 do_test tkt1537-2.2 {

    92   execsql {

    93     CREATE INDEX t2b ON t2(b);

    94     SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;

    95   }

    96 } {1 {} {} {} {} 2 1 3 3 1}

    97 do_test tkt1537-2.3 {

    98   execsql {

    99     SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;

   100   }

   101 } {3 1 2 1 3 4 {} {} {} {}}

   102 do_test tkt1537-2.4 {

   103   execsql {

   104     CREATE INDEX t1a1 ON t1(a1);

   105     CREATE INDEX t1a2 ON t1(a2);

   106     SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;

   107   }

   108 } {3 1 2 1 3 4 {} {} {} {}}

   109 

   110 do_test tkt1537-3.1 {

   111   execsql {

   112     SELECT * FROM t1 LEFT JOIN t2 ON b GLOB 'abc*' WHERE t1.id=1;

   113   }

   114 } {1 {} {} {} {}}

   115 do_test tkt1537-3.2 {

   116   execsql { 

   117     SELECT * FROM t2 LEFT JOIN t1 ON a1 GLOB 'abc*' WHERE t2.id=3;

   118   }

   119 } {3 1 {} {} {}}

   120 

   121 

   122 finish_test