Return to tkt1537.test CVS log

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

sqlite3

    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