-- original: descidx1.test -- credit: http://www.sqlite.org/src/tree?ci=trunk&name=test PRAGMA legacy_file_format=OFF ;CREATE TABLE t1(a,b); CREATE INDEX i1 ON t1(b ASC) ;CREATE INDEX i2 ON t1(a DESC) ;INSERT INTO t1 VALUES(1,1); INSERT INTO t1 VALUES(2,2); INSERT INTO t1 SELECT a+2, a+2 FROM t1; INSERT INTO t1 SELECT a+4, a+4 FROM t1; SELECT b FROM t1 WHERE a>3 AND a<7 ;SELECT a FROM t1 WHERE b>3 AND b<7 ;SELECT b FROM t1 WHERE a>=3 AND a<7 ;SELECT b FROM t1 WHERE a>3 AND a<=7 ;SELECT b FROM t1 WHERE a>=3 AND a<=7 ;SELECT a FROM t1 WHERE b>=3 AND b<=7 ;CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL); CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC); CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC); INSERT INTO t2 VALUES(1,'one',x'31',1.0); INSERT INTO t2 VALUES(2,'two',x'3232',2.0); INSERT INTO t2 VALUES(3,'three',x'333333',3.0); INSERT INTO t2 VALUES(4,'four',x'34343434',4.0); INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0); INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0); INSERT INTO t2 VALUES(2,'two',x'323232',2.1); INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2); INSERT INTO t2 VALUES(2,NULL,NULL,2.3); SELECT count(*) FROM t2 ;SELECT d FROM t2 ORDER BY a ;SELECT d FROM t2 WHERE a>=2 ORDER BY a ;SELECT d FROM t2 WHERE a>2 ORDER BY a ;SELECT d FROM t2 WHERE a=2 AND b>'two' ;SELECT d FROM t2 WHERE a=2 AND b>='two' ;SELECT d FROM t2 WHERE a=2 AND b<'two' ;SELECT d FROM t2 WHERE a=2 AND b<='two' ;CREATE TABLE t3(a,b,c,d); CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC); INSERT INTO t3 VALUES(0,0,0,0); INSERT INTO t3 VALUES(0,0,0,1); INSERT INTO t3 VALUES(0,0,1,0); INSERT INTO t3 VALUES(0,0,1,1); INSERT INTO t3 VALUES(0,1,0,0); INSERT INTO t3 VALUES(0,1,0,1); INSERT INTO t3 VALUES(0,1,1,0); INSERT INTO t3 VALUES(0,1,1,1); INSERT INTO t3 VALUES(1,0,0,0); INSERT INTO t3 VALUES(1,0,0,1); INSERT INTO t3 VALUES(1,0,1,0); INSERT INTO t3 VALUES(1,0,1,1); INSERT INTO t3 VALUES(1,1,0,0); INSERT INTO t3 VALUES(1,1,0,1); INSERT INTO t3 VALUES(1,1,1,0); INSERT INTO t3 VALUES(1,1,1,1); SELECT count(*) FROM t3 ;PRAGMA legacy_file_format ;PRAGMA legacy_file_format ;PRAGMA legacy_file_format=YES ;PRAGMA legacy_file_format ;CREATE TABLE t1(a,b,c) ;VACUUM ;PRAGMA legacy_file_format=NO ;PRAGMA legacy_file_format ;CREATE TABLE t1(a,b,c); CREATE INDEX i1 ON t1(a ASC, b DESC, c ASC); INSERT INTO t1 VALUES(1,2,3); INSERT INTO t1 VALUES(1,1,0); INSERT INTO t1 VALUES(1,2,1); INSERT INTO t1 VALUES(1,3,4) ;VACUUM ;PRAGMA legacy_file_format=ON; VACUUM;