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