xref: /sqlite-3.40.0/test/selectB.test (revision f23329a2) 
1# 2008 June 24
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# $Id: selectB.test,v 1.1 2008/07/01 14:09:14 danielk1977 Exp $
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18proc test_transform {testname sql1 sql2 results} {
19  set ::vdbe1 [list]
20  set ::vdbe2 [list]
21  db eval "explain $sql1" { lappend ::vdbe1 $opcode }
22  db eval "explain $sql2" { lappend ::vdbe2 $opcode }
23
24  do_test $testname.transform {
25    set ::vdbe1
26  } $::vdbe2
27
28  set ::sql1 $sql1
29  do_test $testname.sql1 {
30    execsql $::sql1
31  } $results
32
33  set ::sql2 $sql2
34  do_test $testname.sql2 {
35    execsql $::sql2
36  } $results
37}
38
39do_test selectB-1.1 {
40  execsql {
41    CREATE TABLE t1(a, b, c);
42    CREATE TABLE t2(d, e, f);
43
44    INSERT INTO t1 VALUES( 2,  4,  6);
45    INSERT INTO t1 VALUES( 8, 10, 12);
46    INSERT INTO t1 VALUES(14, 16, 18);
47
48    INSERT INTO t2 VALUES(3,   6,  9);
49    INSERT INTO t2 VALUES(12, 15, 18);
50    INSERT INTO t2 VALUES(21, 24, 27);
51  }
52} {}
53
54test_transform selectB-1.2 {
55  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
56} {
57  SELECT a FROM t1 UNION ALL SELECT d FROM t2
58} {2 8 14 3 12 21}
59
60test_transform selectB-1.3 {
61  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
62} {
63  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
64} {2 3 8 12 14 21}
65
66test_transform selectB-1.4 {
67  SELECT * FROM
68    (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
69  WHERE a>10 ORDER BY 1
70} {
71  SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
72} {12 14 21}
73
74test_transform selectB-1.5 {
75  SELECT * FROM
76    (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
77  WHERE a>10 ORDER BY a
78} {
79  SELECT a FROM t1 WHERE a>10
80    UNION ALL
81  SELECT d FROM t2 WHERE d>10
82  ORDER BY a
83} {12 14 21}
84
85test_transform selectB-1.6 {
86  SELECT * FROM
87    (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12)
88  WHERE a>10 ORDER BY a
89} {
90  SELECT a FROM t1 WHERE a>10
91    UNION ALL
92  SELECT d FROM t2 WHERE d>12 AND d>10
93  ORDER BY a
94} {14 21}
95
96
97finish_test
98
99