[sqlite] Remove obsolete reference version 3.8.7.4.

third_party/sqlite/sqlite-src-3080704/test/distinct.test

-# 2011 July 1
-#
-# 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.  The
-# focus of this script is the DISTINCT modifier.
-#
-
-set testdir [file dirname $argv0]
-source $testdir/tester.tcl
-
-ifcapable !compound {
-  finish_test
-  return
-}
-
-set testprefix distinct
-
-
-proc is_distinct_noop {sql} {
-  set sql1 $sql
-  set sql2 [string map {DISTINCT ""} $sql]
-
-  set program1 [list]
-  set program2 [list]
-  db eval "EXPLAIN $sql1" {
-    if {$opcode != "Noop"} { lappend program1 $opcode }
-  }
-  db eval "EXPLAIN $sql2" {
-    if {$opcode != "Noop"} { lappend program2 $opcode }
-  }
-
-  return [expr {$program1==$program2}]
-}
-
-proc do_distinct_noop_test {tn sql} {
-  uplevel [list do_test $tn [list is_distinct_noop $sql] 1]
-}
-proc do_distinct_not_noop_test {tn sql} {
-  uplevel [list do_test $tn [list is_distinct_noop $sql] 0]
-}
-
-proc do_temptables_test {tn sql temptables} {
-  uplevel [list do_test $tn [subst -novar {
-    set ret ""
-    db eval "EXPLAIN [set sql]" {
-      if {$opcode == "OpenEphemeral" || $opcode == "SorterOpen"} { 
-        if {$p5 != "08" && $p5!="00"} { error "p5 = $p5" }
-        if {$p5 == "08"} {
-          lappend ret hash
-        } else {
-          lappend ret btree
-        }
-      }
-    }
-    set ret
-  }] $temptables]
-}
-
-
-#-------------------------------------------------------------------------
-# The following tests - distinct-1.* - check that the planner correctly 
-# detects cases where a UNIQUE index means that a DISTINCT clause is 
-# redundant. Currently the planner only detects such cases when there
-# is a single table in the FROM clause.
-#
-do_execsql_test 1.0 {
-  CREATE TABLE t1(a, b, c, d);
-  CREATE UNIQUE INDEX i1 ON t1(b, c);
-  CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase);
-
-  CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
-
-  CREATE TABLE t3(c1 PRIMARY KEY NOT NULL, c2 NOT NULL);
-  CREATE INDEX i3 ON t3(c2);
-
-  CREATE TABLE t4(a, b NOT NULL, c NOT NULL, d NOT NULL);
-  CREATE UNIQUE INDEX t4i1 ON t4(b, c);
-  CREATE UNIQUE INDEX t4i2 ON t4(d COLLATE nocase);
-}
-foreach {tn noop sql} {
-
-  1.1 0   "SELECT DISTINCT b, c FROM t1"
-  1.2 1   "SELECT DISTINCT b, c FROM t4"
-  2.1 0   "SELECT DISTINCT c FROM t1 WHERE b = ?"
-  2.2 1   "SELECT DISTINCT c FROM t4 WHERE b = ?"
-  3   1   "SELECT DISTINCT rowid FROM t1"
-  4   1   "SELECT DISTINCT rowid, a FROM t1"
-  5   1   "SELECT DISTINCT x FROM t2"
-  6   1   "SELECT DISTINCT * FROM t2"
-  7   1   "SELECT DISTINCT * FROM (SELECT * FROM t2)"
-
-  8.1 0   "SELECT DISTINCT * FROM t1"
-  8.2 1   "SELECT DISTINCT * FROM t4"
-
-  8   0   "SELECT DISTINCT a, b FROM t1"
-
-  9   0   "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)"
-  10  0   "SELECT DISTINCT c FROM t1"
-  11  0   "SELECT DISTINCT b FROM t1"
-
-  12.1 0   "SELECT DISTINCT a, d FROM t1"
-  12.2 0   "SELECT DISTINCT a, d FROM t4"
-  13.1 0   "SELECT DISTINCT a, b, c COLLATE nocase FROM t1"
-  13.2 0   "SELECT DISTINCT a, b, c COLLATE nocase FROM t4"
-  14.1 0   "SELECT DISTINCT a, d COLLATE nocase FROM t1"
-  14.2 1   "SELECT DISTINCT a, d COLLATE nocase FROM t4"
-
-  15   0   "SELECT DISTINCT a, d COLLATE binary FROM t1"
-  16.1 0   "SELECT DISTINCT a, b, c COLLATE binary FROM t1"
-  16.2 1   "SELECT DISTINCT a, b, c COLLATE binary FROM t4"
-
-  16  0   "SELECT DISTINCT t1.rowid FROM t1, t2"
-  17  0   { /* Technically, it would be possible to detect that DISTINCT
-            ** is a no-op in cases like the following. But SQLite does not
-            ** do so. */
-            SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid }
-
-  18  1   "SELECT DISTINCT c1, c2 FROM t3"
-  19  1   "SELECT DISTINCT c1 FROM t3"
-  20  1   "SELECT DISTINCT * FROM t3"
-  21  0   "SELECT DISTINCT c2 FROM t3"
-
-  22  0   "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
-  23  1   "SELECT DISTINCT rowid FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
-
-  24  0   "SELECT DISTINCT rowid/2 FROM t1"
-  25  1   "SELECT DISTINCT rowid/2, rowid FROM t1"
-  26.1  0   "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?"
-  26.2  1   "SELECT DISTINCT rowid/2, b FROM t4 WHERE c = ?"
-} {
-  if {$noop} {
-    do_distinct_noop_test 1.$tn $sql
-  } else {
-    do_distinct_not_noop_test 1.$tn $sql
-  }
-}
-
-#-------------------------------------------------------------------------
-# The following tests - distinct-2.* - test cases where an index is
-# used to deliver results in order of the DISTINCT expressions. 
-#
-drop_all_tables
-do_execsql_test 2.0 {
-  CREATE TABLE t1(a, b, c);
-
-  CREATE INDEX i1 ON t1(a, b);
-  CREATE INDEX i2 ON t1(b COLLATE nocase, c COLLATE nocase);
-
-  INSERT INTO t1 VALUES('a', 'b', 'c');
-  INSERT INTO t1 VALUES('A', 'B', 'C');
-  INSERT INTO t1 VALUES('a', 'b', 'c');
-  INSERT INTO t1 VALUES('A', 'B', 'C');
-}
-
-foreach {tn sql temptables res} {
-  1   "a, b FROM t1"                                       {}      {A B a b}
-  2   "b, a FROM t1"                                       {}      {B A b a}
-  3   "a, b, c FROM t1"                                    {hash}  {A B C a b c}
-  4   "a, b, c FROM t1 ORDER BY a, b, c"                   {btree} {A B C a b c}
-  5   "b FROM t1 WHERE a = 'a'"                            {}      {b}
-  6   "b FROM t1 ORDER BY +b COLLATE binary"          {btree hash} {B b}
-  7   "a FROM t1"                                          {}      {A a}
-  8   "b COLLATE nocase FROM t1"                           {}      {b}
-  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {b}
-} {
-  do_execsql_test    2.$tn.1 "SELECT DISTINCT $sql" $res
-  do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
-}
-
-do_execsql_test 2.A {
-  SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
-} {a A a A}
-
-do_test 3.0 {
-  db eval {
-    CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b));
-    INSERT INTO t3 VALUES
-        (null, null, 1),
-        (null, null, 2),
-        (null, 3, 4),
-        (null, 3, 5),
-        (6, null, 7),
-        (6, null, 8);
-    SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
-  }
-} {{} {} {} 3 6 {}}
-do_test 3.1 {
-  regexp {OpenEphemeral} [db eval {
-    EXPLAIN SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
-  }]
-} {0}
-
-#-------------------------------------------------------------------------
-# Ticket  [fccbde530a6583bf2748400919f1603d5425995c] (2014-01-08)
-# The logic that computes DISTINCT sometimes thinks that a zeroblob()
-# and a blob of all zeros are different when they should be the same. 
-#
-do_execsql_test 4.1 {
-  DROP TABLE IF EXISTS t1;
-  DROP TABLE IF EXISTS t2;
-  CREATE TABLE t1(a INTEGER);
-  INSERT INTO t1 VALUES(3);
-  INSERT INTO t1 VALUES(2);
-  INSERT INTO t1 VALUES(1);
-  INSERT INTO t1 VALUES(2);
-  INSERT INTO t1 VALUES(3);
-  INSERT INTO t1 VALUES(1);
-  CREATE TABLE t2(x);
-  INSERT INTO t2
-    SELECT DISTINCT
-      CASE a WHEN 1 THEN x'0000000000'
-             WHEN 2 THEN zeroblob(5)
-             ELSE 'xyzzy' END
-      FROM t1;
-  SELECT quote(x) FROM t2 ORDER BY 1;
-} {'xyzzy' X'0000000000'}
-
-finish_test