AryaWu/sqlite
0
1# 2005 September 112#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 script is the DISTINCT modifier on aggregate functions.13#14# $Id: distinctagg.test,v 1.3 2009/02/09 13:19:28 drh Exp $15 16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix distinctagg20 21do_test distinctagg-1.1 {22 execsql {23 CREATE TABLE t1(a,b,c);24 INSERT INTO t1 VALUES(1,2,3);25 INSERT INTO t1 VALUES(1,3,4);26 INSERT INTO t1 VALUES(1,3,5);27 SELECT count(distinct a),28 count(distinct b),29 count(distinct c),30 count(all a) FROM t1;31 }32} {1 2 3 3}33do_test distinctagg-1.2 {34 execsql {35 SELECT b, count(distinct c) FROM t1 GROUP BY b36 }37} {2 1 3 2}38do_test distinctagg-1.3 {39 execsql {40 INSERT INTO t1 SELECT a+1, b+3, c+5 FROM t1;41 INSERT INTO t1 SELECT a+2, b+6, c+10 FROM t1;42 INSERT INTO t1 SELECT a+4, b+12, c+20 FROM t1;43 SELECT count(*), count(distinct a), count(distinct b) FROM t144 }45} {24 8 16}46do_test distinctagg-1.4 {47 execsql {48 SELECT a, count(distinct c) FROM t1 GROUP BY a ORDER BY a49 }50} {1 3 2 3 3 3 4 3 5 3 6 3 7 3 8 3}51 52do_test distinctagg-2.1 {53 catchsql {54 SELECT count(distinct) FROM t1;55 }56} {1 {DISTINCT aggregates must have exactly one argument}}57do_test distinctagg-2.2 {58 catchsql {59 SELECT string_agg(distinct a,b) FROM t1;60 }61} {1 {DISTINCT aggregates must have exactly one argument}}62 63#--------------------------------------------------------------------------64reset_db65do_execsql_test 3.0 {66 CREATE TABLE t1(a, b, c);67 CREATE TABLE t2(d, e, f);68 69 INSERT INTO t1 VALUES (1, 1, 1);70 INSERT INTO t1 VALUES (2, 2, 2);71 INSERT INTO t1 VALUES (3, 3, 3);72 INSERT INTO t1 VALUES (4, 1, 4);73 INSERT INTO t1 VALUES (5, 2, 1);74 INSERT INTO t1 VALUES (5, 3, 2);75 INSERT INTO t1 VALUES (4, 1, 3);76 INSERT INTO t1 VALUES (3, 2, 4);77 INSERT INTO t1 VALUES (2, 3, 1);78 INSERT INTO t1 VALUES (1, 1, 2);79 80 INSERT INTO t2 VALUES('a', 'a', 'a');81 INSERT INTO t2 VALUES('b', 'b', 'b');82 INSERT INTO t2 VALUES('c', 'c', 'c');83 84 CREATE INDEX t1a ON t1(a);85 CREATE INDEX t1bc ON t1(b, c);86}87 88foreach {tn use_eph sql res} {89 1 0 "SELECT count(DISTINCT a) FROM t1" 590 2 0 "SELECT count(DISTINCT b) FROM t1" 391 3 1 "SELECT count(DISTINCT c) FROM t1" 492 4 0 "SELECT count(DISTINCT c) FROM t1 WHERE b=3" 393 5 0 "SELECT count(DISTINCT rowid) FROM t1" 1094 6 0 "SELECT count(DISTINCT a) FROM t1, t2" 595 7 0 "SELECT count(DISTINCT a) FROM t2, t1" 596 8 1 "SELECT count(DISTINCT a+b) FROM t1, t2, t2, t2" 697 9 0 "SELECT count(DISTINCT c) FROM t1 WHERE c=2" 198 10 0 "SELECT count(DISTINCT t1.rowid) FROM t1, t2" 1099} {100 do_test 3.$tn.1 {101 set prg [db eval "EXPLAIN $sql"]102 set idx [lsearch $prg OpenEphemeral]103 expr {$idx>=0}104 } $use_eph105 106 do_execsql_test 3.$tn.2 $sql $res107}108 109do_execsql_test 3.10 {110 SELECT a, count(DISTINCT b) FROM t1 GROUP BY a;111} {112 1 1 2 2 3 2 4 1 5 2113}114 115#--------------------------------------------------------------------------116reset_db117do_execsql_test 3.0 {118 CREATE TABLE t1(a, b, c);119 CREATE INDEX t1a ON t1(a);120 CREATE INDEX t1bc ON t1(b, c);121 122 INSERT INTO t1 VALUES(1, 'A', 1);123 INSERT INTO t1 VALUES(1, 'A', 1);124 INSERT INTO t1 VALUES(2, 'A', 2);125 INSERT INTO t1 VALUES(2, 'A', 2);126 INSERT INTO t1 VALUES(1, 'B', 1);127 INSERT INTO t1 VALUES(2, 'B', 2);128 INSERT INTO t1 VALUES(3, 'B', 3);129 INSERT INTO t1 VALUES(NULL, 'B', NULL);130 INSERT INTO t1 VALUES(NULL, 'C', NULL);131 INSERT INTO t1 VALUES('d', 'D', 'd');132 133 CREATE TABLE t2(d, e, f);134 CREATE INDEX t2def ON t2(d, e, f);135 136 INSERT INTO t2 VALUES(1, 1, 'a');137 INSERT INTO t2 VALUES(1, 1, 'a');138 INSERT INTO t2 VALUES(1, 2, 'a');139 INSERT INTO t2 VALUES(1, 2, 'a');140 INSERT INTO t2 VALUES(1, 2, 'b');141 INSERT INTO t2 VALUES(1, 3, 'b');142 INSERT INTO t2 VALUES(1, 3, 'a');143 INSERT INTO t2 VALUES(1, 3, 'b');144 INSERT INTO t2 VALUES(2, 3, 'x');145 INSERT INTO t2 VALUES(2, 3, 'y');146 INSERT INTO t2 VALUES(2, 3, 'z');147 148 CREATE TABLE t3(x, y, z);149 INSERT INTO t3 VALUES(1,1,1);150 INSERT INTO t3 VALUES(2,2,2);151 152 CREATE TABLE t4(a);153 CREATE INDEX t4a ON t4(a);154 INSERT INTO t4 VALUES(1), (2), (2), (3), (1);155}156 157foreach {tn use_eph sql res} {158 1 0 "SELECT count(DISTINCT c) FROM t1 GROUP BY b" {2 3 0 1}159 2 1 "SELECT count(DISTINCT a) FROM t1 GROUP BY b" {2 3 0 1}160 3 1 "SELECT count(DISTINCT a) FROM t1 GROUP BY b+c" {0 1 1 1 1}161 162 4 0 "SELECT count(DISTINCT f) FROM t2 GROUP BY d, e" {1 2 2 3}163 5 1 "SELECT count(DISTINCT f) FROM t2 GROUP BY d" {2 3}164 6 0 "SELECT count(DISTINCT f) FROM t2 WHERE d IS 1 GROUP BY e" {1 2 2}165 166 7 0 "SELECT count(DISTINCT a) FROM t1" {4}167 8 0 "SELECT count(DISTINCT a) FROM t4" {3}168} {169 do_test 4.$tn.1 {170 set prg [db eval "EXPLAIN $sql"]171 set idx [lsearch $prg OpenEphemeral]172 expr {$idx>=0}173 } $use_eph174 175 do_execsql_test 4.$tn.2 $sql $res176}177 178 179set t3root [db one {SELECT rootpage FROM sqlite_schema WHERE name='t3'}]180foreach {tn use_t3 sql res} {181 1 1 "SELECT count(*) FROM t3" 2182 2 0 "SELECT count(*) FROM t1" 10183 2 1 "SELECT count(DISTINCT a) FROM t1, t3" 4184 3 1 "SELECT count(DISTINCT a) FROM t1 LEFT JOIN t3" 4185 4 1 "SELECT count(DISTINCT a) FROM t1 LEFT JOIN t3 WHERE t3.x=1" 4186 5 1 "SELECT count(DISTINCT a) FROM t1 LEFT JOIN t3 WHERE t3.x=0" 0187 6 1 "SELECT count(DISTINCT a) FROM t1 LEFT JOIN t3 ON (t3.x=0)" 4188 7 1 "SELECT count(DISTINCT x) FROM t1 LEFT JOIN t3" 2189 8 1 "SELECT count(DISTINCT x) FROM t1 LEFT JOIN t3 WHERE t3.x=1" 1190 9 1 "SELECT count(DISTINCT x) FROM t1 LEFT JOIN t3 WHERE t3.x=0" 0191 10 1 "SELECT count(DISTINCT x) FROM t1 LEFT JOIN t3 ON (t3.x=0)" 0192 193} {194 unset -nocomplain a195 do_test 5.$tn.1 {196 set bUse 0197 db eval "EXPLAIN $sql" a {198 if {$a(opcode)=="OpenRead" && $a(p2)==$t3root} {set bUse 1}199 }200 set bUse201 } $use_t3202 203 do_execsql_test 5.$tn.2 $sql $res204}205 206#-------------------------------------------------------------------------207reset_db208do_execsql_test 6.0 {209 CREATE TABLE t1(a, b);210 CREATE TABLE t2(c, d);211 INSERT INTO t1 VALUES(123,456);212 INSERT INTO t2 VALUES(123,456);213}214do_execsql_test 6.1 {215 SELECT count(DISTINCT c) FROM t1 LEFT JOIN t2;216} {1}217 218do_execsql_test 7.0 {219 CREATE TABLE v1 ( v2 UNIQUE, v3 AS( TYPEOF ( NULL ) ) UNIQUE ); 220 SELECT COUNT ( DISTINCT TRUE ) FROM v1 GROUP BY likelihood ( v3 , 0.100000 );221}222 223 224finish_test225