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 the library is able to correctly 147c478bd9Sstevel@tonic-gate# handle file-format 3 (version 2.6.x) databases. 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: format3.test,v 1.4 2003/12/23 02:17:35 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gate# Create a bunch of data to sort against 227c478bd9Sstevel@tonic-gate# 237c478bd9Sstevel@tonic-gatedo_test format3-1.0 { 247c478bd9Sstevel@tonic-gate set fd [open data.txt w] 257c478bd9Sstevel@tonic-gate puts $fd "1\tone\t0\tI\t3.141592653" 267c478bd9Sstevel@tonic-gate puts $fd "2\ttwo\t1\tII\t2.15" 277c478bd9Sstevel@tonic-gate puts $fd "3\tthree\t1\tIII\t4221.0" 287c478bd9Sstevel@tonic-gate puts $fd "4\tfour\t2\tIV\t-0.0013442" 297c478bd9Sstevel@tonic-gate puts $fd "5\tfive\t2\tV\t-11" 307c478bd9Sstevel@tonic-gate puts $fd "6\tsix\t2\tVI\t0.123" 317c478bd9Sstevel@tonic-gate puts $fd "7\tseven\t2\tVII\t123.0" 327c478bd9Sstevel@tonic-gate puts $fd "8\teight\t3\tVIII\t-1.6" 337c478bd9Sstevel@tonic-gate close $fd 347c478bd9Sstevel@tonic-gate execsql { 357c478bd9Sstevel@tonic-gate CREATE TABLE t1( 367c478bd9Sstevel@tonic-gate n int, 377c478bd9Sstevel@tonic-gate v varchar(10), 387c478bd9Sstevel@tonic-gate log int, 397c478bd9Sstevel@tonic-gate roman varchar(10), 407c478bd9Sstevel@tonic-gate flt real 417c478bd9Sstevel@tonic-gate ); 427c478bd9Sstevel@tonic-gate COPY t1 FROM 'data.txt' 437c478bd9Sstevel@tonic-gate } 447c478bd9Sstevel@tonic-gate file delete data.txt 457c478bd9Sstevel@tonic-gate db close 467c478bd9Sstevel@tonic-gate set ::bt [btree_open test.db] 477c478bd9Sstevel@tonic-gate btree_begin_transaction $::bt 487c478bd9Sstevel@tonic-gate set m [btree_get_meta $::bt] 497c478bd9Sstevel@tonic-gate set m [lreplace $m 2 2 3] 507c478bd9Sstevel@tonic-gate eval btree_update_meta $::bt $m 517c478bd9Sstevel@tonic-gate btree_commit $::bt 527c478bd9Sstevel@tonic-gate btree_close $::bt 537c478bd9Sstevel@tonic-gate sqlite db test.db 547c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM t1} 557c478bd9Sstevel@tonic-gate} {8} 567c478bd9Sstevel@tonic-gate 577c478bd9Sstevel@tonic-gatedo_test format3-1.1 { 587c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY n} 597c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 607c478bd9Sstevel@tonic-gatedo_test format3-1.1.1 { 617c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY n ASC} 627c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 637c478bd9Sstevel@tonic-gatedo_test format3-1.1.1 { 647c478bd9Sstevel@tonic-gate execsql {SELECT ALL n FROM t1 ORDER BY n ASC} 657c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 667c478bd9Sstevel@tonic-gatedo_test format3-1.2 { 677c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY n DESC} 687c478bd9Sstevel@tonic-gate} {8 7 6 5 4 3 2 1} 697c478bd9Sstevel@tonic-gatedo_test format3-1.3a { 707c478bd9Sstevel@tonic-gate execsql {SELECT v FROM t1 ORDER BY v} 717c478bd9Sstevel@tonic-gate} {eight five four one seven six three two} 727c478bd9Sstevel@tonic-gatedo_test format3-1.3b { 737c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY v} 747c478bd9Sstevel@tonic-gate} {8 5 4 1 7 6 3 2} 757c478bd9Sstevel@tonic-gatedo_test format3-1.4 { 767c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY v DESC} 777c478bd9Sstevel@tonic-gate} {2 3 6 7 1 4 5 8} 787c478bd9Sstevel@tonic-gatedo_test format3-1.5 { 797c478bd9Sstevel@tonic-gate execsql {SELECT flt FROM t1 ORDER BY flt} 807c478bd9Sstevel@tonic-gate} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0} 817c478bd9Sstevel@tonic-gatedo_test format3-1.6 { 827c478bd9Sstevel@tonic-gate execsql {SELECT flt FROM t1 ORDER BY flt DESC} 837c478bd9Sstevel@tonic-gate} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11} 847c478bd9Sstevel@tonic-gatedo_test format3-1.7 { 857c478bd9Sstevel@tonic-gate execsql {SELECT roman FROM t1 ORDER BY roman} 867c478bd9Sstevel@tonic-gate} {I II III IV V VI VII VIII} 877c478bd9Sstevel@tonic-gatedo_test format3-1.8 { 887c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log, flt} 897c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 907c478bd9Sstevel@tonic-gatedo_test format3-1.8.1 { 917c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log asc, flt} 927c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 937c478bd9Sstevel@tonic-gatedo_test format3-1.8.2 { 947c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log, flt ASC} 957c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 967c478bd9Sstevel@tonic-gatedo_test format3-1.8.3 { 977c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc} 987c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 997c478bd9Sstevel@tonic-gatedo_test format3-1.9 { 1007c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log, flt DESC} 1017c478bd9Sstevel@tonic-gate} {1 3 2 7 6 4 5 8} 1027c478bd9Sstevel@tonic-gatedo_test format3-1.9.1 { 1037c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC} 1047c478bd9Sstevel@tonic-gate} {1 3 2 7 6 4 5 8} 1057c478bd9Sstevel@tonic-gatedo_test format3-1.10 { 1067c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log DESC, flt} 1077c478bd9Sstevel@tonic-gate} {8 5 4 6 7 2 3 1} 1087c478bd9Sstevel@tonic-gatedo_test format3-1.11 { 1097c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC} 1107c478bd9Sstevel@tonic-gate} {8 7 6 4 5 3 2 1} 1117c478bd9Sstevel@tonic-gate 1127c478bd9Sstevel@tonic-gate# These tests are designed to reach some hard-to-reach places 1137c478bd9Sstevel@tonic-gate# inside the string comparison routines. 1147c478bd9Sstevel@tonic-gate# 1157c478bd9Sstevel@tonic-gate# (Later) The sorting behavior changed in 2.7.0. But we will 1167c478bd9Sstevel@tonic-gate# keep these tests. You can never have too many test cases! 1177c478bd9Sstevel@tonic-gate# 1187c478bd9Sstevel@tonic-gatedo_test format3-2.1.1 { 1197c478bd9Sstevel@tonic-gate execsql { 1207c478bd9Sstevel@tonic-gate UPDATE t1 SET v='x' || -flt; 1217c478bd9Sstevel@tonic-gate UPDATE t1 SET v='x-2b' where v=='x-0.123'; 1227c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY v; 1237c478bd9Sstevel@tonic-gate } 1247c478bd9Sstevel@tonic-gate} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11} 1257c478bd9Sstevel@tonic-gatedo_test format3-2.1.2 { 1267c478bd9Sstevel@tonic-gate execsql { 1277c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999); 1287c478bd9Sstevel@tonic-gate } 1297c478bd9Sstevel@tonic-gate} {x-4221 x-123 x-3.141592653 x-2.15 x0.0013442 x1.6 x11 x-2b} 1307c478bd9Sstevel@tonic-gatedo_test format3-2.1.3 { 1317c478bd9Sstevel@tonic-gate execsql { 1327c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0; 1337c478bd9Sstevel@tonic-gate } 1347c478bd9Sstevel@tonic-gate} {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11} 1357c478bd9Sstevel@tonic-gatedo_test format3-2.1.4 { 1367c478bd9Sstevel@tonic-gate execsql { 1377c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999) DESC; 1387c478bd9Sstevel@tonic-gate } 1397c478bd9Sstevel@tonic-gate} {x-2b x11 x1.6 x0.0013442 x-2.15 x-3.141592653 x-123 x-4221} 1407c478bd9Sstevel@tonic-gatedo_test format3-2.1.5 { 1417c478bd9Sstevel@tonic-gate execsql { 1427c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC; 1437c478bd9Sstevel@tonic-gate } 1447c478bd9Sstevel@tonic-gate} {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221} 1457c478bd9Sstevel@tonic-gate 1467c478bd9Sstevel@tonic-gate# This is a bug fix for 2.2.4. 1477c478bd9Sstevel@tonic-gate# Strings are normally mapped to upper-case for a caseless comparison. 1487c478bd9Sstevel@tonic-gate# But this can cause problems for characters in between 'Z' and 'a'. 1497c478bd9Sstevel@tonic-gate# 1507c478bd9Sstevel@tonic-gatedo_test format3-3.1 { 1517c478bd9Sstevel@tonic-gate execsql { 1527c478bd9Sstevel@tonic-gate CREATE TABLE t2(a,b); 1537c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('AGLIENTU',1); 1547c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('AGLIE`',2); 1557c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('AGNA',3); 1567c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a; 1577c478bd9Sstevel@tonic-gate } 1587c478bd9Sstevel@tonic-gate} {AGLIENTU 1 AGLIE` 2 AGNA 3} 1597c478bd9Sstevel@tonic-gatedo_test format3-3.2 { 1607c478bd9Sstevel@tonic-gate execsql { 1617c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a DESC; 1627c478bd9Sstevel@tonic-gate } 1637c478bd9Sstevel@tonic-gate} {AGNA 3 AGLIE` 2 AGLIENTU 1} 1647c478bd9Sstevel@tonic-gatedo_test format3-3.3 { 1657c478bd9Sstevel@tonic-gate execsql { 1667c478bd9Sstevel@tonic-gate DELETE FROM t2; 1677c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('aglientu',1); 1687c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('aglie`',2); 1697c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('agna',3); 1707c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a; 1717c478bd9Sstevel@tonic-gate } 1727c478bd9Sstevel@tonic-gate} {aglie` 2 aglientu 1 agna 3} 1737c478bd9Sstevel@tonic-gatedo_test format3-3.4 { 1747c478bd9Sstevel@tonic-gate execsql { 1757c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a DESC; 1767c478bd9Sstevel@tonic-gate } 1777c478bd9Sstevel@tonic-gate} {agna 3 aglientu 1 aglie` 2} 1787c478bd9Sstevel@tonic-gate 1797c478bd9Sstevel@tonic-gate# Version 2.7.0 testing. 1807c478bd9Sstevel@tonic-gate# 1817c478bd9Sstevel@tonic-gatedo_test format3-4.1 { 1827c478bd9Sstevel@tonic-gate execsql { 1837c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5); 1847c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5); 1857c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4); 1867c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3); 1877c478bd9Sstevel@tonic-gate SELECT n FROM t1 ORDER BY n; 1887c478bd9Sstevel@tonic-gate } 1897c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9 10 11 12} 1907c478bd9Sstevel@tonic-gatedo_test format3-4.2 { 1917c478bd9Sstevel@tonic-gate execsql { 1927c478bd9Sstevel@tonic-gate SELECT n||'' FROM t1 ORDER BY 1; 1937c478bd9Sstevel@tonic-gate } 1947c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9 10 11 12} 1957c478bd9Sstevel@tonic-gatedo_test format3-4.3 { 1967c478bd9Sstevel@tonic-gate execsql { 1977c478bd9Sstevel@tonic-gate SELECT n+0 FROM t1 ORDER BY 1; 1987c478bd9Sstevel@tonic-gate } 1997c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9 10 11 12} 2007c478bd9Sstevel@tonic-gatedo_test format3-4.4 { 2017c478bd9Sstevel@tonic-gate execsql { 2027c478bd9Sstevel@tonic-gate SELECT n||'' FROM t1 ORDER BY 1 DESC; 2037c478bd9Sstevel@tonic-gate } 2047c478bd9Sstevel@tonic-gate} {12 11 10 9 8 7 6 5 4 3 2 1} 2057c478bd9Sstevel@tonic-gatedo_test format3-4.5 { 2067c478bd9Sstevel@tonic-gate execsql { 2077c478bd9Sstevel@tonic-gate SELECT n+0 FROM t1 ORDER BY 1 DESC; 2087c478bd9Sstevel@tonic-gate } 2097c478bd9Sstevel@tonic-gate} {12 11 10 9 8 7 6 5 4 3 2 1} 2107c478bd9Sstevel@tonic-gatedo_test format3-4.6 { 2117c478bd9Sstevel@tonic-gate execsql { 2127c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY 1; 2137c478bd9Sstevel@tonic-gate } 2147c478bd9Sstevel@tonic-gate} {x-123 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221 x0.0013442 x01234567890123456789 x1.6 x11 x2.7 x5.0e10} 2157c478bd9Sstevel@tonic-gatedo_test format3-4.7 { 2167c478bd9Sstevel@tonic-gate execsql { 2177c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY 1 DESC; 2187c478bd9Sstevel@tonic-gate } 2197c478bd9Sstevel@tonic-gate} {x5.0e10 x2.7 x11 x1.6 x01234567890123456789 x0.0013442 x-4221 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123} 2207c478bd9Sstevel@tonic-gatedo_test format3-4.8 { 2217c478bd9Sstevel@tonic-gate execsql { 2227c478bd9Sstevel@tonic-gate SELECT substr(v,2,99) FROM t1 ORDER BY 1; 2237c478bd9Sstevel@tonic-gate } 2247c478bd9Sstevel@tonic-gate} {-4.0e9 -4221 -123 -3.141592653 -2.15 0.0013442 1.6 2.7 11 5.0e10 01234567890123456789 -2b} 2257c478bd9Sstevel@tonic-gate 2267c478bd9Sstevel@tonic-gate# Build some new test data, this time with indices. 2277c478bd9Sstevel@tonic-gate# 2287c478bd9Sstevel@tonic-gatedo_test format3-5.0 { 2297c478bd9Sstevel@tonic-gate execsql { 2307c478bd9Sstevel@tonic-gate DROP TABLE t1; 2317c478bd9Sstevel@tonic-gate CREATE TABLE t1(w int, x text, y blob); 2327c478bd9Sstevel@tonic-gate DROP TABLE t2; 2337c478bd9Sstevel@tonic-gate CREATE TABLE t2(p varchar(1), q clob, r real, s numeric(8)); 2347c478bd9Sstevel@tonic-gate } 2357c478bd9Sstevel@tonic-gate for {set i 1} {$i<=100} {incr i} { 2367c478bd9Sstevel@tonic-gate set w $i 2377c478bd9Sstevel@tonic-gate set x [expr {int(log($i)/log(2))}] 2387c478bd9Sstevel@tonic-gate set y [expr {$i*$i + 2*$i + 1}] 2397c478bd9Sstevel@tonic-gate execsql "INSERT INTO t1 VALUES($w,$x,$y)" 2407c478bd9Sstevel@tonic-gate } 2417c478bd9Sstevel@tonic-gate execsql { 2427c478bd9Sstevel@tonic-gate INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1; 2437c478bd9Sstevel@tonic-gate CREATE INDEX i1w ON t1(w); 2447c478bd9Sstevel@tonic-gate CREATE INDEX i1xy ON t1(x,y); 2457c478bd9Sstevel@tonic-gate CREATE INDEX i2p ON t2(p); 2467c478bd9Sstevel@tonic-gate CREATE INDEX i2r ON t2(r); 2477c478bd9Sstevel@tonic-gate CREATE INDEX i2qs ON t2(q, s); 2487c478bd9Sstevel@tonic-gate } 2497c478bd9Sstevel@tonic-gate} {} 2507c478bd9Sstevel@tonic-gate 2517c478bd9Sstevel@tonic-gate# Do an SQL statement. Append the search count to the end of the result. 2527c478bd9Sstevel@tonic-gate# 2537c478bd9Sstevel@tonic-gateproc count sql { 2547c478bd9Sstevel@tonic-gate set ::sqlite_search_count 0 2557c478bd9Sstevel@tonic-gate return [concat [execsql $sql] $::sqlite_search_count] 2567c478bd9Sstevel@tonic-gate} 2577c478bd9Sstevel@tonic-gate 2587c478bd9Sstevel@tonic-gate# Verify that queries use an index. We are using the special variable 2597c478bd9Sstevel@tonic-gate# "sqlite_search_count" which tallys the number of executions of MoveTo 2607c478bd9Sstevel@tonic-gate# and Next operators in the VDBE. By verifing that the search count is 2617c478bd9Sstevel@tonic-gate# small we can be assured that indices are being used properly. 2627c478bd9Sstevel@tonic-gate# 2637c478bd9Sstevel@tonic-gatedo_test format3-5.1 { 2647c478bd9Sstevel@tonic-gate db close 2657c478bd9Sstevel@tonic-gate sqlite db test.db 2667c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE w=10} 2677c478bd9Sstevel@tonic-gate} {3 121 3} 2687c478bd9Sstevel@tonic-gatedo_test format3-5.2 { 2697c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE w=11} 2707c478bd9Sstevel@tonic-gate} {3 144 3} 2717c478bd9Sstevel@tonic-gatedo_test format3-5.3 { 2727c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE 11=w} 2737c478bd9Sstevel@tonic-gate} {3 144 3} 2747c478bd9Sstevel@tonic-gatedo_test format3-5.4 { 2757c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE 11=w AND x>2} 2767c478bd9Sstevel@tonic-gate} {3 144 3} 2777c478bd9Sstevel@tonic-gatedo_test format3-5.5 { 2787c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} 2797c478bd9Sstevel@tonic-gate} {3 144 3} 2807c478bd9Sstevel@tonic-gatedo_test format3-5.6 { 2817c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} 2827c478bd9Sstevel@tonic-gate} {3 144 3} 2837c478bd9Sstevel@tonic-gatedo_test format3-5.7 { 2847c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} 2857c478bd9Sstevel@tonic-gate} {3 144 3} 2867c478bd9Sstevel@tonic-gatedo_test format3-5.8 { 2877c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} 2887c478bd9Sstevel@tonic-gate} {3 144 3} 2897c478bd9Sstevel@tonic-gatedo_test format3-5.9 { 2907c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} 2917c478bd9Sstevel@tonic-gate} {3 144 3} 2927c478bd9Sstevel@tonic-gatedo_test format3-5.10 { 2937c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} 2947c478bd9Sstevel@tonic-gate} {3 121 3} 2957c478bd9Sstevel@tonic-gatedo_test format3-5.11 { 2967c478bd9Sstevel@tonic-gate count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} 2977c478bd9Sstevel@tonic-gate} {3 100 3} 2987c478bd9Sstevel@tonic-gate 2997c478bd9Sstevel@tonic-gate# New for SQLite version 2.1: Verify that that inequality constraints 3007c478bd9Sstevel@tonic-gate# are used correctly. 3017c478bd9Sstevel@tonic-gate# 3027c478bd9Sstevel@tonic-gatedo_test format3-5.12 { 3037c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND y<100} 3047c478bd9Sstevel@tonic-gate} {8 3} 3057c478bd9Sstevel@tonic-gatedo_test format3-5.13 { 3067c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND 100>y} 3077c478bd9Sstevel@tonic-gate} {8 3} 3087c478bd9Sstevel@tonic-gatedo_test format3-5.14 { 3097c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE 3=x AND y<100} 3107c478bd9Sstevel@tonic-gate} {8 3} 3117c478bd9Sstevel@tonic-gatedo_test format3-5.15 { 3127c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE 3=x AND 100>y} 3137c478bd9Sstevel@tonic-gate} {8 3} 3147c478bd9Sstevel@tonic-gatedo_test format3-5.16 { 3157c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND y<=100} 3167c478bd9Sstevel@tonic-gate} {8 9 5} 3177c478bd9Sstevel@tonic-gatedo_test format3-5.17 { 3187c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND 100>=y} 3197c478bd9Sstevel@tonic-gate} {8 9 5} 3207c478bd9Sstevel@tonic-gatedo_test format3-5.18 { 3217c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND y>225} 3227c478bd9Sstevel@tonic-gate} {15 3} 3237c478bd9Sstevel@tonic-gatedo_test format3-5.19 { 3247c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND 225<y} 3257c478bd9Sstevel@tonic-gate} {15 3} 3267c478bd9Sstevel@tonic-gatedo_test format3-5.20 { 3277c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND y>=225} 3287c478bd9Sstevel@tonic-gate} {14 15 5} 3297c478bd9Sstevel@tonic-gatedo_test format3-5.21 { 3307c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND 225<=y} 3317c478bd9Sstevel@tonic-gate} {14 15 5} 3327c478bd9Sstevel@tonic-gatedo_test format3-5.22 { 3337c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} 3347c478bd9Sstevel@tonic-gate} {11 12 5} 3357c478bd9Sstevel@tonic-gatedo_test format3-5.23 { 3367c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} 3377c478bd9Sstevel@tonic-gate} {10 11 12 13 9} 3387c478bd9Sstevel@tonic-gatedo_test format3-5.24 { 3397c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y} 3407c478bd9Sstevel@tonic-gate} {11 12 5} 3417c478bd9Sstevel@tonic-gatedo_test format3-5.25 { 3427c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y} 3437c478bd9Sstevel@tonic-gate} {10 11 12 13 9} 3447c478bd9Sstevel@tonic-gate 345*1da57d55SToomas Soome# Need to work on optimizing the BETWEEN operator. 3467c478bd9Sstevel@tonic-gate# 3477c478bd9Sstevel@tonic-gate# do_test format3-5.26 { 3487c478bd9Sstevel@tonic-gate# count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196} 3497c478bd9Sstevel@tonic-gate# } {10 11 12 13 9} 3507c478bd9Sstevel@tonic-gate 3517c478bd9Sstevel@tonic-gatedo_test format3-5.27 { 3527c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x=3 AND y+1==122} 3537c478bd9Sstevel@tonic-gate} {10 17} 3547c478bd9Sstevel@tonic-gatedo_test format3-5.28 { 3557c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122} 3567c478bd9Sstevel@tonic-gate} {10 99} 3577c478bd9Sstevel@tonic-gatedo_test format3-5.29 { 3587c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE y==121} 3597c478bd9Sstevel@tonic-gate} {10 99} 3607c478bd9Sstevel@tonic-gate 3617c478bd9Sstevel@tonic-gate 3627c478bd9Sstevel@tonic-gatedo_test format3-5.30 { 3637c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE w>97} 3647c478bd9Sstevel@tonic-gate} {98 99 100 6} 3657c478bd9Sstevel@tonic-gatedo_test format3-5.31 { 3667c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE w>=97} 3677c478bd9Sstevel@tonic-gate} {97 98 99 100 8} 3687c478bd9Sstevel@tonic-gatedo_test format3-5.33 { 3697c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE w==97} 3707c478bd9Sstevel@tonic-gate} {97 3} 3717c478bd9Sstevel@tonic-gatedo_test format3-5.34 { 3727c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE w+1==98} 3737c478bd9Sstevel@tonic-gate} {97 99} 3747c478bd9Sstevel@tonic-gatedo_test format3-5.35 { 3757c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE w<3} 3767c478bd9Sstevel@tonic-gate} {1 2 4} 3777c478bd9Sstevel@tonic-gatedo_test format3-5.36 { 3787c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE w<=3} 3797c478bd9Sstevel@tonic-gate} {1 2 3 6} 3807c478bd9Sstevel@tonic-gatedo_test format3-5.37 { 3817c478bd9Sstevel@tonic-gate count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w} 3827c478bd9Sstevel@tonic-gate} {1 2 3 199} 3837c478bd9Sstevel@tonic-gate 3847c478bd9Sstevel@tonic-gate 3857c478bd9Sstevel@tonic-gate# Do the same kind of thing except use a join as the data source. 3867c478bd9Sstevel@tonic-gate# 3877c478bd9Sstevel@tonic-gatedo_test format3-6.1 { 3887c478bd9Sstevel@tonic-gate db close 3897c478bd9Sstevel@tonic-gate sqlite db test.db 3907c478bd9Sstevel@tonic-gate count { 3917c478bd9Sstevel@tonic-gate SELECT w, p FROM t2, t1 3927c478bd9Sstevel@tonic-gate WHERE x=q AND y=s AND r=8977 3937c478bd9Sstevel@tonic-gate } 3947c478bd9Sstevel@tonic-gate} {34 67 6} 3957c478bd9Sstevel@tonic-gatedo_test format3-6.2 { 3967c478bd9Sstevel@tonic-gate count { 3977c478bd9Sstevel@tonic-gate SELECT w, p FROM t2, t1 3987c478bd9Sstevel@tonic-gate WHERE x=q AND s=y AND r=8977 3997c478bd9Sstevel@tonic-gate } 4007c478bd9Sstevel@tonic-gate} {34 67 6} 4017c478bd9Sstevel@tonic-gatedo_test format3-6.3 { 4027c478bd9Sstevel@tonic-gate count { 4037c478bd9Sstevel@tonic-gate SELECT w, p FROM t2, t1 4047c478bd9Sstevel@tonic-gate WHERE x=q AND s=y AND r=8977 AND w>10 4057c478bd9Sstevel@tonic-gate } 4067c478bd9Sstevel@tonic-gate} {34 67 6} 4077c478bd9Sstevel@tonic-gatedo_test format3-6.4 { 4087c478bd9Sstevel@tonic-gate count { 4097c478bd9Sstevel@tonic-gate SELECT w, p FROM t2, t1 4107c478bd9Sstevel@tonic-gate WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10 4117c478bd9Sstevel@tonic-gate } 4127c478bd9Sstevel@tonic-gate} {34 67 6} 4137c478bd9Sstevel@tonic-gatedo_test format3-6.5 { 4147c478bd9Sstevel@tonic-gate count { 4157c478bd9Sstevel@tonic-gate SELECT w, p FROM t2, t1 4167c478bd9Sstevel@tonic-gate WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10 4177c478bd9Sstevel@tonic-gate } 4187c478bd9Sstevel@tonic-gate} {34 67 6} 4197c478bd9Sstevel@tonic-gatedo_test format3-6.6 { 4207c478bd9Sstevel@tonic-gate count { 4217c478bd9Sstevel@tonic-gate SELECT w, p FROM t2, t1 4227c478bd9Sstevel@tonic-gate WHERE x=q AND p=77 AND s=y AND w>5 4237c478bd9Sstevel@tonic-gate } 4247c478bd9Sstevel@tonic-gate} {24 77 6} 4257c478bd9Sstevel@tonic-gatedo_test format3-6.7 { 4267c478bd9Sstevel@tonic-gate count { 4277c478bd9Sstevel@tonic-gate SELECT w, p FROM t1, t2 4287c478bd9Sstevel@tonic-gate WHERE x=q AND p>77 AND s=y AND w=5 4297c478bd9Sstevel@tonic-gate } 4307c478bd9Sstevel@tonic-gate} {5 96 6} 4317c478bd9Sstevel@tonic-gate 4327c478bd9Sstevel@tonic-gate# Lets do a 3-way join. 4337c478bd9Sstevel@tonic-gate# 4347c478bd9Sstevel@tonic-gatedo_test format3-7.1 { 4357c478bd9Sstevel@tonic-gate count { 4367c478bd9Sstevel@tonic-gate SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 4377c478bd9Sstevel@tonic-gate WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11 4387c478bd9Sstevel@tonic-gate } 4397c478bd9Sstevel@tonic-gate} {11 90 11 9} 4407c478bd9Sstevel@tonic-gatedo_test format3-7.2 { 4417c478bd9Sstevel@tonic-gate count { 4427c478bd9Sstevel@tonic-gate SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 4437c478bd9Sstevel@tonic-gate WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12 4447c478bd9Sstevel@tonic-gate } 4457c478bd9Sstevel@tonic-gate} {12 89 12 9} 4467c478bd9Sstevel@tonic-gatedo_test format3-7.3 { 4477c478bd9Sstevel@tonic-gate count { 4487c478bd9Sstevel@tonic-gate SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 4497c478bd9Sstevel@tonic-gate WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y 4507c478bd9Sstevel@tonic-gate } 4517c478bd9Sstevel@tonic-gate} {15 86 86 9} 4527c478bd9Sstevel@tonic-gate 4537c478bd9Sstevel@tonic-gate# Test to see that the special case of a constant WHERE clause is 4547c478bd9Sstevel@tonic-gate# handled. 4557c478bd9Sstevel@tonic-gate# 4567c478bd9Sstevel@tonic-gatedo_test format3-8.1 { 4577c478bd9Sstevel@tonic-gate count { 4587c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE 0 4597c478bd9Sstevel@tonic-gate } 4607c478bd9Sstevel@tonic-gate} {0} 4617c478bd9Sstevel@tonic-gatedo_test format3-8.2 { 4627c478bd9Sstevel@tonic-gate count { 4637c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE 1 LIMIT 1 4647c478bd9Sstevel@tonic-gate } 4657c478bd9Sstevel@tonic-gate} {1 0 4 1} 4667c478bd9Sstevel@tonic-gatedo_test format3-8.3 { 4677c478bd9Sstevel@tonic-gate execsql { 4687c478bd9Sstevel@tonic-gate SELECT 99 WHERE 0 4697c478bd9Sstevel@tonic-gate } 4707c478bd9Sstevel@tonic-gate} {} 4717c478bd9Sstevel@tonic-gatedo_test format3-8.4 { 4727c478bd9Sstevel@tonic-gate execsql { 4737c478bd9Sstevel@tonic-gate SELECT 99 WHERE 1 4747c478bd9Sstevel@tonic-gate } 4757c478bd9Sstevel@tonic-gate} {99} 4767c478bd9Sstevel@tonic-gate 4777c478bd9Sstevel@tonic-gate# Verify that IN operators in a WHERE clause are handled correctly. 4787c478bd9Sstevel@tonic-gate# 4797c478bd9Sstevel@tonic-gatedo_test format3-9.1 { 4807c478bd9Sstevel@tonic-gate count { 4817c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1; 4827c478bd9Sstevel@tonic-gate } 4837c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 0} 4847c478bd9Sstevel@tonic-gatedo_test format3-9.2 { 4857c478bd9Sstevel@tonic-gate count { 4867c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; 4877c478bd9Sstevel@tonic-gate } 4887c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 199} 4897c478bd9Sstevel@tonic-gatedo_test format3-9.3 { 4907c478bd9Sstevel@tonic-gate count { 4917c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; 4927c478bd9Sstevel@tonic-gate } 4937c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 10} 4947c478bd9Sstevel@tonic-gatedo_test format3-9.4 { 4957c478bd9Sstevel@tonic-gate count { 4967c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; 4977c478bd9Sstevel@tonic-gate } 4987c478bd9Sstevel@tonic-gate} {1 0 4 2 1 9 3 1 16 199} 4997c478bd9Sstevel@tonic-gatedo_test format3-9.5 { 5007c478bd9Sstevel@tonic-gate count { 501*1da57d55SToomas Soome SELECT * FROM t1 WHERE rowid IN 5027c478bd9Sstevel@tonic-gate (select rowid from t1 where rowid IN (-1,2,4)) 5037c478bd9Sstevel@tonic-gate ORDER BY 1; 5047c478bd9Sstevel@tonic-gate } 5057c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 1} 5067c478bd9Sstevel@tonic-gatedo_test format3-9.6 { 5077c478bd9Sstevel@tonic-gate count { 508*1da57d55SToomas Soome SELECT * FROM t1 WHERE rowid+0 IN 5097c478bd9Sstevel@tonic-gate (select rowid from t1 where rowid IN (-1,2,4)) 5107c478bd9Sstevel@tonic-gate ORDER BY 1; 5117c478bd9Sstevel@tonic-gate } 5127c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 199} 5137c478bd9Sstevel@tonic-gatedo_test format3-9.7 { 5147c478bd9Sstevel@tonic-gate count { 515*1da57d55SToomas Soome SELECT * FROM t1 WHERE w IN 5167c478bd9Sstevel@tonic-gate (select rowid from t1 where rowid IN (-1,2,4)) 5177c478bd9Sstevel@tonic-gate ORDER BY 1; 5187c478bd9Sstevel@tonic-gate } 5197c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 7} 5207c478bd9Sstevel@tonic-gatedo_test format3-9.8 { 5217c478bd9Sstevel@tonic-gate count { 522*1da57d55SToomas Soome SELECT * FROM t1 WHERE w+0 IN 5237c478bd9Sstevel@tonic-gate (select rowid from t1 where rowid IN (-1,2,4)) 5247c478bd9Sstevel@tonic-gate ORDER BY 1; 5257c478bd9Sstevel@tonic-gate } 5267c478bd9Sstevel@tonic-gate} {2 1 9 4 2 25 199} 5277c478bd9Sstevel@tonic-gatedo_test format3-9.9 { 5287c478bd9Sstevel@tonic-gate count { 5297c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; 5307c478bd9Sstevel@tonic-gate } 5317c478bd9Sstevel@tonic-gate} {2 1 9 3 1 16 6} 5327c478bd9Sstevel@tonic-gatedo_test format3-9.10 { 5337c478bd9Sstevel@tonic-gate count { 5347c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; 5357c478bd9Sstevel@tonic-gate } 5367c478bd9Sstevel@tonic-gate} {2 1 9 3 1 16 199} 5377c478bd9Sstevel@tonic-gatedo_test format3-9.11 { 5387c478bd9Sstevel@tonic-gate count { 5397c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; 5407c478bd9Sstevel@tonic-gate } 5417c478bd9Sstevel@tonic-gate} {79 6 6400 89 6 8100 199} 5427c478bd9Sstevel@tonic-gatedo_test format3-9.12 { 5437c478bd9Sstevel@tonic-gate count { 5447c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; 5457c478bd9Sstevel@tonic-gate } 5467c478bd9Sstevel@tonic-gate} {79 6 6400 89 6 8100 74} 5477c478bd9Sstevel@tonic-gatedo_test format3-9.13 { 5487c478bd9Sstevel@tonic-gate count { 5497c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; 5507c478bd9Sstevel@tonic-gate } 5517c478bd9Sstevel@tonic-gate} {2 1 9 3 1 16 6} 5527c478bd9Sstevel@tonic-gatedo_test format3-9.14 { 5537c478bd9Sstevel@tonic-gate count { 5547c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; 5557c478bd9Sstevel@tonic-gate } 5567c478bd9Sstevel@tonic-gate} {2 1 9 6} 5577c478bd9Sstevel@tonic-gate 5587c478bd9Sstevel@tonic-gate# This procedure executes the SQL. Then it checks the generated program 5597c478bd9Sstevel@tonic-gate# for the SQL and appends a "nosort" to the result if the program contains the 5607c478bd9Sstevel@tonic-gate# SortCallback opcode. If the program does not contain the SortCallback 5617c478bd9Sstevel@tonic-gate# opcode it appends "sort" 5627c478bd9Sstevel@tonic-gate# 5637c478bd9Sstevel@tonic-gateproc cksort {sql} { 5647c478bd9Sstevel@tonic-gate set data [execsql $sql] 5657c478bd9Sstevel@tonic-gate set prog [execsql "EXPLAIN $sql"] 5667c478bd9Sstevel@tonic-gate if {[regexp SortCallback $prog]} {set x sort} {set x nosort} 5677c478bd9Sstevel@tonic-gate lappend data $x 5687c478bd9Sstevel@tonic-gate return $data 5697c478bd9Sstevel@tonic-gate} 5707c478bd9Sstevel@tonic-gate# Check out the logic that attempts to implement the ORDER BY clause 5717c478bd9Sstevel@tonic-gate# using an index rather than by sorting. 5727c478bd9Sstevel@tonic-gate# 5737c478bd9Sstevel@tonic-gatedo_test format3-10.1 { 5747c478bd9Sstevel@tonic-gate execsql { 5757c478bd9Sstevel@tonic-gate CREATE TABLE t3(a,b,c); 5767c478bd9Sstevel@tonic-gate CREATE INDEX t3a ON t3(a); 5777c478bd9Sstevel@tonic-gate CREATE INDEX t3bc ON t3(b,c); 5787c478bd9Sstevel@tonic-gate CREATE INDEX t3acb ON t3(a,c,b); 5797c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT w, 101-w, y FROM t1; 5807c478bd9Sstevel@tonic-gate SELECT count(*), sum(a), sum(b), sum(c) FROM t3; 5817c478bd9Sstevel@tonic-gate } 5827c478bd9Sstevel@tonic-gate} {100 5050 5050 348550} 5837c478bd9Sstevel@tonic-gatedo_test format3-10.2 { 5847c478bd9Sstevel@tonic-gate cksort { 5857c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a LIMIT 3 5867c478bd9Sstevel@tonic-gate } 5877c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort} 5887c478bd9Sstevel@tonic-gatedo_test format3-10.3 { 5897c478bd9Sstevel@tonic-gate cksort { 5907c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a+1 LIMIT 3 5917c478bd9Sstevel@tonic-gate } 5927c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 sort} 5937c478bd9Sstevel@tonic-gatedo_test format3-10.4 { 5947c478bd9Sstevel@tonic-gate cksort { 5957c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3 5967c478bd9Sstevel@tonic-gate } 5977c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort} 5987c478bd9Sstevel@tonic-gatedo_test format3-10.5 { 5997c478bd9Sstevel@tonic-gate cksort { 6007c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3 6017c478bd9Sstevel@tonic-gate } 6027c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort} 6037c478bd9Sstevel@tonic-gatedo_test format3-10.6 { 6047c478bd9Sstevel@tonic-gate cksort { 6057c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3 6067c478bd9Sstevel@tonic-gate } 6077c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 nosort} 6087c478bd9Sstevel@tonic-gatedo_test format3-10.7 { 6097c478bd9Sstevel@tonic-gate cksort { 6107c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3 6117c478bd9Sstevel@tonic-gate } 6127c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 sort} 6137c478bd9Sstevel@tonic-gatedo_test format3-10.8 { 6147c478bd9Sstevel@tonic-gate cksort { 6157c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 6167c478bd9Sstevel@tonic-gate } 6177c478bd9Sstevel@tonic-gate} {1 100 4 2 99 9 3 98 16 sort} 6187c478bd9Sstevel@tonic-gatedo_test format3-10.9 { 6197c478bd9Sstevel@tonic-gate cksort { 6207c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 6217c478bd9Sstevel@tonic-gate } 6227c478bd9Sstevel@tonic-gate} {1 100 4 nosort} 6237c478bd9Sstevel@tonic-gatedo_test format3-10.10 { 6247c478bd9Sstevel@tonic-gate cksort { 6257c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 6267c478bd9Sstevel@tonic-gate } 6277c478bd9Sstevel@tonic-gate} {1 100 4 nosort} 6287c478bd9Sstevel@tonic-gatedo_test format3-10.11 { 6297c478bd9Sstevel@tonic-gate cksort { 6307c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 6317c478bd9Sstevel@tonic-gate } 6327c478bd9Sstevel@tonic-gate} {1 100 4 nosort} 6337c478bd9Sstevel@tonic-gatedo_test format3-10.12 { 6347c478bd9Sstevel@tonic-gate cksort { 6357c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3 6367c478bd9Sstevel@tonic-gate } 6377c478bd9Sstevel@tonic-gate} {1 100 4 nosort} 6387c478bd9Sstevel@tonic-gatedo_test format3-10.13 { 6397c478bd9Sstevel@tonic-gate cksort { 6407c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3 6417c478bd9Sstevel@tonic-gate } 6427c478bd9Sstevel@tonic-gate} {100 1 10201 99 2 10000 98 3 9801 nosort} 6437c478bd9Sstevel@tonic-gatedo_test format3-10.13.1 { 6447c478bd9Sstevel@tonic-gate cksort { 6457c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a>0 ORDER BY a+1 DESC LIMIT 3 6467c478bd9Sstevel@tonic-gate } 6477c478bd9Sstevel@tonic-gate} {100 1 10201 99 2 10000 98 3 9801 sort} 6487c478bd9Sstevel@tonic-gatedo_test format3-10.14 { 6497c478bd9Sstevel@tonic-gate cksort { 6507c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY b LIMIT 3 6517c478bd9Sstevel@tonic-gate } 6527c478bd9Sstevel@tonic-gate} {100 1 10201 99 2 10000 98 3 9801 nosort} 6537c478bd9Sstevel@tonic-gatedo_test format3-10.15 { 6547c478bd9Sstevel@tonic-gate cksort { 6557c478bd9Sstevel@tonic-gate SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3 6567c478bd9Sstevel@tonic-gate } 6577c478bd9Sstevel@tonic-gate} {1 0 2 1 3 1 nosort} 6587c478bd9Sstevel@tonic-gatedo_test format3-10.16 { 6597c478bd9Sstevel@tonic-gate cksort { 6607c478bd9Sstevel@tonic-gate SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 6617c478bd9Sstevel@tonic-gate } 6627c478bd9Sstevel@tonic-gate} {1 0 2 1 3 1 sort} 6637c478bd9Sstevel@tonic-gatedo_test format3-10.17 { 6647c478bd9Sstevel@tonic-gate cksort { 6657c478bd9Sstevel@tonic-gate SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3; 6667c478bd9Sstevel@tonic-gate } 6677c478bd9Sstevel@tonic-gate} {4 121 10201 sort} 6687c478bd9Sstevel@tonic-gatedo_test format3-10.18 { 6697c478bd9Sstevel@tonic-gate cksort { 6707c478bd9Sstevel@tonic-gate SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3; 6717c478bd9Sstevel@tonic-gate } 6727c478bd9Sstevel@tonic-gate} {4 9 16 sort} 6737c478bd9Sstevel@tonic-gatedo_test format3-10.19 { 6747c478bd9Sstevel@tonic-gate cksort { 6757c478bd9Sstevel@tonic-gate SELECT y FROM t1 ORDER BY w LIMIT 3; 6767c478bd9Sstevel@tonic-gate } 6777c478bd9Sstevel@tonic-gate} {4 9 16 nosort} 6787c478bd9Sstevel@tonic-gate 6797c478bd9Sstevel@tonic-gate# Check that all comparisons are numeric. Similar tests in misc1.test 6807c478bd9Sstevel@tonic-gate# check the same comparisons on a format4+ database and find that some 6817c478bd9Sstevel@tonic-gate# are numeric and some are text. 6827c478bd9Sstevel@tonic-gate# 6837c478bd9Sstevel@tonic-gatedo_test format3-11.1 { 6847c478bd9Sstevel@tonic-gate execsql {SELECT '0'=='0.0'} 6857c478bd9Sstevel@tonic-gate} {1} 6867c478bd9Sstevel@tonic-gatedo_test format3-11.2 { 6877c478bd9Sstevel@tonic-gate execsql {SELECT '0'==0.0} 6887c478bd9Sstevel@tonic-gate} {1} 6897c478bd9Sstevel@tonic-gatedo_test format3-11.3 { 6907c478bd9Sstevel@tonic-gate execsql {SELECT '123456789012345678901'=='123456789012345678900'} 6917c478bd9Sstevel@tonic-gate} {1} 6927c478bd9Sstevel@tonic-gatedo_test format3-11.4 { 6937c478bd9Sstevel@tonic-gate execsql { 6947c478bd9Sstevel@tonic-gate CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE); 6957c478bd9Sstevel@tonic-gate INSERT INTO t6 VALUES('0','0.0'); 6967c478bd9Sstevel@tonic-gate SELECT * FROM t6; 6977c478bd9Sstevel@tonic-gate } 6987c478bd9Sstevel@tonic-gate} {0 0.0} 6997c478bd9Sstevel@tonic-gatedo_test format3-11.5 { 7007c478bd9Sstevel@tonic-gate execsql { 7017c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t6 VALUES(0.0,'x'); 7027c478bd9Sstevel@tonic-gate SELECT * FROM t6; 7037c478bd9Sstevel@tonic-gate } 7047c478bd9Sstevel@tonic-gate} {0 0.0} 7057c478bd9Sstevel@tonic-gatedo_test format3-11.6 { 7067c478bd9Sstevel@tonic-gate execsql { 7077c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t6 VALUES('y',0); 7087c478bd9Sstevel@tonic-gate SELECT * FROM t6; 7097c478bd9Sstevel@tonic-gate } 7107c478bd9Sstevel@tonic-gate} {0 0.0} 7117c478bd9Sstevel@tonic-gatedo_test format3-11.7 { 7127c478bd9Sstevel@tonic-gate execsql { 7137c478bd9Sstevel@tonic-gate CREATE TABLE t7(x INTEGER, y TEXT, z); 7147c478bd9Sstevel@tonic-gate INSERT INTO t7 VALUES(0,0,1); 7157c478bd9Sstevel@tonic-gate INSERT INTO t7 VALUES(0.0,0,2); 7167c478bd9Sstevel@tonic-gate INSERT INTO t7 VALUES(0,0.0,3); 7177c478bd9Sstevel@tonic-gate INSERT INTO t7 VALUES(0.0,0.0,4); 7187c478bd9Sstevel@tonic-gate SELECT DISTINCT x, y FROM t7 ORDER BY z; 7197c478bd9Sstevel@tonic-gate } 7207c478bd9Sstevel@tonic-gate} {0 0} 7217c478bd9Sstevel@tonic-gate 7227c478bd9Sstevel@tonic-gate# Make sure attempts to attach a format 3 database fail. 7237c478bd9Sstevel@tonic-gate# 7247c478bd9Sstevel@tonic-gatedo_test format3-12.1 { 7257c478bd9Sstevel@tonic-gate file delete -force test2.db 7267c478bd9Sstevel@tonic-gate sqlite db2 test2.db 7277c478bd9Sstevel@tonic-gate catchsql { 7287c478bd9Sstevel@tonic-gate CREATE TABLE t8(x,y); 7297c478bd9Sstevel@tonic-gate ATTACH DATABASE 'test.db' AS format3; 7307c478bd9Sstevel@tonic-gate } db2; 7317c478bd9Sstevel@tonic-gate} {1 {incompatible file format in auxiliary database: format3}} 7327c478bd9Sstevel@tonic-gatedo_test format3-12.2 { 7337c478bd9Sstevel@tonic-gate catchsql { 7347c478bd9Sstevel@tonic-gate ATTACH DATABASE 'test2.db' AS test2; 7357c478bd9Sstevel@tonic-gate } 7367c478bd9Sstevel@tonic-gate} {1 {cannot attach auxiliary databases to an older format master database}} 7377c478bd9Sstevel@tonic-gatedb2 close 7387c478bd9Sstevel@tonic-gate 7397c478bd9Sstevel@tonic-gatefinish_test 740