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 the SELECT statement. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: select2.test,v 1.18 2002/04/02 13:26:11 drh Exp $ 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 197c478bd9Sstevel@tonic-gate 207c478bd9Sstevel@tonic-gate# Create a table with some data 217c478bd9Sstevel@tonic-gate# 227c478bd9Sstevel@tonic-gateexecsql {CREATE TABLE tbl1(f1 int, f2 int)} 237c478bd9Sstevel@tonic-gateset f [open ./testdata1.txt w] 247c478bd9Sstevel@tonic-gatefor {set i 0} {$i<=30} {incr i} { 257c478bd9Sstevel@tonic-gate puts $f "[expr {$i%9}]\t[expr {$i%10}]" 267c478bd9Sstevel@tonic-gate} 277c478bd9Sstevel@tonic-gateclose $f 287c478bd9Sstevel@tonic-gateexecsql {COPY tbl1 FROM './testdata1.txt'} 297c478bd9Sstevel@tonic-gatefile delete -force ./testdata1.txt 307c478bd9Sstevel@tonic-gatecatch {unset data} 317c478bd9Sstevel@tonic-gate 327c478bd9Sstevel@tonic-gate# Do a second query inside a first. 337c478bd9Sstevel@tonic-gate# 347c478bd9Sstevel@tonic-gatedo_test select2-1.1 { 357c478bd9Sstevel@tonic-gate set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1} 367c478bd9Sstevel@tonic-gate set r {} 377c478bd9Sstevel@tonic-gate db eval $sql data { 387c478bd9Sstevel@tonic-gate set f1 $data(f1) 397c478bd9Sstevel@tonic-gate lappend r $f1: 407c478bd9Sstevel@tonic-gate set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2" 417c478bd9Sstevel@tonic-gate db eval $sql2 d2 { 427c478bd9Sstevel@tonic-gate lappend r $d2(f2) 437c478bd9Sstevel@tonic-gate } 447c478bd9Sstevel@tonic-gate } 457c478bd9Sstevel@tonic-gate set r 467c478bd9Sstevel@tonic-gate} {0: 0 7 8 9 1: 0 1 8 9 2: 0 1 2 9 3: 0 1 2 3 4: 2 3 4 5: 3 4 5 6: 4 5 6 7: 5 6 7 8: 6 7 8} 477c478bd9Sstevel@tonic-gate 487c478bd9Sstevel@tonic-gatedo_test select2-1.2 { 497c478bd9Sstevel@tonic-gate set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5} 507c478bd9Sstevel@tonic-gate set r {} 517c478bd9Sstevel@tonic-gate db eval $sql data { 527c478bd9Sstevel@tonic-gate set f1 $data(f1) 537c478bd9Sstevel@tonic-gate lappend r $f1: 547c478bd9Sstevel@tonic-gate set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2" 557c478bd9Sstevel@tonic-gate db eval $sql2 d2 { 567c478bd9Sstevel@tonic-gate lappend r $d2(f2) 577c478bd9Sstevel@tonic-gate } 587c478bd9Sstevel@tonic-gate } 597c478bd9Sstevel@tonic-gate set r 607c478bd9Sstevel@tonic-gate} {4: 2 3 4} 617c478bd9Sstevel@tonic-gate 627c478bd9Sstevel@tonic-gate# Create a largish table 637c478bd9Sstevel@tonic-gate# 647c478bd9Sstevel@tonic-gatedo_test select2-2.0 { 657c478bd9Sstevel@tonic-gate execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)} 667c478bd9Sstevel@tonic-gate set f [open ./testdata1.txt w] 677c478bd9Sstevel@tonic-gate for {set i 1} {$i<=30000} {incr i} { 687c478bd9Sstevel@tonic-gate puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]" 697c478bd9Sstevel@tonic-gate } 707c478bd9Sstevel@tonic-gate close $f 717c478bd9Sstevel@tonic-gate # execsql {--vdbe-trace-on--} 727c478bd9Sstevel@tonic-gate execsql {COPY tbl2 FROM './testdata1.txt'} 737c478bd9Sstevel@tonic-gate file delete -force ./testdata1.txt 747c478bd9Sstevel@tonic-gate} {} 757c478bd9Sstevel@tonic-gate 767c478bd9Sstevel@tonic-gatedo_test select2-2.1 { 777c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM tbl2} 787c478bd9Sstevel@tonic-gate} {30000} 797c478bd9Sstevel@tonic-gatedo_test select2-2.2 { 807c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM tbl2 WHERE f2>1000} 817c478bd9Sstevel@tonic-gate} {29500} 827c478bd9Sstevel@tonic-gate 837c478bd9Sstevel@tonic-gatedo_test select2-3.1 { 847c478bd9Sstevel@tonic-gate execsql {SELECT f1 FROM tbl2 WHERE 1000=f2} 857c478bd9Sstevel@tonic-gate} {500} 867c478bd9Sstevel@tonic-gate 877c478bd9Sstevel@tonic-gatedo_test select2-3.2a { 887c478bd9Sstevel@tonic-gate execsql {CREATE INDEX idx1 ON tbl2(f2)} 897c478bd9Sstevel@tonic-gate} {} 907c478bd9Sstevel@tonic-gate 917c478bd9Sstevel@tonic-gatedo_test select2-3.2b { 927c478bd9Sstevel@tonic-gate execsql {SELECT f1 FROM tbl2 WHERE 1000=f2} 937c478bd9Sstevel@tonic-gate} {500} 947c478bd9Sstevel@tonic-gatedo_test select2-3.2c { 957c478bd9Sstevel@tonic-gate execsql {SELECT f1 FROM tbl2 WHERE f2=1000} 967c478bd9Sstevel@tonic-gate} {500} 977c478bd9Sstevel@tonic-gatedo_test select2-3.2d { 987c478bd9Sstevel@tonic-gate set sqlite_search_count 0 997c478bd9Sstevel@tonic-gate execsql {SELECT * FROM tbl2 WHERE 1000=f2} 1007c478bd9Sstevel@tonic-gate set sqlite_search_count 1017c478bd9Sstevel@tonic-gate} {3} 1027c478bd9Sstevel@tonic-gatedo_test select2-3.2e { 1037c478bd9Sstevel@tonic-gate set sqlite_search_count 0 1047c478bd9Sstevel@tonic-gate execsql {SELECT * FROM tbl2 WHERE f2=1000} 1057c478bd9Sstevel@tonic-gate set sqlite_search_count 1067c478bd9Sstevel@tonic-gate} {3} 1077c478bd9Sstevel@tonic-gate 1087c478bd9Sstevel@tonic-gate# Make sure queries run faster with an index than without 1097c478bd9Sstevel@tonic-gate# 1107c478bd9Sstevel@tonic-gatedo_test select2-3.3 { 1117c478bd9Sstevel@tonic-gate execsql {DROP INDEX idx1} 1127c478bd9Sstevel@tonic-gate set sqlite_search_count 0 1137c478bd9Sstevel@tonic-gate execsql {SELECT f1 FROM tbl2 WHERE f2==2000} 1147c478bd9Sstevel@tonic-gate set sqlite_search_count 1157c478bd9Sstevel@tonic-gate} {29999} 1167c478bd9Sstevel@tonic-gate 1177c478bd9Sstevel@tonic-gate# Make sure we can optimize functions in the WHERE clause that 1187c478bd9Sstevel@tonic-gate# use fields from two or more different table. (Bug #6) 1197c478bd9Sstevel@tonic-gate# 1207c478bd9Sstevel@tonic-gatedo_test select2-4.1 { 1217c478bd9Sstevel@tonic-gate execsql { 1227c478bd9Sstevel@tonic-gate CREATE TABLE aa(a); 1237c478bd9Sstevel@tonic-gate CREATE TABLE bb(b); 1247c478bd9Sstevel@tonic-gate INSERT INTO aa VALUES(1); 1257c478bd9Sstevel@tonic-gate INSERT INTO aa VALUES(3); 1267c478bd9Sstevel@tonic-gate INSERT INTO bb VALUES(2); 1277c478bd9Sstevel@tonic-gate INSERT INTO bb VALUES(4); 1287c478bd9Sstevel@tonic-gate SELECT * FROM aa, bb WHERE max(a,b)>2; 1297c478bd9Sstevel@tonic-gate } 1307c478bd9Sstevel@tonic-gate} {1 4 3 2 3 4} 1317c478bd9Sstevel@tonic-gatedo_test select2-4.2 { 1327c478bd9Sstevel@tonic-gate execsql { 1337c478bd9Sstevel@tonic-gate INSERT INTO bb VALUES(0); 1347c478bd9Sstevel@tonic-gate SELECT * FROM aa, bb WHERE b; 1357c478bd9Sstevel@tonic-gate } 1367c478bd9Sstevel@tonic-gate} {1 2 1 4 3 2 3 4} 1377c478bd9Sstevel@tonic-gatedo_test select2-4.3 { 1387c478bd9Sstevel@tonic-gate execsql { 1397c478bd9Sstevel@tonic-gate SELECT * FROM aa, bb WHERE NOT b; 1407c478bd9Sstevel@tonic-gate } 1417c478bd9Sstevel@tonic-gate} {1 0 3 0} 1427c478bd9Sstevel@tonic-gatedo_test select2-4.4 { 1437c478bd9Sstevel@tonic-gate execsql { 1447c478bd9Sstevel@tonic-gate SELECT * FROM aa, bb WHERE min(a,b); 1457c478bd9Sstevel@tonic-gate } 1467c478bd9Sstevel@tonic-gate} {1 2 1 4 3 2 3 4} 1477c478bd9Sstevel@tonic-gatedo_test select2-4.5 { 1487c478bd9Sstevel@tonic-gate execsql { 1497c478bd9Sstevel@tonic-gate SELECT * FROM aa, bb WHERE NOT min(a,b); 1507c478bd9Sstevel@tonic-gate } 1517c478bd9Sstevel@tonic-gate} {1 0 3 0} 1527c478bd9Sstevel@tonic-gatedo_test select2-4.6 { 1537c478bd9Sstevel@tonic-gate execsql { 1547c478bd9Sstevel@tonic-gate SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END; 1557c478bd9Sstevel@tonic-gate } 1567c478bd9Sstevel@tonic-gate} {1 2 3 4} 1577c478bd9Sstevel@tonic-gatedo_test select2-4.7 { 1587c478bd9Sstevel@tonic-gate execsql { 1597c478bd9Sstevel@tonic-gate SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END; 1607c478bd9Sstevel@tonic-gate } 1617c478bd9Sstevel@tonic-gate} {1 4 1 0 3 2 3 0} 1627c478bd9Sstevel@tonic-gate 1637c478bd9Sstevel@tonic-gatefinish_test 164