AryaWu/sqlite

1# 2001 September 152#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.  The12# focus of this file is testing SELECT statements that are part of13# expressions.14#15# $Id: subselect.test,v 1.16 2008/08/04 03:51:24 danielk1977 Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# Omit this whole file if the library is build without subquery support.21ifcapable !subquery {22  finish_test23  return24}25 26# Basic sanity checking.  Try a simple subselect.27#28do_test subselect-1.1 {29  execsql {30    CREATE TABLE t1(a int, b int);31    INSERT INTO t1 VALUES(1,2);32    INSERT INTO t1 VALUES(3,4);33    INSERT INTO t1 VALUES(5,6);34  }35  execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)}36} {3 4}37 38# Try a select with more than one result column.39#40do_test subselect-1.2 {41  set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg]42  lappend v $msg43} {1 {row value misused}}44 45# A subselect without an aggregate.46#47do_test subselect-1.3a {48  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)}49} {2}50do_test subselect-1.3b {51  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)}52} {4}53do_test subselect-1.3c {54  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}55} {6}56do_test subselect-1.3d {57  execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}58} {}59ifcapable compound {60  do_test subselect-1.3e {61    execsql {62      SELECT b FROM t163       WHERE a = (SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY 1);64    }65  } {2}66}67 68# What if the subselect doesn't return any value.  We should get69# NULL as the result.  Check it out.70#71do_test subselect-1.4 {72  execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}73} {2}74 75# Try multiple subselects within a single expression.76#77do_test subselect-1.5 {78  execsql {79    CREATE TABLE t2(x int, y int);80    INSERT INTO t2 VALUES(1,2);81    INSERT INTO t2 VALUES(2,4);82    INSERT INTO t2 VALUES(3,8);83    INSERT INTO t2 VALUES(4,16);84  }85  execsql {86    SELECT y from t2 87    WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1)88  }89} {8}90 91# Try something useful.  Delete every entry from t2 where the92# x value is less than half of the maximum.93#94do_test subselect-1.6 {95  execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)}96  execsql {SELECT x FROM t2 ORDER BY x}97} {2 3 4}98 99# Make sure sorting works for SELECTs there used as a scalar expression.100#101do_test subselect-2.1 {102  execsql {103    SELECT (SELECT a FROM t1 ORDER BY a), (SELECT a FROM t1 ORDER BY a DESC)104  }105} {1 5}106do_test subselect-2.2 {107  execsql {108    SELECT 1 IN (SELECT a FROM t1 ORDER BY a);109  }110} {1}111do_test subselect-2.3 {112  execsql {113    SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC);114  }115} {0}116 117# Verify that the ORDER BY clause is honored in a subquery.118#119ifcapable compound {120do_test subselect-3.1 {121  execsql {122    CREATE TABLE t3(x int);123    INSERT INTO t3 SELECT a FROM t1 UNION ALL SELECT b FROM t1;124    SELECT * FROM t3 ORDER BY x;125  }126} {1 2 3 4 5 6}127} ;# ifcapable compound128ifcapable !compound {129do_test subselect-3.1 {130  execsql {131    CREATE TABLE t3(x int);132    INSERT INTO t3 SELECT a FROM t1; 133    INSERT INTO t3 SELECT b FROM t1;134    SELECT * FROM t3 ORDER BY x;135  }136} {1 2 3 4 5 6}137} ;# ifcapable !compound138 139do_test subselect-3.2 {140  execsql {141    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2);142  }143} {3}144do_test subselect-3.3 {145  execsql {146    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2);147  }148} {11}149do_test subselect-3.4 {150  execsql {151    SELECT (SELECT x FROM t3 ORDER BY x);152  }153} {1}154do_test subselect-3.5 {155  execsql {156    SELECT (SELECT x FROM t3 ORDER BY x DESC);157  }158} {6}159do_test subselect-3.6 {160  execsql {161    SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1);162  }163} {1}164do_test subselect-3.7 {165  execsql {166    SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1);167  }168} {6}169do_test subselect-3.8 {170  execsql {171    SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1 OFFSET 2);172  }173} {3}174do_test subselect-3.9 {175  execsql {176    SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);177  }178} {4}179do_test subselect-3.10 {180  execsql {181    SELECT x FROM t3 WHERE x IN182       (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);183  }184} {4}185 186# Ticket #2295.187# Make sure type affinities work correctly on subqueries with188# an ORDER BY clause.189#190do_test subselect-4.1 {191  execsql {192    CREATE TABLE t4(a TEXT, b TEXT);193    INSERT INTO t4 VALUES('a','1');194    INSERT INTO t4 VALUES('b','2');195    INSERT INTO t4 VALUES('c','3');196    SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b);197  }198} {a b c}199do_test subselect-4.2 {200  execsql {201    SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b LIMIT 1);202  }203} {a}204do_test subselect-4.3 {205  execsql {206    SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b DESC LIMIT 1);207  }208} {c}209 210finish_test211