Return to tkt1537.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/tkt1537.test, revision 1.1.1.1

1.1       misho       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