Annotation of embedaddon/sqlite3/test/distinct.test, revision 1.1
1.1 ! misho 1: # 2011 July 1
! 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. The
! 12: # focus of this script is the DISTINCT modifier.
! 13: #
! 14:
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17:
! 18: set testprefix distinct
! 19:
! 20:
! 21: proc is_distinct_noop {sql} {
! 22: set sql1 $sql
! 23: set sql2 [string map {DISTINCT ""} $sql]
! 24:
! 25: set program1 [list]
! 26: set program2 [list]
! 27: db eval "EXPLAIN $sql1" {
! 28: if {$opcode != "Noop"} { lappend program1 $opcode }
! 29: }
! 30: db eval "EXPLAIN $sql2" {
! 31: if {$opcode != "Noop"} { lappend program2 $opcode }
! 32: }
! 33:
! 34: return [expr {$program1==$program2}]
! 35: }
! 36:
! 37: proc do_distinct_noop_test {tn sql} {
! 38: uplevel [list do_test $tn [list is_distinct_noop $sql] 1]
! 39: }
! 40: proc do_distinct_not_noop_test {tn sql} {
! 41: uplevel [list do_test $tn [list is_distinct_noop $sql] 0]
! 42: }
! 43:
! 44: proc do_temptables_test {tn sql temptables} {
! 45: uplevel [list do_test $tn [subst -novar {
! 46: set ret ""
! 47: db eval "EXPLAIN [set sql]" {
! 48: if {$opcode == "OpenEphemeral" || $opcode == "SorterOpen"} {
! 49: if {$p5 != "10" && $p5!="00"} { error "p5 = $p5" }
! 50: if {$p5 == "10"} {
! 51: lappend ret hash
! 52: } else {
! 53: lappend ret btree
! 54: }
! 55: }
! 56: }
! 57: set ret
! 58: }] $temptables]
! 59: }
! 60:
! 61:
! 62: #-------------------------------------------------------------------------
! 63: # The following tests - distinct-1.* - check that the planner correctly
! 64: # detects cases where a UNIQUE index means that a DISTINCT clause is
! 65: # redundant. Currently the planner only detects such cases when there
! 66: # is a single table in the FROM clause.
! 67: #
! 68: do_execsql_test 1.0 {
! 69: CREATE TABLE t1(a, b, c, d);
! 70: CREATE UNIQUE INDEX i1 ON t1(b, c);
! 71: CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase);
! 72:
! 73: CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
! 74:
! 75: CREATE TABLE t3(c1 PRIMARY KEY, c2);
! 76: CREATE INDEX i3 ON t3(c2);
! 77: }
! 78: foreach {tn noop sql} {
! 79:
! 80: 1 1 "SELECT DISTINCT b, c FROM t1"
! 81: 2 1 "SELECT DISTINCT c FROM t1 WHERE b = ?"
! 82: 3 1 "SELECT DISTINCT rowid FROM t1"
! 83: 4 1 "SELECT DISTINCT rowid, a FROM t1"
! 84: 5 1 "SELECT DISTINCT x FROM t2"
! 85: 6 1 "SELECT DISTINCT * FROM t2"
! 86: 7 1 "SELECT DISTINCT * FROM (SELECT * FROM t2)"
! 87:
! 88: 8 1 "SELECT DISTINCT * FROM t1"
! 89:
! 90: 8 0 "SELECT DISTINCT a, b FROM t1"
! 91:
! 92: 9 0 "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)"
! 93: 10 0 "SELECT DISTINCT c FROM t1"
! 94: 11 0 "SELECT DISTINCT b FROM t1"
! 95:
! 96: 12 0 "SELECT DISTINCT a, d FROM t1"
! 97: 13 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t1"
! 98: 14 1 "SELECT DISTINCT a, d COLLATE nocase FROM t1"
! 99: 15 0 "SELECT DISTINCT a, d COLLATE binary FROM t1"
! 100: 16 1 "SELECT DISTINCT a, b, c COLLATE binary FROM t1"
! 101:
! 102: 16 0 "SELECT DISTINCT t1.rowid FROM t1, t2"
! 103: 17 0 { /* Technically, it would be possible to detect that DISTINCT
! 104: ** is a no-op in cases like the following. But SQLite does not
! 105: ** do so. */
! 106: SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid }
! 107:
! 108: 18 1 "SELECT DISTINCT c1, c2 FROM t3"
! 109: 19 1 "SELECT DISTINCT c1 FROM t3"
! 110: 20 1 "SELECT DISTINCT * FROM t3"
! 111: 21 0 "SELECT DISTINCT c2 FROM t3"
! 112:
! 113: 22 0 "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
! 114: 23 1 "SELECT DISTINCT rowid FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
! 115:
! 116: 24 0 "SELECT DISTINCT rowid/2 FROM t1"
! 117: 25 1 "SELECT DISTINCT rowid/2, rowid FROM t1"
! 118: 26 1 "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?"
! 119: } {
! 120: if {$noop} {
! 121: do_distinct_noop_test 1.$tn $sql
! 122: } else {
! 123: do_distinct_not_noop_test 1.$tn $sql
! 124: }
! 125: }
! 126:
! 127: #-------------------------------------------------------------------------
! 128: # The following tests - distinct-2.* - test cases where an index is
! 129: # used to deliver results in order of the DISTINCT expressions.
! 130: #
! 131: drop_all_tables
! 132: do_execsql_test 2.0 {
! 133: CREATE TABLE t1(a, b, c);
! 134:
! 135: CREATE INDEX i1 ON t1(a, b);
! 136: CREATE INDEX i2 ON t1(b COLLATE nocase, c COLLATE nocase);
! 137:
! 138: INSERT INTO t1 VALUES('a', 'b', 'c');
! 139: INSERT INTO t1 VALUES('A', 'B', 'C');
! 140: INSERT INTO t1 VALUES('a', 'b', 'c');
! 141: INSERT INTO t1 VALUES('A', 'B', 'C');
! 142: }
! 143:
! 144: foreach {tn sql temptables res} {
! 145: 1 "a, b FROM t1" {} {A B a b}
! 146: 2 "b, a FROM t1" {} {B A b a}
! 147: 3 "a, b, c FROM t1" {hash} {a b c A B C}
! 148: 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c}
! 149: 5 "b FROM t1 WHERE a = 'a'" {} {b}
! 150: 6 "b FROM t1" {hash} {b B}
! 151: 7 "a FROM t1" {} {A a}
! 152: 8 "b COLLATE nocase FROM t1" {} {b}
! 153: 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {B}
! 154: } {
! 155: do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res
! 156: do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
! 157: }
! 158:
! 159: do_execsql_test 2.A {
! 160: SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o;
! 161: } {a A a A}
! 162:
! 163:
! 164:
! 165:
! 166: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to distinct.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test