1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 15 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing aggregate functions and the 147c478bd9Sstevel@tonic-gate# GROUP BY and HAVING clauses of SELECT statements. 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: select5.test,v 1.6 2001/10/15 00:44:36 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gate# Build some test data 227c478bd9Sstevel@tonic-gate# 237c478bd9Sstevel@tonic-gateset fd [open data1.txt w] 247c478bd9Sstevel@tonic-gatefor {set i 1} {$i<32} {incr i} { 257c478bd9Sstevel@tonic-gate for {set j 0} {pow(2,$j)<$i} {incr j} {} 267c478bd9Sstevel@tonic-gate puts $fd "[expr {32-$i}]\t[expr {10-$j}]" 277c478bd9Sstevel@tonic-gate} 287c478bd9Sstevel@tonic-gateclose $fd 297c478bd9Sstevel@tonic-gateexecsql { 307c478bd9Sstevel@tonic-gate CREATE TABLE t1(x int, y int); 317c478bd9Sstevel@tonic-gate COPY t1 FROM 'data1.txt' 327c478bd9Sstevel@tonic-gate} 337c478bd9Sstevel@tonic-gatefile delete data1.txt 347c478bd9Sstevel@tonic-gate 357c478bd9Sstevel@tonic-gatedo_test select5-1.0 { 367c478bd9Sstevel@tonic-gate execsql {SELECT DISTINCT y FROM t1 ORDER BY y} 377c478bd9Sstevel@tonic-gate} {5 6 7 8 9 10} 387c478bd9Sstevel@tonic-gate 397c478bd9Sstevel@tonic-gate# Sort by an aggregate function. 407c478bd9Sstevel@tonic-gate# 417c478bd9Sstevel@tonic-gatedo_test select5-1.1 { 427c478bd9Sstevel@tonic-gate execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY y} 437c478bd9Sstevel@tonic-gate} {5 15 6 8 7 4 8 2 9 1 10 1} 447c478bd9Sstevel@tonic-gatedo_test select5-1.2 { 457c478bd9Sstevel@tonic-gate execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY count(*), y} 467c478bd9Sstevel@tonic-gate} {9 1 10 1 8 2 7 4 6 8 5 15} 477c478bd9Sstevel@tonic-gatedo_test select5-1.3 { 487c478bd9Sstevel@tonic-gate execsql {SELECT count(*), y FROM t1 GROUP BY y ORDER BY count(*), y} 497c478bd9Sstevel@tonic-gate} {1 9 1 10 2 8 4 7 8 6 15 5} 507c478bd9Sstevel@tonic-gate 517c478bd9Sstevel@tonic-gate# Some error messages associated with aggregates and GROUP BY 527c478bd9Sstevel@tonic-gate# 537c478bd9Sstevel@tonic-gatedo_test select5-2.1 { 547c478bd9Sstevel@tonic-gate set v [catch {execsql { 557c478bd9Sstevel@tonic-gate SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y 567c478bd9Sstevel@tonic-gate }} msg] 577c478bd9Sstevel@tonic-gate lappend v $msg 587c478bd9Sstevel@tonic-gate} {1 {no such column: z}} 597c478bd9Sstevel@tonic-gatedo_test select5-2.2 { 607c478bd9Sstevel@tonic-gate set v [catch {execsql { 617c478bd9Sstevel@tonic-gate SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y 627c478bd9Sstevel@tonic-gate }} msg] 637c478bd9Sstevel@tonic-gate lappend v $msg 647c478bd9Sstevel@tonic-gate} {1 {no such function: z}} 657c478bd9Sstevel@tonic-gatedo_test select5-2.3 { 667c478bd9Sstevel@tonic-gate set v [catch {execsql { 677c478bd9Sstevel@tonic-gate SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y 687c478bd9Sstevel@tonic-gate }} msg] 697c478bd9Sstevel@tonic-gate lappend v $msg 707c478bd9Sstevel@tonic-gate} {0 {8 2 9 1 10 1}} 717c478bd9Sstevel@tonic-gatedo_test select5-2.4 { 727c478bd9Sstevel@tonic-gate set v [catch {execsql { 737c478bd9Sstevel@tonic-gate SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y 747c478bd9Sstevel@tonic-gate }} msg] 757c478bd9Sstevel@tonic-gate lappend v $msg 767c478bd9Sstevel@tonic-gate} {1 {no such function: z}} 777c478bd9Sstevel@tonic-gatedo_test select5-2.5 { 787c478bd9Sstevel@tonic-gate set v [catch {execsql { 797c478bd9Sstevel@tonic-gate SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y 807c478bd9Sstevel@tonic-gate }} msg] 817c478bd9Sstevel@tonic-gate lappend v $msg 827c478bd9Sstevel@tonic-gate} {1 {no such column: z}} 837c478bd9Sstevel@tonic-gate 847c478bd9Sstevel@tonic-gate# Get the Agg function to rehash in vdbe.c 857c478bd9Sstevel@tonic-gate# 867c478bd9Sstevel@tonic-gatedo_test select5-3.1 { 877c478bd9Sstevel@tonic-gate execsql { 887c478bd9Sstevel@tonic-gate SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x 897c478bd9Sstevel@tonic-gate } 907c478bd9Sstevel@tonic-gate} {1 1 5 2 1 5 3 1 5} 917c478bd9Sstevel@tonic-gate 927c478bd9Sstevel@tonic-gate# Run various aggregate functions when the count is zero. 937c478bd9Sstevel@tonic-gate# 947c478bd9Sstevel@tonic-gatedo_test select5-4.1 { 957c478bd9Sstevel@tonic-gate execsql { 967c478bd9Sstevel@tonic-gate SELECT avg(x) FROM t1 WHERE x>100 977c478bd9Sstevel@tonic-gate } 987c478bd9Sstevel@tonic-gate} {{}} 997c478bd9Sstevel@tonic-gatedo_test select5-4.2 { 1007c478bd9Sstevel@tonic-gate execsql { 1017c478bd9Sstevel@tonic-gate SELECT count(x) FROM t1 WHERE x>100 1027c478bd9Sstevel@tonic-gate } 1037c478bd9Sstevel@tonic-gate} {0} 1047c478bd9Sstevel@tonic-gatedo_test select5-4.3 { 1057c478bd9Sstevel@tonic-gate execsql { 1067c478bd9Sstevel@tonic-gate SELECT min(x) FROM t1 WHERE x>100 1077c478bd9Sstevel@tonic-gate } 1087c478bd9Sstevel@tonic-gate} {{}} 1097c478bd9Sstevel@tonic-gatedo_test select5-4.4 { 1107c478bd9Sstevel@tonic-gate execsql { 1117c478bd9Sstevel@tonic-gate SELECT max(x) FROM t1 WHERE x>100 1127c478bd9Sstevel@tonic-gate } 1137c478bd9Sstevel@tonic-gate} {{}} 1147c478bd9Sstevel@tonic-gatedo_test select5-4.5 { 1157c478bd9Sstevel@tonic-gate execsql { 1167c478bd9Sstevel@tonic-gate SELECT sum(x) FROM t1 WHERE x>100 1177c478bd9Sstevel@tonic-gate } 1187c478bd9Sstevel@tonic-gate} {0} 1197c478bd9Sstevel@tonic-gate 1207c478bd9Sstevel@tonic-gatefinish_test 121