-- original: misc3.test -- credit: http://www.sqlite.org/src/tree?ci=trunk&name=test CREATE TABLE t1(a UNIQUE,b); INSERT INTO t1 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); UPDATE t1 SET b=b||b; UPDATE t1 SET b=b||b; UPDATE t1 SET b=b||b; UPDATE t1 SET b=b||b; UPDATE t1 SET b=b||b; INSERT INTO t1 VALUES(2,'x'); UPDATE t1 SET b=substr(b,1,500); BEGIN ;CREATE TABLE t2(x,y); COMMIT; PRAGMA integrity_check ;DROP TABLE t1; DROP TABLE t2 ;CREATE TABLE t1(a UNIQUE,b); INSERT INTO t1 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_'); INSERT INTO t1 SELECT a+1, b||b FROM t1; INSERT INTO t1 SELECT a+2, b||b FROM t1; INSERT INTO t1 SELECT a+4, b FROM t1; INSERT INTO t1 SELECT a+8, b FROM t1; INSERT INTO t1 SELECT a+16, b FROM t1; INSERT INTO t1 SELECT a+32, b FROM t1; INSERT INTO t1 SELECT a+64, b FROM t1; BEGIN ;INSERT INTO t1 VALUES(200,'hello out there'); COMMIT; PRAGMA integrity_check ;SELECT 2e-25*0.5e25 ;SELECT 2.0e-25*000000.500000000000000000000000000000e+00025 ;SELECT 000000000002e-0000000025*0.5e25 ;SELECT 2e-25*0.5e250 ;SELECT 2.0e-250*0.5e25 ;SELECT '-2.0e-127' * '-0.5e27' ;SELECT '+2.0e-127' * '-0.5e27' ;SELECT 2.0e-27 * '+0.5e+127' ;SELECT 2.0e-27 * '+0.000005e+132' ;CREATE TABLE t2(a INT UNIQUE) ;INSERT INTO t2 VALUES(2147483648) ;INSERT INTO t2 VALUES(-2147483649) ;INSERT INTO t2 VALUES(+2147483649) ;INSERT INTO t2 VALUES(+2147483647); INSERT INTO t2 VALUES(-2147483648); INSERT INTO t2 VALUES(-2147483647); INSERT INTO t2 VALUES(2147483646); SELECT * FROM t2 ORDER BY a ;SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a ;SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a ;SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a ;SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC ;SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC ;SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC ;SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC ;SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC ;CREATE TABLE t3(a INTEGER PRIMARY KEY, b); INSERT INTO t3(b) VALUES('abc'); INSERT INTO t3(b) VALUES('xyz'); INSERT INTO t3(b) VALUES(NULL); INSERT INTO t3(b) VALUES(NULL); INSERT INTO t3(b) SELECT b||'d' FROM t3; INSERT INTO t3(b) SELECT b||'e' FROM t3; INSERT INTO t3(b) SELECT b||'f' FROM t3; INSERT INTO t3(b) SELECT b||'g' FROM t3; INSERT INTO t3(b) SELECT b||'h' FROM t3; SELECT count(a), count(b) FROM t3 ;SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3) ;SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1) ;CREATE TABLE x1 (b, c); INSERT INTO x1 VALUES('dog',3); INSERT INTO x1 VALUES('cat',1); INSERT INTO x1 VALUES('dog',4); CREATE TABLE x2 (c, e); INSERT INTO x2 VALUES(1,'one'); INSERT INTO x2 VALUES(2,'two'); INSERT INTO x2 VALUES(3,'three'); INSERT INTO x2 VALUES(4,'four'); SELECT x2.c AS c, e, b FROM x2 LEFT JOIN (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) USING(c) ;SELECT * FROM ( SELECT x2.c AS c, e, b FROM x2 LEFT JOIN (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b) USING(c) ) ;EXPLAIN BEGIN ;EXPLAIN COMMIT ;BEGIN; EXPLAIN ROLLBACK ;CREATE TABLE ex1( a INTEGER DEFAULT 54321, b TEXT DEFAULT "hello", c REAL DEFAULT 3.1415926 ); CREATE UNIQUE INDEX ex1i1 ON ex1(a); EXPLAIN REINDEX ;EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC ;EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC ;BEGIN; CREATE TABLE y1(a); CREATE TABLE y2(b); CREATE TABLE y3(c); CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; END; INSERT INTO y1 VALUES(1); INSERT INTO y1 VALUES(2); INSERT INTO y1 SELECT a+2 FROM y1; INSERT INTO y1 SELECT a+4 FROM y1; INSERT INTO y1 SELECT a+8 FROM y1; INSERT INTO y1 SELECT a+16 FROM y1; INSERT INTO y2 SELECT a FROM y1; COMMIT; SELECT count(*) FROM y1 ;DELETE FROM y1; SELECT count(*) FROM y1 ;SELECT count(*) FROM y3 ;SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 ;SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2;