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: select1.test,v 1.30.2.3 2004/07/20 01:45:49 drh Exp $ 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 197c478bd9Sstevel@tonic-gate 207c478bd9Sstevel@tonic-gate# Try to select on a non-existant table. 217c478bd9Sstevel@tonic-gate# 227c478bd9Sstevel@tonic-gatedo_test select1-1.1 { 237c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT * FROM test1}} msg] 247c478bd9Sstevel@tonic-gate lappend v $msg 257c478bd9Sstevel@tonic-gate} {1 {no such table: test1}} 267c478bd9Sstevel@tonic-gate 277c478bd9Sstevel@tonic-gateexecsql {CREATE TABLE test1(f1 int, f2 int)} 287c478bd9Sstevel@tonic-gate 297c478bd9Sstevel@tonic-gatedo_test select1-1.2 { 307c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT * FROM test1, test2}} msg] 317c478bd9Sstevel@tonic-gate lappend v $msg 327c478bd9Sstevel@tonic-gate} {1 {no such table: test2}} 337c478bd9Sstevel@tonic-gatedo_test select1-1.3 { 347c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT * FROM test2, test1}} msg] 357c478bd9Sstevel@tonic-gate lappend v $msg 367c478bd9Sstevel@tonic-gate} {1 {no such table: test2}} 377c478bd9Sstevel@tonic-gate 387c478bd9Sstevel@tonic-gateexecsql {INSERT INTO test1(f1,f2) VALUES(11,22)} 397c478bd9Sstevel@tonic-gate 407c478bd9Sstevel@tonic-gate 417c478bd9Sstevel@tonic-gate# Make sure the columns are extracted correctly. 427c478bd9Sstevel@tonic-gate# 437c478bd9Sstevel@tonic-gatedo_test select1-1.4 { 447c478bd9Sstevel@tonic-gate execsql {SELECT f1 FROM test1} 457c478bd9Sstevel@tonic-gate} {11} 467c478bd9Sstevel@tonic-gatedo_test select1-1.5 { 477c478bd9Sstevel@tonic-gate execsql {SELECT f2 FROM test1} 487c478bd9Sstevel@tonic-gate} {22} 497c478bd9Sstevel@tonic-gatedo_test select1-1.6 { 507c478bd9Sstevel@tonic-gate execsql {SELECT f2, f1 FROM test1} 517c478bd9Sstevel@tonic-gate} {22 11} 527c478bd9Sstevel@tonic-gatedo_test select1-1.7 { 537c478bd9Sstevel@tonic-gate execsql {SELECT f1, f2 FROM test1} 547c478bd9Sstevel@tonic-gate} {11 22} 557c478bd9Sstevel@tonic-gatedo_test select1-1.8 { 567c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1} 577c478bd9Sstevel@tonic-gate} {11 22} 587c478bd9Sstevel@tonic-gatedo_test select1-1.8.1 { 597c478bd9Sstevel@tonic-gate execsql {SELECT *, * FROM test1} 607c478bd9Sstevel@tonic-gate} {11 22 11 22} 617c478bd9Sstevel@tonic-gatedo_test select1-1.8.2 { 627c478bd9Sstevel@tonic-gate execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1} 637c478bd9Sstevel@tonic-gate} {11 22 11 22} 647c478bd9Sstevel@tonic-gatedo_test select1-1.8.3 { 657c478bd9Sstevel@tonic-gate execsql {SELECT 'one', *, 'two', * FROM test1} 667c478bd9Sstevel@tonic-gate} {one 11 22 two 11 22} 677c478bd9Sstevel@tonic-gate 687c478bd9Sstevel@tonic-gateexecsql {CREATE TABLE test2(r1 real, r2 real)} 697c478bd9Sstevel@tonic-gateexecsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)} 707c478bd9Sstevel@tonic-gate 717c478bd9Sstevel@tonic-gatedo_test select1-1.9 { 727c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1, test2} 737c478bd9Sstevel@tonic-gate} {11 22 1.1 2.2} 747c478bd9Sstevel@tonic-gatedo_test select1-1.9.1 { 757c478bd9Sstevel@tonic-gate execsql {SELECT *, 'hi' FROM test1, test2} 767c478bd9Sstevel@tonic-gate} {11 22 1.1 2.2 hi} 777c478bd9Sstevel@tonic-gatedo_test select1-1.9.2 { 787c478bd9Sstevel@tonic-gate execsql {SELECT 'one', *, 'two', * FROM test1, test2} 797c478bd9Sstevel@tonic-gate} {one 11 22 1.1 2.2 two 11 22 1.1 2.2} 807c478bd9Sstevel@tonic-gatedo_test select1-1.10 { 817c478bd9Sstevel@tonic-gate execsql {SELECT test1.f1, test2.r1 FROM test1, test2} 827c478bd9Sstevel@tonic-gate} {11 1.1} 837c478bd9Sstevel@tonic-gatedo_test select1-1.11 { 847c478bd9Sstevel@tonic-gate execsql {SELECT test1.f1, test2.r1 FROM test2, test1} 857c478bd9Sstevel@tonic-gate} {11 1.1} 867c478bd9Sstevel@tonic-gatedo_test select1-1.11.1 { 877c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2, test1} 887c478bd9Sstevel@tonic-gate} {1.1 2.2 11 22} 897c478bd9Sstevel@tonic-gatedo_test select1-1.11.2 { 907c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1 AS a, test1 AS b} 917c478bd9Sstevel@tonic-gate} {11 22 11 22} 927c478bd9Sstevel@tonic-gatedo_test select1-1.12 { 937c478bd9Sstevel@tonic-gate execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2) 947c478bd9Sstevel@tonic-gate FROM test2, test1} 957c478bd9Sstevel@tonic-gate} {11 2.2} 967c478bd9Sstevel@tonic-gatedo_test select1-1.13 { 977c478bd9Sstevel@tonic-gate execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2) 987c478bd9Sstevel@tonic-gate FROM test1, test2} 997c478bd9Sstevel@tonic-gate} {1.1 22} 1007c478bd9Sstevel@tonic-gate 1017c478bd9Sstevel@tonic-gateset long {This is a string that is too big to fit inside a NBFS buffer} 1027c478bd9Sstevel@tonic-gatedo_test select1-2.0 { 1037c478bd9Sstevel@tonic-gate execsql " 1047c478bd9Sstevel@tonic-gate DROP TABLE test2; 1057c478bd9Sstevel@tonic-gate DELETE FROM test1; 1067c478bd9Sstevel@tonic-gate INSERT INTO test1 VALUES(11,22); 1077c478bd9Sstevel@tonic-gate INSERT INTO test1 VALUES(33,44); 1087c478bd9Sstevel@tonic-gate CREATE TABLE t3(a,b); 1097c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES('abc',NULL); 1107c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(NULL,'xyz'); 1117c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT * FROM test1; 1127c478bd9Sstevel@tonic-gate CREATE TABLE t4(a,b); 1137c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(NULL,'$long'); 1147c478bd9Sstevel@tonic-gate SELECT * FROM t3; 1157c478bd9Sstevel@tonic-gate " 1167c478bd9Sstevel@tonic-gate} {abc {} {} xyz 11 22 33 44} 1177c478bd9Sstevel@tonic-gate 1187c478bd9Sstevel@tonic-gate# Error messges from sqliteExprCheck 1197c478bd9Sstevel@tonic-gate# 1207c478bd9Sstevel@tonic-gatedo_test select1-2.1 { 1217c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg] 1227c478bd9Sstevel@tonic-gate lappend v $msg 1237c478bd9Sstevel@tonic-gate} {1 {wrong number of arguments to function count()}} 1247c478bd9Sstevel@tonic-gatedo_test select1-2.2 { 1257c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT count(f1) FROM test1}} msg] 1267c478bd9Sstevel@tonic-gate lappend v $msg 1277c478bd9Sstevel@tonic-gate} {0 2} 1287c478bd9Sstevel@tonic-gatedo_test select1-2.3 { 1297c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT Count() FROM test1}} msg] 1307c478bd9Sstevel@tonic-gate lappend v $msg 1317c478bd9Sstevel@tonic-gate} {0 2} 1327c478bd9Sstevel@tonic-gatedo_test select1-2.4 { 1337c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg] 1347c478bd9Sstevel@tonic-gate lappend v $msg 1357c478bd9Sstevel@tonic-gate} {0 2} 1367c478bd9Sstevel@tonic-gatedo_test select1-2.5 { 1377c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg] 1387c478bd9Sstevel@tonic-gate lappend v $msg 1397c478bd9Sstevel@tonic-gate} {0 3} 1407c478bd9Sstevel@tonic-gatedo_test select1-2.5.1 { 1417c478bd9Sstevel@tonic-gate execsql {SELECT count(*),count(a),count(b) FROM t3} 1427c478bd9Sstevel@tonic-gate} {4 3 3} 1437c478bd9Sstevel@tonic-gatedo_test select1-2.5.2 { 1447c478bd9Sstevel@tonic-gate execsql {SELECT count(*),count(a),count(b) FROM t4} 1457c478bd9Sstevel@tonic-gate} {1 0 1} 1467c478bd9Sstevel@tonic-gatedo_test select1-2.5.3 { 1477c478bd9Sstevel@tonic-gate execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5} 1487c478bd9Sstevel@tonic-gate} {0 0 0} 1497c478bd9Sstevel@tonic-gatedo_test select1-2.6 { 1507c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT min(*) FROM test1}} msg] 1517c478bd9Sstevel@tonic-gate lappend v $msg 1527c478bd9Sstevel@tonic-gate} {1 {wrong number of arguments to function min()}} 1537c478bd9Sstevel@tonic-gatedo_test select1-2.7 { 1547c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT Min(f1) FROM test1}} msg] 1557c478bd9Sstevel@tonic-gate lappend v $msg 1567c478bd9Sstevel@tonic-gate} {0 11} 1577c478bd9Sstevel@tonic-gatedo_test select1-2.8 { 1587c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg] 1597c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 1607c478bd9Sstevel@tonic-gate} {0 {11 33}} 1617c478bd9Sstevel@tonic-gatedo_test select1-2.8.1 { 1627c478bd9Sstevel@tonic-gate execsql {SELECT coalesce(min(a),'xyzzy') FROM t3} 1637c478bd9Sstevel@tonic-gate} {11} 1647c478bd9Sstevel@tonic-gatedo_test select1-2.8.2 { 1657c478bd9Sstevel@tonic-gate execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3} 1667c478bd9Sstevel@tonic-gate} {11} 1677c478bd9Sstevel@tonic-gatedo_test select1-2.8.3 { 1687c478bd9Sstevel@tonic-gate execsql {SELECT min(b), min(b) FROM t4} 1697c478bd9Sstevel@tonic-gate} [list $long $long] 1707c478bd9Sstevel@tonic-gatedo_test select1-2.9 { 1717c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT MAX(*) FROM test1}} msg] 1727c478bd9Sstevel@tonic-gate lappend v $msg 1737c478bd9Sstevel@tonic-gate} {1 {wrong number of arguments to function MAX()}} 1747c478bd9Sstevel@tonic-gatedo_test select1-2.10 { 1757c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT Max(f1) FROM test1}} msg] 1767c478bd9Sstevel@tonic-gate lappend v $msg 1777c478bd9Sstevel@tonic-gate} {0 33} 1787c478bd9Sstevel@tonic-gatedo_test select1-2.11 { 1797c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg] 1807c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 1817c478bd9Sstevel@tonic-gate} {0 {22 44}} 1827c478bd9Sstevel@tonic-gatedo_test select1-2.12 { 1837c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg] 1847c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 1857c478bd9Sstevel@tonic-gate} {0 {23 45}} 1867c478bd9Sstevel@tonic-gatedo_test select1-2.13 { 1877c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] 1887c478bd9Sstevel@tonic-gate lappend v $msg 1897c478bd9Sstevel@tonic-gate} {0 34} 1907c478bd9Sstevel@tonic-gatedo_test select1-2.13.1 { 1917c478bd9Sstevel@tonic-gate execsql {SELECT coalesce(max(a),'xyzzy') FROM t3} 1927c478bd9Sstevel@tonic-gate} {abc} 1937c478bd9Sstevel@tonic-gatedo_test select1-2.13.2 { 1947c478bd9Sstevel@tonic-gate execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3} 1957c478bd9Sstevel@tonic-gate} {xyzzy} 1967c478bd9Sstevel@tonic-gatedo_test select1-2.14 { 1977c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] 1987c478bd9Sstevel@tonic-gate lappend v $msg 1997c478bd9Sstevel@tonic-gate} {1 {wrong number of arguments to function SUM()}} 2007c478bd9Sstevel@tonic-gatedo_test select1-2.15 { 2017c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg] 2027c478bd9Sstevel@tonic-gate lappend v $msg 2037c478bd9Sstevel@tonic-gate} {0 44} 2047c478bd9Sstevel@tonic-gatedo_test select1-2.16 { 2057c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg] 2067c478bd9Sstevel@tonic-gate lappend v $msg 2077c478bd9Sstevel@tonic-gate} {1 {wrong number of arguments to function sum()}} 2087c478bd9Sstevel@tonic-gatedo_test select1-2.17 { 2097c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] 2107c478bd9Sstevel@tonic-gate lappend v $msg 2117c478bd9Sstevel@tonic-gate} {0 45} 2127c478bd9Sstevel@tonic-gatedo_test select1-2.17.1 { 2137c478bd9Sstevel@tonic-gate execsql {SELECT sum(a) FROM t3} 2147c478bd9Sstevel@tonic-gate} {44} 2157c478bd9Sstevel@tonic-gatedo_test select1-2.18 { 2167c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg] 2177c478bd9Sstevel@tonic-gate lappend v $msg 2187c478bd9Sstevel@tonic-gate} {1 {no such function: XYZZY}} 2197c478bd9Sstevel@tonic-gatedo_test select1-2.19 { 2207c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] 2217c478bd9Sstevel@tonic-gate lappend v $msg 2227c478bd9Sstevel@tonic-gate} {0 44} 2237c478bd9Sstevel@tonic-gatedo_test select1-2.20 { 2247c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg] 2257c478bd9Sstevel@tonic-gate lappend v $msg 2267c478bd9Sstevel@tonic-gate} {1 {misuse of aggregate function min()}} 2277c478bd9Sstevel@tonic-gate 2287c478bd9Sstevel@tonic-gate# WHERE clause expressions 2297c478bd9Sstevel@tonic-gate# 2307c478bd9Sstevel@tonic-gatedo_test select1-3.1 { 2317c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg] 2327c478bd9Sstevel@tonic-gate lappend v $msg 2337c478bd9Sstevel@tonic-gate} {0 {}} 2347c478bd9Sstevel@tonic-gatedo_test select1-3.2 { 2357c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg] 2367c478bd9Sstevel@tonic-gate lappend v $msg 2377c478bd9Sstevel@tonic-gate} {0 11} 2387c478bd9Sstevel@tonic-gatedo_test select1-3.3 { 2397c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg] 2407c478bd9Sstevel@tonic-gate lappend v $msg 2417c478bd9Sstevel@tonic-gate} {0 11} 2427c478bd9Sstevel@tonic-gatedo_test select1-3.4 { 2437c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg] 2447c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 2457c478bd9Sstevel@tonic-gate} {0 {11 33}} 2467c478bd9Sstevel@tonic-gatedo_test select1-3.5 { 2477c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg] 2487c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 2497c478bd9Sstevel@tonic-gate} {0 33} 2507c478bd9Sstevel@tonic-gatedo_test select1-3.6 { 2517c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg] 2527c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 2537c478bd9Sstevel@tonic-gate} {0 33} 2547c478bd9Sstevel@tonic-gatedo_test select1-3.7 { 2557c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg] 2567c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 2577c478bd9Sstevel@tonic-gate} {0 33} 2587c478bd9Sstevel@tonic-gatedo_test select1-3.8 { 2597c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg] 2607c478bd9Sstevel@tonic-gate lappend v [lsort $msg] 2617c478bd9Sstevel@tonic-gate} {0 {11 33}} 2627c478bd9Sstevel@tonic-gatedo_test select1-3.9 { 2637c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg] 2647c478bd9Sstevel@tonic-gate lappend v $msg 2657c478bd9Sstevel@tonic-gate} {1 {wrong number of arguments to function count()}} 2667c478bd9Sstevel@tonic-gate 2677c478bd9Sstevel@tonic-gate# ORDER BY expressions 2687c478bd9Sstevel@tonic-gate# 2697c478bd9Sstevel@tonic-gatedo_test select1-4.1 { 2707c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg] 2717c478bd9Sstevel@tonic-gate lappend v $msg 2727c478bd9Sstevel@tonic-gate} {0 {11 33}} 2737c478bd9Sstevel@tonic-gatedo_test select1-4.2 { 2747c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg] 2757c478bd9Sstevel@tonic-gate lappend v $msg 2767c478bd9Sstevel@tonic-gate} {0 {33 11}} 2777c478bd9Sstevel@tonic-gatedo_test select1-4.3 { 2787c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] 2797c478bd9Sstevel@tonic-gate lappend v $msg 2807c478bd9Sstevel@tonic-gate} {0 {11 33}} 2817c478bd9Sstevel@tonic-gatedo_test select1-4.4 { 2827c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] 2837c478bd9Sstevel@tonic-gate lappend v $msg 2847c478bd9Sstevel@tonic-gate} {1 {misuse of aggregate function min()}} 2857c478bd9Sstevel@tonic-gatedo_test select1-4.5 { 2867c478bd9Sstevel@tonic-gate catchsql { 2877c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 ORDER BY 8.4; 2887c478bd9Sstevel@tonic-gate } 2897c478bd9Sstevel@tonic-gate} {1 {ORDER BY terms must not be non-integer constants}} 2907c478bd9Sstevel@tonic-gatedo_test select1-4.6 { 2917c478bd9Sstevel@tonic-gate catchsql { 2927c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 ORDER BY '8.4'; 2937c478bd9Sstevel@tonic-gate } 2947c478bd9Sstevel@tonic-gate} {1 {ORDER BY terms must not be non-integer constants}} 2957c478bd9Sstevel@tonic-gatedo_test select1-4.7 { 2967c478bd9Sstevel@tonic-gate catchsql { 2977c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 ORDER BY 'xyz'; 2987c478bd9Sstevel@tonic-gate } 2997c478bd9Sstevel@tonic-gate} {1 {ORDER BY terms must not be non-integer constants}} 3007c478bd9Sstevel@tonic-gatedo_test select1-4.8 { 3017c478bd9Sstevel@tonic-gate execsql { 3027c478bd9Sstevel@tonic-gate CREATE TABLE t5(a,b); 3037c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(1,10); 3047c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(2,9); 3057c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY 1; 3067c478bd9Sstevel@tonic-gate } 3077c478bd9Sstevel@tonic-gate} {1 10 2 9} 3087c478bd9Sstevel@tonic-gatedo_test select1-4.9 { 3097c478bd9Sstevel@tonic-gate execsql { 3107c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY 2; 3117c478bd9Sstevel@tonic-gate } 3127c478bd9Sstevel@tonic-gate} {2 9 1 10} 3137c478bd9Sstevel@tonic-gatedo_test select1-4.10 { 3147c478bd9Sstevel@tonic-gate catchsql { 3157c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY 3; 3167c478bd9Sstevel@tonic-gate } 3177c478bd9Sstevel@tonic-gate} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}} 3187c478bd9Sstevel@tonic-gatedo_test select1-4.11 { 3197c478bd9Sstevel@tonic-gate execsql { 3207c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(3,10); 3217c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY 2, 1 DESC; 3227c478bd9Sstevel@tonic-gate } 3237c478bd9Sstevel@tonic-gate} {2 9 3 10 1 10} 3247c478bd9Sstevel@tonic-gatedo_test select1-4.12 { 3257c478bd9Sstevel@tonic-gate execsql { 3267c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY 1 DESC, b; 3277c478bd9Sstevel@tonic-gate } 3287c478bd9Sstevel@tonic-gate} {3 10 2 9 1 10} 3297c478bd9Sstevel@tonic-gatedo_test select1-4.13 { 3307c478bd9Sstevel@tonic-gate execsql { 3317c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY b DESC, 1; 3327c478bd9Sstevel@tonic-gate } 3337c478bd9Sstevel@tonic-gate} {1 10 3 10 2 9} 3347c478bd9Sstevel@tonic-gate 3357c478bd9Sstevel@tonic-gate 3367c478bd9Sstevel@tonic-gate# ORDER BY ignored on an aggregate query 3377c478bd9Sstevel@tonic-gate# 3387c478bd9Sstevel@tonic-gatedo_test select1-5.1 { 3397c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg] 3407c478bd9Sstevel@tonic-gate lappend v $msg 3417c478bd9Sstevel@tonic-gate} {0 33} 3427c478bd9Sstevel@tonic-gate 3437c478bd9Sstevel@tonic-gateexecsql {CREATE TABLE test2(t1 test, t2 text)} 3447c478bd9Sstevel@tonic-gateexecsql {INSERT INTO test2 VALUES('abc','xyz')} 3457c478bd9Sstevel@tonic-gate 3467c478bd9Sstevel@tonic-gate# Check for column naming 3477c478bd9Sstevel@tonic-gate# 3487c478bd9Sstevel@tonic-gatedo_test select1-6.1 { 3497c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 3507c478bd9Sstevel@tonic-gate lappend v $msg 3517c478bd9Sstevel@tonic-gate} {0 {f1 11 f1 33}} 3527c478bd9Sstevel@tonic-gatedo_test select1-6.1.1 { 3537c478bd9Sstevel@tonic-gate execsql {PRAGMA full_column_names=on} 3547c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg] 3557c478bd9Sstevel@tonic-gate lappend v $msg 3567c478bd9Sstevel@tonic-gate} {0 {test1.f1 11 test1.f1 33}} 3577c478bd9Sstevel@tonic-gatedo_test select1-6.1.2 { 3587c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg] 3597c478bd9Sstevel@tonic-gate lappend v $msg 3607c478bd9Sstevel@tonic-gate} {0 {f1 11 f1 33}} 3617c478bd9Sstevel@tonic-gatedo_test select1-6.1.3 { 3627c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 3637c478bd9Sstevel@tonic-gate lappend v $msg 3647c478bd9Sstevel@tonic-gate} {0 {test1.f1 11 test1.f2 22}} 3657c478bd9Sstevel@tonic-gatedo_test select1-6.1.4 { 3667c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 3677c478bd9Sstevel@tonic-gate execsql {PRAGMA full_column_names=off} 3687c478bd9Sstevel@tonic-gate lappend v $msg 3697c478bd9Sstevel@tonic-gate} {0 {test1.f1 11 test1.f2 22}} 3707c478bd9Sstevel@tonic-gatedo_test select1-6.1.5 { 3717c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg] 3727c478bd9Sstevel@tonic-gate lappend v $msg 3737c478bd9Sstevel@tonic-gate} {0 {f1 11 f2 22}} 3747c478bd9Sstevel@tonic-gatedo_test select1-6.1.6 { 3757c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg] 3767c478bd9Sstevel@tonic-gate lappend v $msg 3777c478bd9Sstevel@tonic-gate} {0 {f1 11 f2 22}} 3787c478bd9Sstevel@tonic-gatedo_test select1-6.2 { 3797c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg] 3807c478bd9Sstevel@tonic-gate lappend v $msg 3817c478bd9Sstevel@tonic-gate} {0 {xyzzy 11 xyzzy 33}} 3827c478bd9Sstevel@tonic-gatedo_test select1-6.3 { 3837c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg] 3847c478bd9Sstevel@tonic-gate lappend v $msg 3857c478bd9Sstevel@tonic-gate} {0 {xyzzy 11 xyzzy 33}} 3867c478bd9Sstevel@tonic-gatedo_test select1-6.3.1 { 3877c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg] 3887c478bd9Sstevel@tonic-gate lappend v $msg 3897c478bd9Sstevel@tonic-gate} {0 {{xyzzy } 11 {xyzzy } 33}} 3907c478bd9Sstevel@tonic-gatedo_test select1-6.4 { 3917c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg] 3927c478bd9Sstevel@tonic-gate lappend v $msg 3937c478bd9Sstevel@tonic-gate} {0 {xyzzy 33 xyzzy 77}} 3947c478bd9Sstevel@tonic-gatedo_test select1-6.4a { 3957c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg] 3967c478bd9Sstevel@tonic-gate lappend v $msg 3977c478bd9Sstevel@tonic-gate} {0 {f1+F2 33 f1+F2 77}} 3987c478bd9Sstevel@tonic-gatedo_test select1-6.5 { 3997c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 4007c478bd9Sstevel@tonic-gate lappend v $msg 4017c478bd9Sstevel@tonic-gate} {0 {test1.f1+F2 33 test1.f1+F2 77}} 4027c478bd9Sstevel@tonic-gatedo_test select1-6.5.1 { 4037c478bd9Sstevel@tonic-gate execsql2 {PRAGMA full_column_names=on} 4047c478bd9Sstevel@tonic-gate set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg] 4057c478bd9Sstevel@tonic-gate execsql2 {PRAGMA full_column_names=off} 4067c478bd9Sstevel@tonic-gate lappend v $msg 4077c478bd9Sstevel@tonic-gate} {0 {test1.f1+F2 33 test1.f1+F2 77}} 4087c478bd9Sstevel@tonic-gatedo_test select1-6.6 { 409*1da57d55SToomas Soome set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 4107c478bd9Sstevel@tonic-gate ORDER BY f2}} msg] 4117c478bd9Sstevel@tonic-gate lappend v $msg 4127c478bd9Sstevel@tonic-gate} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}} 4137c478bd9Sstevel@tonic-gatedo_test select1-6.7 { 414*1da57d55SToomas Soome set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 4157c478bd9Sstevel@tonic-gate ORDER BY f2}} msg] 4167c478bd9Sstevel@tonic-gate lappend v $msg 4177c478bd9Sstevel@tonic-gate} {0 {A.f1 11 t1 abc A.f1 33 t1 abc}} 4187c478bd9Sstevel@tonic-gatedo_test select1-6.8 { 419*1da57d55SToomas Soome set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 4207c478bd9Sstevel@tonic-gate ORDER BY f2}} msg] 4217c478bd9Sstevel@tonic-gate lappend v $msg 4227c478bd9Sstevel@tonic-gate} {1 {ambiguous column name: f1}} 4237c478bd9Sstevel@tonic-gatedo_test select1-6.8b { 424*1da57d55SToomas Soome set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 4257c478bd9Sstevel@tonic-gate ORDER BY f2}} msg] 4267c478bd9Sstevel@tonic-gate lappend v $msg 4277c478bd9Sstevel@tonic-gate} {1 {ambiguous column name: f2}} 4287c478bd9Sstevel@tonic-gatedo_test select1-6.8c { 429*1da57d55SToomas Soome set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 4307c478bd9Sstevel@tonic-gate ORDER BY f2}} msg] 4317c478bd9Sstevel@tonic-gate lappend v $msg 4327c478bd9Sstevel@tonic-gate} {1 {ambiguous column name: A.f1}} 4337c478bd9Sstevel@tonic-gatedo_test select1-6.9 { 434*1da57d55SToomas Soome set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 4357c478bd9Sstevel@tonic-gate ORDER BY A.f1, B.f1}} msg] 4367c478bd9Sstevel@tonic-gate lappend v $msg 4377c478bd9Sstevel@tonic-gate} {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}} 4387c478bd9Sstevel@tonic-gatedo_test select1-6.10 { 4397c478bd9Sstevel@tonic-gate set v [catch {execsql2 { 4407c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 UNION SELECT f2 FROM test1 4417c478bd9Sstevel@tonic-gate ORDER BY f2; 4427c478bd9Sstevel@tonic-gate }} msg] 4437c478bd9Sstevel@tonic-gate lappend v $msg 4447c478bd9Sstevel@tonic-gate} {0 {f2 11 f2 22 f2 33 f2 44}} 4457c478bd9Sstevel@tonic-gatedo_test select1-6.11 { 4467c478bd9Sstevel@tonic-gate set v [catch {execsql2 { 4477c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 4487c478bd9Sstevel@tonic-gate ORDER BY f2+100; 4497c478bd9Sstevel@tonic-gate }} msg] 4507c478bd9Sstevel@tonic-gate lappend v $msg 4517c478bd9Sstevel@tonic-gate} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}} 4527c478bd9Sstevel@tonic-gate 4537c478bd9Sstevel@tonic-gatedo_test select1-7.1 { 4547c478bd9Sstevel@tonic-gate set v [catch {execsql { 4557c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 WHERE f2=; 4567c478bd9Sstevel@tonic-gate }} msg] 4577c478bd9Sstevel@tonic-gate lappend v $msg 4587c478bd9Sstevel@tonic-gate} {1 {near ";": syntax error}} 4597c478bd9Sstevel@tonic-gatedo_test select1-7.2 { 4607c478bd9Sstevel@tonic-gate set v [catch {execsql { 4617c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 UNION SELECT WHERE; 4627c478bd9Sstevel@tonic-gate }} msg] 4637c478bd9Sstevel@tonic-gate lappend v $msg 4647c478bd9Sstevel@tonic-gate} {1 {near "WHERE": syntax error}} 4657c478bd9Sstevel@tonic-gatedo_test select1-7.3 { 4667c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg] 4677c478bd9Sstevel@tonic-gate lappend v $msg 4687c478bd9Sstevel@tonic-gate} {1 {near "as": syntax error}} 4697c478bd9Sstevel@tonic-gatedo_test select1-7.4 { 4707c478bd9Sstevel@tonic-gate set v [catch {execsql { 4717c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 ORDER BY; 4727c478bd9Sstevel@tonic-gate }} msg] 4737c478bd9Sstevel@tonic-gate lappend v $msg 4747c478bd9Sstevel@tonic-gate} {1 {near ";": syntax error}} 4757c478bd9Sstevel@tonic-gatedo_test select1-7.5 { 4767c478bd9Sstevel@tonic-gate set v [catch {execsql { 4777c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 ORDER BY f1 desc, f2 where; 4787c478bd9Sstevel@tonic-gate }} msg] 4797c478bd9Sstevel@tonic-gate lappend v $msg 4807c478bd9Sstevel@tonic-gate} {1 {near "where": syntax error}} 4817c478bd9Sstevel@tonic-gatedo_test select1-7.6 { 4827c478bd9Sstevel@tonic-gate set v [catch {execsql { 4837c478bd9Sstevel@tonic-gate SELECT count(f1,f2 FROM test1; 4847c478bd9Sstevel@tonic-gate }} msg] 4857c478bd9Sstevel@tonic-gate lappend v $msg 4867c478bd9Sstevel@tonic-gate} {1 {near "FROM": syntax error}} 4877c478bd9Sstevel@tonic-gatedo_test select1-7.7 { 4887c478bd9Sstevel@tonic-gate set v [catch {execsql { 4897c478bd9Sstevel@tonic-gate SELECT count(f1,f2+) FROM test1; 4907c478bd9Sstevel@tonic-gate }} msg] 4917c478bd9Sstevel@tonic-gate lappend v $msg 4927c478bd9Sstevel@tonic-gate} {1 {near ")": syntax error}} 4937c478bd9Sstevel@tonic-gatedo_test select1-7.8 { 4947c478bd9Sstevel@tonic-gate set v [catch {execsql { 4957c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 ORDER BY f2, f1+; 4967c478bd9Sstevel@tonic-gate }} msg] 4977c478bd9Sstevel@tonic-gate lappend v $msg 4987c478bd9Sstevel@tonic-gate} {1 {near ";": syntax error}} 4997c478bd9Sstevel@tonic-gate 5007c478bd9Sstevel@tonic-gatedo_test select1-8.1 { 5017c478bd9Sstevel@tonic-gate execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1} 5027c478bd9Sstevel@tonic-gate} {11 33} 5037c478bd9Sstevel@tonic-gatedo_test select1-8.2 { 5047c478bd9Sstevel@tonic-gate execsql { 5057c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20' 5067c478bd9Sstevel@tonic-gate ORDER BY f1 5077c478bd9Sstevel@tonic-gate } 5087c478bd9Sstevel@tonic-gate} {11} 5097c478bd9Sstevel@tonic-gatedo_test select1-8.3 { 5107c478bd9Sstevel@tonic-gate execsql { 5117c478bd9Sstevel@tonic-gate SELECT f1 FROM test1 WHERE 5-3==2 5127c478bd9Sstevel@tonic-gate ORDER BY f1 5137c478bd9Sstevel@tonic-gate } 5147c478bd9Sstevel@tonic-gate} {11 33} 5157c478bd9Sstevel@tonic-gatedo_test select1-8.4 { 5167c478bd9Sstevel@tonic-gate execsql { 5177c478bd9Sstevel@tonic-gate SELECT coalesce(f1/(f1-11),'x'), 5187c478bd9Sstevel@tonic-gate coalesce(min(f1/(f1-11),5),'y'), 5197c478bd9Sstevel@tonic-gate coalesce(max(f1/(f1-33),6),'z') 5207c478bd9Sstevel@tonic-gate FROM test1 ORDER BY f1 5217c478bd9Sstevel@tonic-gate } 5227c478bd9Sstevel@tonic-gate} {x y 6 1.5 1.5 z} 5237c478bd9Sstevel@tonic-gatedo_test select1-8.5 { 5247c478bd9Sstevel@tonic-gate execsql { 5257c478bd9Sstevel@tonic-gate SELECT min(1,2,3), -max(1,2,3) 5267c478bd9Sstevel@tonic-gate FROM test1 ORDER BY f1 5277c478bd9Sstevel@tonic-gate } 5287c478bd9Sstevel@tonic-gate} {1 -3 1 -3} 5297c478bd9Sstevel@tonic-gate 5307c478bd9Sstevel@tonic-gate 5317c478bd9Sstevel@tonic-gate# Check the behavior when the result set is empty 5327c478bd9Sstevel@tonic-gate# 5337c478bd9Sstevel@tonic-gatedo_test select1-9.1 { 5347c478bd9Sstevel@tonic-gate catch {unset r} 5357c478bd9Sstevel@tonic-gate set r(*) {} 5367c478bd9Sstevel@tonic-gate db eval {SELECT * FROM test1 WHERE f1<0} r {} 5377c478bd9Sstevel@tonic-gate set r(*) 5387c478bd9Sstevel@tonic-gate} {} 5397c478bd9Sstevel@tonic-gatedo_test select1-9.2 { 5407c478bd9Sstevel@tonic-gate execsql {PRAGMA empty_result_callbacks=on} 5417c478bd9Sstevel@tonic-gate set r(*) {} 5427c478bd9Sstevel@tonic-gate db eval {SELECT * FROM test1 WHERE f1<0} r {} 5437c478bd9Sstevel@tonic-gate set r(*) 5447c478bd9Sstevel@tonic-gate} {f1 f2} 5457c478bd9Sstevel@tonic-gatedo_test select1-9.3 { 5467c478bd9Sstevel@tonic-gate set r(*) {} 5477c478bd9Sstevel@tonic-gate db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {} 5487c478bd9Sstevel@tonic-gate set r(*) 5497c478bd9Sstevel@tonic-gate} {f1 f2} 5507c478bd9Sstevel@tonic-gatedo_test select1-9.4 { 5517c478bd9Sstevel@tonic-gate set r(*) {} 5527c478bd9Sstevel@tonic-gate db eval {SELECT * FROM test1 ORDER BY f1} r {} 5537c478bd9Sstevel@tonic-gate set r(*) 5547c478bd9Sstevel@tonic-gate} {f1 f2} 5557c478bd9Sstevel@tonic-gatedo_test select1-9.5 { 5567c478bd9Sstevel@tonic-gate set r(*) {} 5577c478bd9Sstevel@tonic-gate db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} 5587c478bd9Sstevel@tonic-gate set r(*) 5597c478bd9Sstevel@tonic-gate} {f1 f2} 5607c478bd9Sstevel@tonic-gateunset r 5617c478bd9Sstevel@tonic-gate 5627c478bd9Sstevel@tonic-gate# Check for ORDER BY clauses that refer to an AS name in the column list 5637c478bd9Sstevel@tonic-gate# 5647c478bd9Sstevel@tonic-gatedo_test select1-10.1 { 5657c478bd9Sstevel@tonic-gate execsql { 5667c478bd9Sstevel@tonic-gate SELECT f1 AS x FROM test1 ORDER BY x 5677c478bd9Sstevel@tonic-gate } 5687c478bd9Sstevel@tonic-gate} {11 33} 5697c478bd9Sstevel@tonic-gatedo_test select1-10.2 { 5707c478bd9Sstevel@tonic-gate execsql { 5717c478bd9Sstevel@tonic-gate SELECT f1 AS x FROM test1 ORDER BY -x 5727c478bd9Sstevel@tonic-gate } 5737c478bd9Sstevel@tonic-gate} {33 11} 5747c478bd9Sstevel@tonic-gatedo_test select1-10.3 { 5757c478bd9Sstevel@tonic-gate execsql { 5767c478bd9Sstevel@tonic-gate SELECT f1-23 AS x FROM test1 ORDER BY abs(x) 5777c478bd9Sstevel@tonic-gate } 5787c478bd9Sstevel@tonic-gate} {10 -12} 5797c478bd9Sstevel@tonic-gatedo_test select1-10.4 { 5807c478bd9Sstevel@tonic-gate execsql { 5817c478bd9Sstevel@tonic-gate SELECT f1-23 AS x FROM test1 ORDER BY -abs(x) 5827c478bd9Sstevel@tonic-gate } 5837c478bd9Sstevel@tonic-gate} {-12 10} 5847c478bd9Sstevel@tonic-gatedo_test select1-10.5 { 5857c478bd9Sstevel@tonic-gate execsql { 5867c478bd9Sstevel@tonic-gate SELECT f1-22 AS x, f2-22 as y FROM test1 5877c478bd9Sstevel@tonic-gate } 5887c478bd9Sstevel@tonic-gate} {-11 0 11 22} 5897c478bd9Sstevel@tonic-gatedo_test select1-10.6 { 5907c478bd9Sstevel@tonic-gate execsql { 5917c478bd9Sstevel@tonic-gate SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50 5927c478bd9Sstevel@tonic-gate } 5937c478bd9Sstevel@tonic-gate} {11 22} 5947c478bd9Sstevel@tonic-gate 5957c478bd9Sstevel@tonic-gate# Check the ability to specify "TABLE.*" in the result set of a SELECT 5967c478bd9Sstevel@tonic-gate# 5977c478bd9Sstevel@tonic-gatedo_test select1-11.1 { 5987c478bd9Sstevel@tonic-gate execsql { 5997c478bd9Sstevel@tonic-gate DELETE FROM t3; 6007c478bd9Sstevel@tonic-gate DELETE FROM t4; 6017c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(1,2); 6027c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(3,4); 6037c478bd9Sstevel@tonic-gate SELECT * FROM t3, t4; 6047c478bd9Sstevel@tonic-gate } 6057c478bd9Sstevel@tonic-gate} {1 2 3 4} 6067c478bd9Sstevel@tonic-gatedo_test select1-11.2 { 6077c478bd9Sstevel@tonic-gate execsql2 { 6087c478bd9Sstevel@tonic-gate SELECT * FROM t3, t4; 6097c478bd9Sstevel@tonic-gate } 6107c478bd9Sstevel@tonic-gate} {t3.a 1 t3.b 2 t4.a 3 t4.b 4} 6117c478bd9Sstevel@tonic-gatedo_test select1-11.3 { 6127c478bd9Sstevel@tonic-gate execsql2 { 6137c478bd9Sstevel@tonic-gate SELECT * FROM t3 AS x, t4 AS y; 6147c478bd9Sstevel@tonic-gate } 6157c478bd9Sstevel@tonic-gate} {x.a 1 x.b 2 y.a 3 y.b 4} 6167c478bd9Sstevel@tonic-gatedo_test select1-11.4.1 { 6177c478bd9Sstevel@tonic-gate execsql { 6187c478bd9Sstevel@tonic-gate SELECT t3.*, t4.b FROM t3, t4; 6197c478bd9Sstevel@tonic-gate } 6207c478bd9Sstevel@tonic-gate} {1 2 4} 6217c478bd9Sstevel@tonic-gatedo_test select1-11.4.2 { 6227c478bd9Sstevel@tonic-gate execsql { 6237c478bd9Sstevel@tonic-gate SELECT "t3".*, t4.b FROM t3, t4; 6247c478bd9Sstevel@tonic-gate } 6257c478bd9Sstevel@tonic-gate} {1 2 4} 6267c478bd9Sstevel@tonic-gatedo_test select1-11.5 { 6277c478bd9Sstevel@tonic-gate execsql2 { 6287c478bd9Sstevel@tonic-gate SELECT t3.*, t4.b FROM t3, t4; 6297c478bd9Sstevel@tonic-gate } 6307c478bd9Sstevel@tonic-gate} {t3.a 1 t3.b 2 t4.b 4} 6317c478bd9Sstevel@tonic-gatedo_test select1-11.6 { 6327c478bd9Sstevel@tonic-gate execsql2 { 6337c478bd9Sstevel@tonic-gate SELECT x.*, y.b FROM t3 AS x, t4 AS y; 6347c478bd9Sstevel@tonic-gate } 6357c478bd9Sstevel@tonic-gate} {x.a 1 x.b 2 y.b 4} 6367c478bd9Sstevel@tonic-gatedo_test select1-11.7 { 6377c478bd9Sstevel@tonic-gate execsql { 6387c478bd9Sstevel@tonic-gate SELECT t3.b, t4.* FROM t3, t4; 6397c478bd9Sstevel@tonic-gate } 6407c478bd9Sstevel@tonic-gate} {2 3 4} 6417c478bd9Sstevel@tonic-gatedo_test select1-11.8 { 6427c478bd9Sstevel@tonic-gate execsql2 { 6437c478bd9Sstevel@tonic-gate SELECT t3.b, t4.* FROM t3, t4; 6447c478bd9Sstevel@tonic-gate } 6457c478bd9Sstevel@tonic-gate} {t3.b 2 t4.a 3 t4.b 4} 6467c478bd9Sstevel@tonic-gatedo_test select1-11.9 { 6477c478bd9Sstevel@tonic-gate execsql2 { 6487c478bd9Sstevel@tonic-gate SELECT x.b, y.* FROM t3 AS x, t4 AS y; 6497c478bd9Sstevel@tonic-gate } 6507c478bd9Sstevel@tonic-gate} {x.b 2 y.a 3 y.b 4} 6517c478bd9Sstevel@tonic-gatedo_test select1-11.10 { 6527c478bd9Sstevel@tonic-gate catchsql { 6537c478bd9Sstevel@tonic-gate SELECT t5.* FROM t3, t4; 6547c478bd9Sstevel@tonic-gate } 6557c478bd9Sstevel@tonic-gate} {1 {no such table: t5}} 6567c478bd9Sstevel@tonic-gatedo_test select1-11.11 { 6577c478bd9Sstevel@tonic-gate catchsql { 6587c478bd9Sstevel@tonic-gate SELECT t3.* FROM t3 AS x, t4; 6597c478bd9Sstevel@tonic-gate } 6607c478bd9Sstevel@tonic-gate} {1 {no such table: t3}} 6617c478bd9Sstevel@tonic-gatedo_test select1-11.12 { 6627c478bd9Sstevel@tonic-gate execsql2 { 6637c478bd9Sstevel@tonic-gate SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4) 6647c478bd9Sstevel@tonic-gate } 6657c478bd9Sstevel@tonic-gate} {t3.a 1 t3.b 2} 6667c478bd9Sstevel@tonic-gatedo_test select1-11.13 { 6677c478bd9Sstevel@tonic-gate execsql2 { 6687c478bd9Sstevel@tonic-gate SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3 6697c478bd9Sstevel@tonic-gate } 6707c478bd9Sstevel@tonic-gate} {t3.a 1 t3.b 2} 6717c478bd9Sstevel@tonic-gatedo_test select1-11.14 { 6727c478bd9Sstevel@tonic-gate execsql2 { 6737c478bd9Sstevel@tonic-gate SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx' 6747c478bd9Sstevel@tonic-gate } 6757c478bd9Sstevel@tonic-gate} {t3.a 1 t3.b 2 tx.max(a) 3 tx.max(b) 4} 6767c478bd9Sstevel@tonic-gatedo_test select1-11.15 { 6777c478bd9Sstevel@tonic-gate execsql2 { 6787c478bd9Sstevel@tonic-gate SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y 6797c478bd9Sstevel@tonic-gate } 6807c478bd9Sstevel@tonic-gate} {y.max(a) 3 y.max(b) 4 t3.a 1 t3.b 2} 6817c478bd9Sstevel@tonic-gatedo_test select1-11.16 { 6827c478bd9Sstevel@tonic-gate execsql2 { 6837c478bd9Sstevel@tonic-gate SELECT y.* FROM t3 as y, t4 as z 6847c478bd9Sstevel@tonic-gate } 6857c478bd9Sstevel@tonic-gate} {y.a 1 y.b 2} 6867c478bd9Sstevel@tonic-gate 6877c478bd9Sstevel@tonic-gate# Tests of SELECT statements without a FROM clause. 6887c478bd9Sstevel@tonic-gate# 6897c478bd9Sstevel@tonic-gatedo_test select1-12.1 { 6907c478bd9Sstevel@tonic-gate execsql2 { 6917c478bd9Sstevel@tonic-gate SELECT 1+2+3 6927c478bd9Sstevel@tonic-gate } 6937c478bd9Sstevel@tonic-gate} {1+2+3 6} 6947c478bd9Sstevel@tonic-gatedo_test select1-12.2 { 6957c478bd9Sstevel@tonic-gate execsql2 { 6967c478bd9Sstevel@tonic-gate SELECT 1,'hello',2 6977c478bd9Sstevel@tonic-gate } 6987c478bd9Sstevel@tonic-gate} {1 1 'hello' hello 2 2} 6997c478bd9Sstevel@tonic-gatedo_test select1-12.3 { 7007c478bd9Sstevel@tonic-gate execsql2 { 7017c478bd9Sstevel@tonic-gate SELECT 1 AS 'a','hello' AS 'b',2 AS 'c' 7027c478bd9Sstevel@tonic-gate } 7037c478bd9Sstevel@tonic-gate} {a 1 b hello c 2} 7047c478bd9Sstevel@tonic-gatedo_test select1-12.4 { 7057c478bd9Sstevel@tonic-gate execsql { 7067c478bd9Sstevel@tonic-gate DELETE FROM t3; 7077c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(1,2); 7087c478bd9Sstevel@tonic-gate SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a; 7097c478bd9Sstevel@tonic-gate } 7107c478bd9Sstevel@tonic-gate} {1 2 3 4} 7117c478bd9Sstevel@tonic-gatedo_test select1-12.5 { 7127c478bd9Sstevel@tonic-gate execsql { 7137c478bd9Sstevel@tonic-gate SELECT 3, 4 UNION SELECT * FROM t3; 7147c478bd9Sstevel@tonic-gate } 7157c478bd9Sstevel@tonic-gate} {1 2 3 4} 7167c478bd9Sstevel@tonic-gatedo_test select1-12.6 { 7177c478bd9Sstevel@tonic-gate execsql { 7187c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a=(SELECT 1); 7197c478bd9Sstevel@tonic-gate } 7207c478bd9Sstevel@tonic-gate} {1 2} 7217c478bd9Sstevel@tonic-gatedo_test select1-12.7 { 7227c478bd9Sstevel@tonic-gate execsql { 7237c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE a=(SELECT 2); 7247c478bd9Sstevel@tonic-gate } 7257c478bd9Sstevel@tonic-gate} {} 7267c478bd9Sstevel@tonic-gatedo_test select1-12.8 { 7277c478bd9Sstevel@tonic-gate execsql2 { 7287c478bd9Sstevel@tonic-gate SELECT x FROM ( 7297c478bd9Sstevel@tonic-gate SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 7307c478bd9Sstevel@tonic-gate ) ORDER BY x; 7317c478bd9Sstevel@tonic-gate } 7327c478bd9Sstevel@tonic-gate} {x 1 x 3} 7337c478bd9Sstevel@tonic-gatedo_test select1-12.9 { 7347c478bd9Sstevel@tonic-gate execsql2 { 7357c478bd9Sstevel@tonic-gate SELECT z.x FROM ( 7367c478bd9Sstevel@tonic-gate SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b 7377c478bd9Sstevel@tonic-gate ) AS 'z' ORDER BY x; 7387c478bd9Sstevel@tonic-gate } 7397c478bd9Sstevel@tonic-gate} {z.x 1 z.x 3} 7407c478bd9Sstevel@tonic-gate 7417c478bd9Sstevel@tonic-gate 7427c478bd9Sstevel@tonic-gatefinish_test 743