Imported Upstream version 2.0.3

1 files changed, 381 insertions, 0 deletions

diff --git a/test/selectB.test b/test/selectB.test
new file mode 100644
index 0000000..b9d979a
--- /dev/null
+++ b/test/selectB.test
@@ -0,0 +1,381 @@
+# 2008 June 24
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library. 
+#
+# $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !compound {
+  finish_test
+  return
+}
+
+proc test_transform {testname sql1 sql2 results} {
+  set ::vdbe1 [list]
+  set ::vdbe2 [list]
+  db eval "explain $sql1" { lappend ::vdbe1 $opcode }
+  db eval "explain $sql2" { lappend ::vdbe2 $opcode }
+
+  do_test $testname.transform {
+    set ::vdbe1
+  } $::vdbe2
+
+  set ::sql1 $sql1
+  do_test $testname.sql1 {
+    execsql $::sql1
+  } $results
+
+  set ::sql2 $sql2
+  do_test $testname.sql2 {
+    execsql $::sql2
+  } $results
+}
+
+do_test selectB-1.1 {
+  execsql {
+    CREATE TABLE t1(a, b, c);
+    CREATE TABLE t2(d, e, f);
+
+    INSERT INTO t1 VALUES( 2,  4,  6);
+    INSERT INTO t1 VALUES( 8, 10, 12);
+    INSERT INTO t1 VALUES(14, 16, 18);
+
+    INSERT INTO t2 VALUES(3,   6,  9);
+    INSERT INTO t2 VALUES(12, 15, 18);
+    INSERT INTO t2 VALUES(21, 24, 27);
+  }
+} {}
+
+for {set ii 1} {$ii <= 2} {incr ii} {
+
+  if {$ii == 2} {
+    do_test selectB-2.1 {
+      execsql {
+        CREATE INDEX i1 ON t1(a);
+        CREATE INDEX i2 ON t2(d);
+      }
+    } {}
+  }
+
+  test_transform selectB-$ii.2 {
+    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
+  } {
+    SELECT a FROM t1 UNION ALL SELECT d FROM t2
+  } {2 8 14 3 12 21}
+  
+  test_transform selectB-$ii.3 {
+    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
+  } {
+    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
+  } {2 3 8 12 14 21}
+  
+  test_transform selectB-$ii.4 {
+    SELECT * FROM 
+      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
+    WHERE a>10 ORDER BY 1
+  } {
+    SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
+  } {12 14 21}
+  
+  test_transform selectB-$ii.5 {
+    SELECT * FROM 
+      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
+    WHERE a>10 ORDER BY a
+  } {
+    SELECT a FROM t1 WHERE a>10 
+      UNION ALL 
+    SELECT d FROM t2 WHERE d>10 
+    ORDER BY a
+  } {12 14 21}
+  
+  test_transform selectB-$ii.6 {
+    SELECT * FROM 
+      (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) 
+    WHERE a>10 ORDER BY a
+  } {
+    SELECT a FROM t1 WHERE a>10
+      UNION ALL 
+    SELECT d FROM t2 WHERE d>12 AND d>10
+    ORDER BY a
+  } {14 21}
+  
+  test_transform selectB-$ii.7 {
+    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
+    LIMIT 2
+  } {
+    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
+  } {2 3}
+  
+  test_transform selectB-$ii.8 {
+    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
+    LIMIT 2 OFFSET 3
+  } {
+    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
+  } {12 14}
+
+  test_transform selectB-$ii.9 {
+    SELECT * FROM (
+      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
+    ) 
+  } {
+    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
+  } {2 8 14 3 12 21 6 12 18}
+  
+  test_transform selectB-$ii.10 {
+    SELECT * FROM (
+      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
+    ) ORDER BY 1
+  } {
+    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
+    ORDER BY 1
+  } {2 3 6 8 12 12 14 18 21}
+  
+  test_transform selectB-$ii.11 {
+    SELECT * FROM (
+      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
+    ) WHERE a>=10 ORDER BY 1 LIMIT 3
+  } {
+    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
+    UNION ALL SELECT c FROM t1 WHERE c>=10
+    ORDER BY 1 LIMIT 3
+  } {12 12 14}
+
+  test_transform selectB-$ii.12 {
+    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
+  } {
+    SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
+  } {2 8}
+
+  # An ORDER BY in a compound subqueries defeats flattening.  Ticket #3773
+  # test_transform selectB-$ii.13 {
+  #   SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
+  # } {
+  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
+  # } {2 3 8 12 14 21}
+  # 
+  # test_transform selectB-$ii.14 {
+  #  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
+  # } {
+  #  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
+  # } {21 14 12 8 3 2}
+  #
+  # test_transform selectB-$ii.14 {
+  #   SELECT * FROM (
+  #     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
+  #   ) LIMIT 2 OFFSET 2
+  # } {
+  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
+  #    LIMIT 2 OFFSET 2
+  # } {12 8}
+  #
+  # test_transform selectB-$ii.15 {
+  #   SELECT * FROM (
+  #     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
+  #  )
+  # } {
+  #   SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
+  # } {2 4 3 6 8 10 12 15 14 16 21 24}
+}
+
+do_test selectB-3.0 {
+  execsql {
+    DROP INDEX i1;
+    DROP INDEX i2;
+  }
+} {}
+
+for {set ii 3} {$ii <= 4} {incr ii} {
+
+  if {$ii == 4} {
+    do_test selectB-4.0 {
+      execsql {
+        CREATE INDEX i1 ON t1(a);
+        CREATE INDEX i2 ON t1(b);
+        CREATE INDEX i3 ON t1(c);
+        CREATE INDEX i4 ON t2(d);
+        CREATE INDEX i5 ON t2(e);
+        CREATE INDEX i6 ON t2(f);
+      }
+    } {}
+  }
+
+  do_test selectB-$ii.1 {
+    execsql {
+      SELECT DISTINCT * FROM 
+        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
+      ORDER BY 1;
+    }
+  } {6 12 15 18 24}
+  
+  do_test selectB-$ii.2 {
+    execsql {
+      SELECT c, count(*) FROM 
+        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
+      GROUP BY c ORDER BY 1;
+    }
+  } {6 2 12 1 15 1 18 1 24 1}
+  do_test selectB-$ii.3 {
+    execsql {
+      SELECT c, count(*) FROM 
+        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
+      GROUP BY c HAVING count(*)>1;
+    }
+  } {6 2}
+  do_test selectB-$ii.4 {
+    execsql {
+      SELECT t4.c, t3.a FROM 
+        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
+      WHERE t3.a=14
+      ORDER BY 1
+    }
+  } {6 14 6 14 12 14 15 14 18 14 24 14}
+  
+  do_test selectB-$ii.5 {
+    execsql {
+      SELECT d FROM t2 
+      EXCEPT 
+      SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
+    }
+  } {}
+  do_test selectB-$ii.6 {
+    execsql {
+      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
+      EXCEPT 
+      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
+    }
+  } {}
+  do_test selectB-$ii.7 {
+    execsql {
+      SELECT c FROM t1
+      EXCEPT 
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+    }
+  } {12}
+  do_test selectB-$ii.8 {
+    execsql {
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      EXCEPT 
+      SELECT c FROM t1
+    }
+  } {9 15 24 27}
+  do_test selectB-$ii.9 {
+    execsql {
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      EXCEPT 
+      SELECT c FROM t1
+      ORDER BY c DESC
+    }
+  } {27 24 15 9}
+  
+  do_test selectB-$ii.10 {
+    execsql {
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      UNION 
+      SELECT c FROM t1
+      ORDER BY c DESC
+    }
+  } {27 24 18 15 12 9 6}
+  do_test selectB-$ii.11 {
+    execsql {
+      SELECT c FROM t1
+      UNION 
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      ORDER BY c
+    }
+  } {6 9 12 15 18 24 27}
+  do_test selectB-$ii.12 {
+    execsql {
+      SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
+      ORDER BY c
+    }
+  } {6 9 12 15 18 18 24 27}
+  do_test selectB-$ii.13 {
+    execsql {
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      UNION 
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      ORDER BY 1
+    }
+  } {6 9 15 18 24 27}
+  
+  do_test selectB-$ii.14 {
+    execsql {
+      SELECT c FROM t1
+      INTERSECT 
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      ORDER BY 1
+    }
+  } {6 18}
+  do_test selectB-$ii.15 {
+    execsql {
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      INTERSECT 
+      SELECT c FROM t1
+      ORDER BY 1
+    }
+  } {6 18}
+  do_test selectB-$ii.16 {
+    execsql {
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      INTERSECT 
+      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
+      ORDER BY 1
+    }
+  } {6 9 15 18 24 27}
+
+  do_test selectB-$ii.17 {
+    execsql {
+      SELECT * FROM (
+        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
+      ) LIMIT 2
+    }
+  } {2 8}
+
+  do_test selectB-$ii.18 {
+    execsql {
+      SELECT * FROM (
+        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
+      ) LIMIT 2
+    }
+  } {14 3}
+
+  do_test selectB-$ii.19 {
+    execsql {
+      SELECT * FROM (
+        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
+      )
+    }
+  } {0 1 1 0}
+
+  do_test selectB-$ii.20 {
+    execsql {
+      SELECT DISTINCT * FROM (
+        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
+      )
+    }
+  } {0 1}
+
+  do_test selectB-$ii.21 {
+    execsql {
+      SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
+    }
+  } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
+
+  do_test selectB-$ii.21 {
+    execsql {
+      SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
+    }
+  } {3 12 21 345}
+}
+
+finish_test