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 CREATE TABLE statement. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: sort.test,v 1.9 2003/04/18 17:45:15 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 bunch of data to sort against 217c478bd9Sstevel@tonic-gate# 227c478bd9Sstevel@tonic-gatedo_test sort-1.0 { 237c478bd9Sstevel@tonic-gate set fd [open data.txt w] 247c478bd9Sstevel@tonic-gate puts $fd "1\tone\t0\tI\t3.141592653" 257c478bd9Sstevel@tonic-gate puts $fd "2\ttwo\t1\tII\t2.15" 267c478bd9Sstevel@tonic-gate puts $fd "3\tthree\t1\tIII\t4221.0" 277c478bd9Sstevel@tonic-gate puts $fd "4\tfour\t2\tIV\t-0.0013442" 287c478bd9Sstevel@tonic-gate puts $fd "5\tfive\t2\tV\t-11" 297c478bd9Sstevel@tonic-gate puts $fd "6\tsix\t2\tVI\t0.123" 307c478bd9Sstevel@tonic-gate puts $fd "7\tseven\t2\tVII\t123.0" 317c478bd9Sstevel@tonic-gate puts $fd "8\teight\t3\tVIII\t-1.6" 327c478bd9Sstevel@tonic-gate close $fd 337c478bd9Sstevel@tonic-gate execsql { 347c478bd9Sstevel@tonic-gate CREATE TABLE t1( 357c478bd9Sstevel@tonic-gate n int, 367c478bd9Sstevel@tonic-gate v varchar(10), 377c478bd9Sstevel@tonic-gate log int, 387c478bd9Sstevel@tonic-gate roman varchar(10), 397c478bd9Sstevel@tonic-gate flt real 407c478bd9Sstevel@tonic-gate ); 417c478bd9Sstevel@tonic-gate COPY t1 FROM 'data.txt' 427c478bd9Sstevel@tonic-gate } 437c478bd9Sstevel@tonic-gate file delete data.txt 447c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM t1} 457c478bd9Sstevel@tonic-gate} {8} 467c478bd9Sstevel@tonic-gate 477c478bd9Sstevel@tonic-gatedo_test sort-1.1 { 487c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY n} 497c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 507c478bd9Sstevel@tonic-gatedo_test sort-1.1.1 { 517c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY n ASC} 527c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 537c478bd9Sstevel@tonic-gatedo_test sort-1.1.1 { 547c478bd9Sstevel@tonic-gate execsql {SELECT ALL n FROM t1 ORDER BY n ASC} 557c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8} 567c478bd9Sstevel@tonic-gatedo_test sort-1.2 { 577c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY n DESC} 587c478bd9Sstevel@tonic-gate} {8 7 6 5 4 3 2 1} 597c478bd9Sstevel@tonic-gatedo_test sort-1.3a { 607c478bd9Sstevel@tonic-gate execsql {SELECT v FROM t1 ORDER BY v} 617c478bd9Sstevel@tonic-gate} {eight five four one seven six three two} 627c478bd9Sstevel@tonic-gatedo_test sort-1.3b { 637c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY v} 647c478bd9Sstevel@tonic-gate} {8 5 4 1 7 6 3 2} 657c478bd9Sstevel@tonic-gatedo_test sort-1.4 { 667c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY v DESC} 677c478bd9Sstevel@tonic-gate} {2 3 6 7 1 4 5 8} 687c478bd9Sstevel@tonic-gatedo_test sort-1.5 { 697c478bd9Sstevel@tonic-gate execsql {SELECT flt FROM t1 ORDER BY flt} 707c478bd9Sstevel@tonic-gate} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0} 717c478bd9Sstevel@tonic-gatedo_test sort-1.6 { 727c478bd9Sstevel@tonic-gate execsql {SELECT flt FROM t1 ORDER BY flt DESC} 737c478bd9Sstevel@tonic-gate} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11} 747c478bd9Sstevel@tonic-gatedo_test sort-1.7 { 757c478bd9Sstevel@tonic-gate execsql {SELECT roman FROM t1 ORDER BY roman} 767c478bd9Sstevel@tonic-gate} {I II III IV V VI VII VIII} 777c478bd9Sstevel@tonic-gatedo_test sort-1.8 { 787c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log, flt} 797c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 807c478bd9Sstevel@tonic-gatedo_test sort-1.8.1 { 817c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log asc, flt} 827c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 837c478bd9Sstevel@tonic-gatedo_test sort-1.8.2 { 847c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log, flt ASC} 857c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 867c478bd9Sstevel@tonic-gatedo_test sort-1.8.3 { 877c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc} 887c478bd9Sstevel@tonic-gate} {1 2 3 5 4 6 7 8} 897c478bd9Sstevel@tonic-gatedo_test sort-1.9 { 907c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log, flt DESC} 917c478bd9Sstevel@tonic-gate} {1 3 2 7 6 4 5 8} 927c478bd9Sstevel@tonic-gatedo_test sort-1.9.1 { 937c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC} 947c478bd9Sstevel@tonic-gate} {1 3 2 7 6 4 5 8} 957c478bd9Sstevel@tonic-gatedo_test sort-1.10 { 967c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log DESC, flt} 977c478bd9Sstevel@tonic-gate} {8 5 4 6 7 2 3 1} 987c478bd9Sstevel@tonic-gatedo_test sort-1.11 { 997c478bd9Sstevel@tonic-gate execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC} 1007c478bd9Sstevel@tonic-gate} {8 7 6 4 5 3 2 1} 1017c478bd9Sstevel@tonic-gate 1027c478bd9Sstevel@tonic-gate# These tests are designed to reach some hard-to-reach places 1037c478bd9Sstevel@tonic-gate# inside the string comparison routines. 1047c478bd9Sstevel@tonic-gate# 1057c478bd9Sstevel@tonic-gate# (Later) The sorting behavior changed in 2.7.0. But we will 1067c478bd9Sstevel@tonic-gate# keep these tests. You can never have too many test cases! 1077c478bd9Sstevel@tonic-gate# 1087c478bd9Sstevel@tonic-gatedo_test sort-2.1.1 { 1097c478bd9Sstevel@tonic-gate execsql { 1107c478bd9Sstevel@tonic-gate UPDATE t1 SET v='x' || -flt; 1117c478bd9Sstevel@tonic-gate UPDATE t1 SET v='x-2b' where v=='x-0.123'; 1127c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY v; 1137c478bd9Sstevel@tonic-gate } 1147c478bd9Sstevel@tonic-gate} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11} 1157c478bd9Sstevel@tonic-gatedo_test sort-2.1.2 { 1167c478bd9Sstevel@tonic-gate execsql { 1177c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999); 1187c478bd9Sstevel@tonic-gate } 1197c478bd9Sstevel@tonic-gate} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11} 1207c478bd9Sstevel@tonic-gatedo_test sort-2.1.3 { 1217c478bd9Sstevel@tonic-gate execsql { 1227c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0; 1237c478bd9Sstevel@tonic-gate } 1247c478bd9Sstevel@tonic-gate} {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11} 1257c478bd9Sstevel@tonic-gatedo_test sort-2.1.4 { 1267c478bd9Sstevel@tonic-gate execsql { 1277c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999) DESC; 1287c478bd9Sstevel@tonic-gate } 1297c478bd9Sstevel@tonic-gate} {x11 x1.6 x0.0013442 x-4221 x-3.141592653 x-2b x-2.15 x-123} 1307c478bd9Sstevel@tonic-gatedo_test sort-2.1.5 { 1317c478bd9Sstevel@tonic-gate execsql { 1327c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC; 1337c478bd9Sstevel@tonic-gate } 1347c478bd9Sstevel@tonic-gate} {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221} 1357c478bd9Sstevel@tonic-gate 1367c478bd9Sstevel@tonic-gate# This is a bug fix for 2.2.4. 1377c478bd9Sstevel@tonic-gate# Strings are normally mapped to upper-case for a caseless comparison. 1387c478bd9Sstevel@tonic-gate# But this can cause problems for characters in between 'Z' and 'a'. 1397c478bd9Sstevel@tonic-gate# 1407c478bd9Sstevel@tonic-gatedo_test sort-3.1 { 1417c478bd9Sstevel@tonic-gate execsql { 1427c478bd9Sstevel@tonic-gate CREATE TABLE t2(a,b); 1437c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('AGLIENTU',1); 1447c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('AGLIE`',2); 1457c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('AGNA',3); 1467c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a; 1477c478bd9Sstevel@tonic-gate } 1487c478bd9Sstevel@tonic-gate} {AGLIENTU 1 AGLIE` 2 AGNA 3} 1497c478bd9Sstevel@tonic-gatedo_test sort-3.2 { 1507c478bd9Sstevel@tonic-gate execsql { 1517c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a DESC; 1527c478bd9Sstevel@tonic-gate } 1537c478bd9Sstevel@tonic-gate} {AGNA 3 AGLIE` 2 AGLIENTU 1} 1547c478bd9Sstevel@tonic-gatedo_test sort-3.3 { 1557c478bd9Sstevel@tonic-gate execsql { 1567c478bd9Sstevel@tonic-gate DELETE FROM t2; 1577c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('aglientu',1); 1587c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('aglie`',2); 1597c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES('agna',3); 1607c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a; 1617c478bd9Sstevel@tonic-gate } 1627c478bd9Sstevel@tonic-gate} {aglie` 2 aglientu 1 agna 3} 1637c478bd9Sstevel@tonic-gatedo_test sort-3.4 { 1647c478bd9Sstevel@tonic-gate execsql { 1657c478bd9Sstevel@tonic-gate SELECT a, b FROM t2 ORDER BY a DESC; 1667c478bd9Sstevel@tonic-gate } 1677c478bd9Sstevel@tonic-gate} {agna 3 aglientu 1 aglie` 2} 1687c478bd9Sstevel@tonic-gate 1697c478bd9Sstevel@tonic-gate# Version 2.7.0 testing. 1707c478bd9Sstevel@tonic-gate# 1717c478bd9Sstevel@tonic-gatedo_test sort-4.1 { 1727c478bd9Sstevel@tonic-gate execsql { 1737c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5); 1747c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5); 1757c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4); 1767c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3); 1777c478bd9Sstevel@tonic-gate SELECT n FROM t1 ORDER BY n; 1787c478bd9Sstevel@tonic-gate } 1797c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9 10 11 12} 1807c478bd9Sstevel@tonic-gatedo_test sort-4.2 { 1817c478bd9Sstevel@tonic-gate execsql { 1827c478bd9Sstevel@tonic-gate SELECT n||'' FROM t1 ORDER BY 1; 1837c478bd9Sstevel@tonic-gate } 1847c478bd9Sstevel@tonic-gate} {1 10 11 12 2 3 4 5 6 7 8 9} 1857c478bd9Sstevel@tonic-gatedo_test sort-4.3 { 1867c478bd9Sstevel@tonic-gate execsql { 1877c478bd9Sstevel@tonic-gate SELECT n+0 FROM t1 ORDER BY 1; 1887c478bd9Sstevel@tonic-gate } 1897c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9 10 11 12} 1907c478bd9Sstevel@tonic-gatedo_test sort-4.4 { 1917c478bd9Sstevel@tonic-gate execsql { 1927c478bd9Sstevel@tonic-gate SELECT n||'' FROM t1 ORDER BY 1 DESC; 1937c478bd9Sstevel@tonic-gate } 1947c478bd9Sstevel@tonic-gate} {9 8 7 6 5 4 3 2 12 11 10 1} 1957c478bd9Sstevel@tonic-gatedo_test sort-4.5 { 1967c478bd9Sstevel@tonic-gate execsql { 1977c478bd9Sstevel@tonic-gate SELECT n+0 FROM t1 ORDER BY 1 DESC; 1987c478bd9Sstevel@tonic-gate } 1997c478bd9Sstevel@tonic-gate} {12 11 10 9 8 7 6 5 4 3 2 1} 2007c478bd9Sstevel@tonic-gatedo_test sort-4.6 { 2017c478bd9Sstevel@tonic-gate execsql { 2027c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY 1; 2037c478bd9Sstevel@tonic-gate } 2047c478bd9Sstevel@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} 2057c478bd9Sstevel@tonic-gatedo_test sort-4.7 { 2067c478bd9Sstevel@tonic-gate execsql { 2077c478bd9Sstevel@tonic-gate SELECT v FROM t1 ORDER BY 1 DESC; 2087c478bd9Sstevel@tonic-gate } 2097c478bd9Sstevel@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} 2107c478bd9Sstevel@tonic-gatedo_test sort-4.8 { 2117c478bd9Sstevel@tonic-gate execsql { 2127c478bd9Sstevel@tonic-gate SELECT substr(v,2,99) FROM t1 ORDER BY 1; 2137c478bd9Sstevel@tonic-gate } 2147c478bd9Sstevel@tonic-gate} {-123 -2.15 -2b -3.141592653 -4.0e9 -4221 0.0013442 01234567890123456789 1.6 11 2.7 5.0e10} 2157c478bd9Sstevel@tonic-gate#do_test sort-4.9 { 2167c478bd9Sstevel@tonic-gate# execsql { 2177c478bd9Sstevel@tonic-gate# SELECT substr(v,2,99)+0.0 FROM t1 ORDER BY 1; 2187c478bd9Sstevel@tonic-gate# } 2197c478bd9Sstevel@tonic-gate#} {-4000000000 -4221 -123 -3.141592653 -2.15 -2 0.0013442 1.6 2.7 11 50000000000 1.23456789012346e+18} 2207c478bd9Sstevel@tonic-gate 2217c478bd9Sstevel@tonic-gatedo_test sort-5.1 { 2227c478bd9Sstevel@tonic-gate execsql { 2237c478bd9Sstevel@tonic-gate create table t3(a,b); 2247c478bd9Sstevel@tonic-gate insert into t3 values(5,NULL); 2257c478bd9Sstevel@tonic-gate insert into t3 values(6,NULL); 2267c478bd9Sstevel@tonic-gate insert into t3 values(3,NULL); 2277c478bd9Sstevel@tonic-gate insert into t3 values(4,'cd'); 2287c478bd9Sstevel@tonic-gate insert into t3 values(1,'ab'); 2297c478bd9Sstevel@tonic-gate insert into t3 values(2,NULL); 2307c478bd9Sstevel@tonic-gate select a from t3 order by b, a; 2317c478bd9Sstevel@tonic-gate } 2327c478bd9Sstevel@tonic-gate} {2 3 5 6 1 4} 2337c478bd9Sstevel@tonic-gatedo_test sort-5.2 { 2347c478bd9Sstevel@tonic-gate execsql { 2357c478bd9Sstevel@tonic-gate select a from t3 order by b, a desc; 2367c478bd9Sstevel@tonic-gate } 2377c478bd9Sstevel@tonic-gate} {6 5 3 2 1 4} 2387c478bd9Sstevel@tonic-gatedo_test sort-5.3 { 2397c478bd9Sstevel@tonic-gate execsql { 2407c478bd9Sstevel@tonic-gate select a from t3 order by b desc, a; 2417c478bd9Sstevel@tonic-gate } 2427c478bd9Sstevel@tonic-gate} {4 1 2 3 5 6} 2437c478bd9Sstevel@tonic-gatedo_test sort-5.4 { 2447c478bd9Sstevel@tonic-gate execsql { 2457c478bd9Sstevel@tonic-gate select a from t3 order by b desc, a desc; 2467c478bd9Sstevel@tonic-gate } 2477c478bd9Sstevel@tonic-gate} {4 1 6 5 3 2} 2487c478bd9Sstevel@tonic-gate 2497c478bd9Sstevel@tonic-gatedo_test sort-6.1 { 2507c478bd9Sstevel@tonic-gate execsql { 2517c478bd9Sstevel@tonic-gate create index i3 on t3(b,a); 2527c478bd9Sstevel@tonic-gate select a from t3 order by b, a; 2537c478bd9Sstevel@tonic-gate } 2547c478bd9Sstevel@tonic-gate} {2 3 5 6 1 4} 2557c478bd9Sstevel@tonic-gatedo_test sort-6.2 { 2567c478bd9Sstevel@tonic-gate execsql { 2577c478bd9Sstevel@tonic-gate select a from t3 order by b, a desc; 2587c478bd9Sstevel@tonic-gate } 2597c478bd9Sstevel@tonic-gate} {6 5 3 2 1 4} 2607c478bd9Sstevel@tonic-gatedo_test sort-6.3 { 2617c478bd9Sstevel@tonic-gate execsql { 2627c478bd9Sstevel@tonic-gate select a from t3 order by b desc, a; 2637c478bd9Sstevel@tonic-gate } 2647c478bd9Sstevel@tonic-gate} {4 1 2 3 5 6} 2657c478bd9Sstevel@tonic-gatedo_test sort-6.4 { 2667c478bd9Sstevel@tonic-gate execsql { 2677c478bd9Sstevel@tonic-gate select a from t3 order by b desc, a desc; 2687c478bd9Sstevel@tonic-gate } 2697c478bd9Sstevel@tonic-gate} {4 1 6 5 3 2} 2707c478bd9Sstevel@tonic-gate 2717c478bd9Sstevel@tonic-gatedo_test sort-7.1 { 2727c478bd9Sstevel@tonic-gate execsql { 2737c478bd9Sstevel@tonic-gate CREATE TABLE t4( 2747c478bd9Sstevel@tonic-gate a INTEGER, 2757c478bd9Sstevel@tonic-gate b VARCHAR(30) 2767c478bd9Sstevel@tonic-gate ); 2777c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(1,1); 2787c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(2,2); 2797c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(11,11); 2807c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(12,12); 2817c478bd9Sstevel@tonic-gate SELECT a FROM t4 ORDER BY 1; 2827c478bd9Sstevel@tonic-gate } 2837c478bd9Sstevel@tonic-gate} {1 2 11 12} 2847c478bd9Sstevel@tonic-gatedo_test sort-7.2 { 2857c478bd9Sstevel@tonic-gate execsql { 2867c478bd9Sstevel@tonic-gate SELECT b FROM t4 ORDER BY 1 2877c478bd9Sstevel@tonic-gate } 2887c478bd9Sstevel@tonic-gate} {1 11 12 2} 2897c478bd9Sstevel@tonic-gatedo_test sort-7.3 { 2907c478bd9Sstevel@tonic-gate execsql { 2917c478bd9Sstevel@tonic-gate CREATE VIEW v4 AS SELECT * FROM t4; 2927c478bd9Sstevel@tonic-gate SELECT a FROM v4 ORDER BY 1; 2937c478bd9Sstevel@tonic-gate } 2947c478bd9Sstevel@tonic-gate} {1 2 11 12} 2957c478bd9Sstevel@tonic-gatedo_test sort-7.4 { 2967c478bd9Sstevel@tonic-gate execsql { 2977c478bd9Sstevel@tonic-gate SELECT b FROM v4 ORDER BY 1; 2987c478bd9Sstevel@tonic-gate } 2997c478bd9Sstevel@tonic-gate} {1 11 12 2} 3007c478bd9Sstevel@tonic-gatedo_test sort-7.5 { 3017c478bd9Sstevel@tonic-gate execsql { 3027c478bd9Sstevel@tonic-gate SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1; 3037c478bd9Sstevel@tonic-gate } 3047c478bd9Sstevel@tonic-gate} {1 2 11 12} 3057c478bd9Sstevel@tonic-gatedo_test sort-7.6 { 3067c478bd9Sstevel@tonic-gate execsql { 3077c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1; 3087c478bd9Sstevel@tonic-gate } 3097c478bd9Sstevel@tonic-gate} {1 2 11 12} 3107c478bd9Sstevel@tonic-gatedo_test sort-7.7 { 3117c478bd9Sstevel@tonic-gate execsql { 3127c478bd9Sstevel@tonic-gate SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1; 3137c478bd9Sstevel@tonic-gate } 3147c478bd9Sstevel@tonic-gate} {1 2 11 12} 3157c478bd9Sstevel@tonic-gatedo_test sort-7.8 { 3167c478bd9Sstevel@tonic-gate execsql { 3177c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1; 3187c478bd9Sstevel@tonic-gate } 3197c478bd9Sstevel@tonic-gate} {1 11 12 2} 3207c478bd9Sstevel@tonic-gatedo_test sort-7.9 { 3217c478bd9Sstevel@tonic-gate execsql { 3227c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric; 3237c478bd9Sstevel@tonic-gate } 3247c478bd9Sstevel@tonic-gate} {1 2 11 12} 3257c478bd9Sstevel@tonic-gatedo_test sort-7.10 { 3267c478bd9Sstevel@tonic-gate execsql { 3277c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer; 3287c478bd9Sstevel@tonic-gate } 3297c478bd9Sstevel@tonic-gate} {1 2 11 12} 3307c478bd9Sstevel@tonic-gatedo_test sort-7.11 { 3317c478bd9Sstevel@tonic-gate execsql { 3327c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text; 3337c478bd9Sstevel@tonic-gate } 3347c478bd9Sstevel@tonic-gate} {1 11 12 2} 3357c478bd9Sstevel@tonic-gatedo_test sort-7.12 { 3367c478bd9Sstevel@tonic-gate execsql { 3377c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE blob; 3387c478bd9Sstevel@tonic-gate } 3397c478bd9Sstevel@tonic-gate} {1 11 12 2} 3407c478bd9Sstevel@tonic-gatedo_test sort-7.13 { 3417c478bd9Sstevel@tonic-gate execsql { 3427c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE clob; 3437c478bd9Sstevel@tonic-gate } 3447c478bd9Sstevel@tonic-gate} {1 11 12 2} 3457c478bd9Sstevel@tonic-gatedo_test sort-7.14 { 3467c478bd9Sstevel@tonic-gate execsql { 3477c478bd9Sstevel@tonic-gate SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE varchar; 3487c478bd9Sstevel@tonic-gate } 3497c478bd9Sstevel@tonic-gate} {1 11 12 2} 3507c478bd9Sstevel@tonic-gate 3517c478bd9Sstevel@tonic-gate# Ticket #297 3527c478bd9Sstevel@tonic-gate# 3537c478bd9Sstevel@tonic-gatedo_test sort-8.1 { 3547c478bd9Sstevel@tonic-gate execsql { 3557c478bd9Sstevel@tonic-gate CREATE TABLE t5(a real, b text); 3567c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(100,'A1'); 3577c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(100.0,'A2'); 3587c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY a, b; 3597c478bd9Sstevel@tonic-gate } 3607c478bd9Sstevel@tonic-gate} {100 A1 100.0 A2} 3617c478bd9Sstevel@tonic-gate 3627c478bd9Sstevel@tonic-gatefinish_test 363