Return to subquery2.test CVS log

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

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

1.1       misho       1: # 2011 September 16
                      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.  The
                     12: # focus of this script is testing correlated subqueries
                     13: #
                     14: #
                     15: 
                     16: set testdir [file dirname $argv0]
                     17: source $testdir/tester.tcl
                     18: 
                     19: ifcapable !subquery {
                     20:   finish_test
                     21:   return
                     22: }
                     23: 
                     24: do_test subquery2-1.1 {
                     25:   execsql {
                     26:     BEGIN;
                     27:     CREATE TABLE t1(a,b);
                     28:     INSERT INTO t1 VALUES(1,2);
                     29:     INSERT INTO t1 VALUES(3,4);
                     30:     INSERT INTO t1 VALUES(5,6);
                     31:     INSERT INTO t1 VALUES(7,8);
                     32:     CREATE TABLE t2(c,d);
                     33:     INSERT INTO t2 VALUES(1,1);
                     34:     INSERT INTO t2 VALUES(3,9);
                     35:     INSERT INTO t2 VALUES(5,25);
                     36:     INSERT INTO t2 VALUES(7,49);
                     37:     CREATE TABLE t3(e,f);
                     38:     INSERT INTO t3 VALUES(1,1);
                     39:     INSERT INTO t3 VALUES(3,27);
                     40:     INSERT INTO t3 VALUES(5,125);
                     41:     INSERT INTO t3 VALUES(7,343);
                     42:     COMMIT;
                     43:   }
                     44:   execsql {
                     45:     SELECT a FROM t1
                     46:      WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
                     47:   }
                     48: } {1 3 5 7}
                     49: do_test subquery2-1.2 {
                     50:   execsql {
                     51:     CREATE INDEX t1b ON t1(b);
                     52:     SELECT a FROM t1
                     53:      WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
                     54:   }
                     55: } {1 3 5 7}
                     56: 
                     57: do_test subquery2-1.11 {
                     58:   execsql {
                     59:     SELECT a FROM t1
                     60:      WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
                     61:   }
                     62: } {1}
                     63: do_test subquery2-1.12 {
                     64:   execsql {
                     65:     SELECT a FROM t1
                     66:      WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
                     67:   }
                     68: } {1}
                     69: 
                     70: do_test subquery2-1.21 {
                     71:   execsql {
                     72:     SELECT a FROM t1
                     73:      WHERE +b=(SELECT x+1 FROM 
                     74:                  (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
                     75:   }
                     76: } {1 3 5 7}
                     77: do_test subquery2-1.22 {
                     78:   execsql {
                     79:     SELECT a FROM t1
                     80:      WHERE b=(SELECT x+1 FROM 
                     81:                  (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
                     82:   }
                     83: } {1 3 5 7}
                     84: 
                     85: 
                     86: finish_test