AryaWu/sqlite

1# 2008 September 162#3# The author disclaims copyright to this source code.  In place of4# 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# $Id: selectC.test,v 1.5 2009/05/17 15:26:21 drh Exp $14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix selectC18 19# Ticket #20do_test selectC-1.1 {21  execsql {22    CREATE TABLE t1(a, b, c);23    INSERT INTO t1 VALUES(1,'aaa','bbb');24    INSERT INTO t1 SELECT * FROM t1;25    INSERT INTO t1 VALUES(2,'ccc','ddd');26 27    SELECT DISTINCT a AS x, b||c AS y28      FROM t129     WHERE y IN ('aaabbb','xxx');30  }31} {1 aaabbb}32do_test selectC-1.2 {33  execsql {34    SELECT DISTINCT a AS x, b||c AS y35      FROM t136     WHERE b||c IN ('aaabbb','xxx');37  }38} {1 aaabbb}39do_test selectC-1.3 {40  execsql {41    SELECT DISTINCT a AS x, b||c AS y42      FROM t143     WHERE y='aaabbb'44  }45} {1 aaabbb}46do_test selectC-1.4 {47  execsql {48    SELECT DISTINCT a AS x, b||c AS y49      FROM t150     WHERE b||c='aaabbb'51  }52} {1 aaabbb}53do_test selectC-1.5 {54  execsql {55    SELECT DISTINCT a AS x, b||c AS y56      FROM t157     WHERE x=258  }59} {2 cccddd}60do_test selectC-1.6 {61  execsql {62    SELECT DISTINCT a AS x, b||c AS y63      FROM t164     WHERE a=265  }66} {2 cccddd}67do_test selectC-1.7 {68  execsql {69    SELECT DISTINCT a AS x, b||c AS y70      FROM t171     WHERE +y='aaabbb'72  }73} {1 aaabbb}74do_test selectC-1.8 {75  execsql {76    SELECT a AS x, b||c AS y77      FROM t178     GROUP BY x, y79    HAVING y='aaabbb'80  }81} {1 aaabbb}82do_test selectC-1.9 {83  execsql {84    SELECT a AS x, b||c AS y85      FROM t186     GROUP BY x, y87    HAVING b||c='aaabbb'88  }89} {1 aaabbb}90do_test selectC-1.10 {91  execsql {92    SELECT a AS x, b||c AS y93      FROM t194     WHERE y='aaabbb'95     GROUP BY x, y96  }97} {1 aaabbb}98do_test selectC-1.11 {99  execsql {100    SELECT a AS x, b||c AS y101      FROM t1102     WHERE b||c='aaabbb'103     GROUP BY x, y104  }105} {1 aaabbb}106proc longname_toupper x {return [string toupper $x]}107db function uppercaseconversionfunctionwithaverylongname longname_toupper108do_test selectC-1.12.1 {109  execsql {110    SELECT DISTINCT upper(b) AS x111      FROM t1112     ORDER BY x113  }114} {AAA CCC}115do_test selectC-1.12.2 {116  execsql {117    SELECT DISTINCT uppercaseconversionfunctionwithaverylongname(b) AS x118      FROM t1119     ORDER BY x120  }121} {AAA CCC}122do_test selectC-1.13.1 {123  execsql {124    SELECT upper(b) AS x125      FROM t1126     GROUP BY x127     ORDER BY x128  }129} {AAA CCC}130do_test selectC-1.13.2 {131  execsql {132    SELECT uppercaseconversionfunctionwithaverylongname(b) AS x133      FROM t1134     GROUP BY x135     ORDER BY x136  }137} {AAA CCC}138do_test selectC-1.14.1 {139  execsql {140    SELECT upper(b) AS x141      FROM t1142     ORDER BY x DESC143  }144} {CCC AAA AAA}145do_test selectC-1.14.2 {146  execsql {147    SELECT uppercaseconversionfunctionwithaverylongname(b) AS x148      FROM t1149     ORDER BY x DESC150  }151} {CCC AAA AAA}152 153# The following query used to leak memory.  Verify that has been fixed.154#155ifcapable trigger&&compound {156  do_test selectC-2.1 {157    catchsql {158      CREATE TABLE t21a(a,b);159      INSERT INTO t21a VALUES(1,2);160      CREATE TABLE t21b(n);161      CREATE TRIGGER r21 AFTER INSERT ON t21b BEGIN162        SELECT a FROM t21a WHERE a>new.x UNION ALL163        SELECT b FROM t21a WHERE b>new.x ORDER BY 1 LIMIT 2;164      END;165      INSERT INTO t21b VALUES(6);166    }167  } {1 {no such column: new.x}}168}169 170# Check that ticket [883034dcb5] is fixed.171#172do_test selectC-3.1 {173  execsql {174    CREATE TABLE person (175        org_id          TEXT NOT NULL,176        nickname        TEXT NOT NULL,177        license         TEXT,178        CONSTRAINT person_pk PRIMARY KEY (org_id, nickname),179        CONSTRAINT person_license_uk UNIQUE (license)180    );181    INSERT INTO person VALUES('meyers', 'jack', '2GAT123');182    INSERT INTO person VALUES('meyers', 'hill', 'V345FMP');183    INSERT INTO person VALUES('meyers', 'jim', '2GAT138');184    INSERT INTO person VALUES('smith', 'maggy', '');185    INSERT INTO person VALUES('smith', 'jose', 'JJZ109');186    INSERT INTO person VALUES('smith', 'jack', 'THX138');187    INSERT INTO person VALUES('lakeside', 'dave', '953OKG');188    INSERT INTO person VALUES('lakeside', 'amy', NULL);189    INSERT INTO person VALUES('lake-apts', 'tom', NULL);190    INSERT INTO person VALUES('acorn', 'hideo', 'CQB421');191    192    SELECT 193      org_id, 194      count((NOT (org_id IS NULL)) AND (NOT (nickname IS NULL)))195    FROM person 196    WHERE (CASE WHEN license != '' THEN 1 ELSE 0 END)197    GROUP BY 1;198  }199} {acorn 1 lakeside 1 meyers 3 smith 2}200do_test selectC-3.2 {201  execsql {202    CREATE TABLE t2(a PRIMARY KEY, b);203    INSERT INTO t2 VALUES('abc', 'xxx');204    INSERT INTO t2 VALUES('def', 'yyy');205    SELECT a, max(b || a) FROM t2 WHERE (b||b||b)!='value' GROUP BY a;206  }207} {abc xxxabc def yyydef}208do_test selectC-3.3 {209  execsql {210    SELECT b, max(a || b) FROM t2 WHERE (b||b||b)!='value' GROUP BY a;211  }212} {xxx abcxxx yyy defyyy}213 214 215proc udf {} { incr ::udf }216set ::udf 0217db function udf udf218 219do_execsql_test selectC-4.1 {220  create table t_distinct_bug (a, b, c);221  insert into t_distinct_bug values ('1', '1', 'a');222  insert into t_distinct_bug values ('1', '2', 'b');223  insert into t_distinct_bug values ('1', '3', 'c');224  insert into t_distinct_bug values ('1', '1', 'd');225  insert into t_distinct_bug values ('1', '2', 'e');226  insert into t_distinct_bug values ('1', '3', 'f');227} {}228 229do_execsql_test selectC-4.2 {230  select a from (select distinct a, b from t_distinct_bug)231} {1 1 1}232 233do_execsql_test selectC-4.2b {234  CREATE VIEW v42b AS SELECT DISTINCT a, b FROM t_distinct_bug;235  SELECT a FROM v42b;236} {1 1 1}237 238do_execsql_test selectC-4.3 {239  select a, udf() from (select distinct a, b from t_distinct_bug)240} {1 1 1 2 1 3}241 242#-------------------------------------------------------------------------243# Test that the problem in ticket #190c2507 has been fixed.244#245do_execsql_test 5.0 {246  CREATE TABLE x1(a);247  CREATE TABLE x2(b);248  CREATE TABLE x3(c);249  CREATE VIEW vvv AS SELECT b FROM x2 ORDER BY 1;250 251  INSERT INTO x1 VALUES('a'), ('b');252  INSERT INTO x2 VALUES(22), (23), (25), (24), (21);253  INSERT INTO x3 VALUES(302), (303), (301);254}255 256do_execsql_test 5.1 {257  CREATE TABLE x4 AS SELECT b FROM vvv UNION ALL SELECT c from x3;258  SELECT * FROM x4;259} {21 22 23 24 25 302 303 301}260 261do_execsql_test 5.2 {262  SELECT * FROM x1, x4263} {264  a 21 a 22 a 23 a 24 a 25 a 302 a 303 a 301265  b 21 b 22 b 23 b 24 b 25 b 302 b 303 b 301266}267 268do_execsql_test 5.3 {269  SELECT * FROM x1, (SELECT b FROM vvv UNION ALL SELECT c from x3) ORDER BY 1,2;270} {271  a 21 a 22 a 23 a 24 a 25 a 301 a 302 a 303272  b 21 b 22 b 23 b 24 b 25 b 301 b 302 b 303273}274 275finish_test276