# 2015-10-06 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements test cases for the [b65cb2c8d91f6685841d7d1e13b6] # bug: Correct handling of LIMIT and OFFSET on a UNION ALL query where # the right-hand SELECT contains an ORDER BY in a subquery. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !compound { finish_test return } do_execsql_test offset1-1.1 { CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'); CREATE TABLE t2(x,y); INSERT INTO t2 VALUES(8,'y'),(9,'z'),(6,'w'),(7,'x'); SELECT count(*) FROM t1, t2; } {20} do_execsql_test offset1-1.2.0 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 0; } {1 a 2 b 3 c} do_execsql_test offset1-1.2.1 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 1; } {2 b 3 c 4 d} do_execsql_test offset1-1.2.2 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 2; } {3 c 4 d 5 e} do_execsql_test offset1-1.2.3 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 3; } {4 d 5 e 6 w} do_execsql_test offset1-1.2.4 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 4; } {5 e 6 w 7 x} do_execsql_test offset1-1.2.5 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 5; } {6 w 7 x 8 y} do_execsql_test offset1-1.2.6 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 6; } {7 x 8 y 9 z} do_execsql_test offset1-1.2.7 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 7; } {8 y 9 z} do_execsql_test offset1-1.2.8 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 8; } {9 z} do_execsql_test offset1-1.2.9 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 9; } {} do_execsql_test offset1-1.3.0 { SELECT * FROM t1 LIMIT 0; } {} do_execsql_test offset1-1.4.0 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 0 OFFSET 1; } {} do_execsql_test offset1-1.4.1 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 1 OFFSET 1; } {2 b} do_execsql_test offset1-1.4.2 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 2 OFFSET 1; } {2 b 3 c} do_execsql_test offset1-1.4.3 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 3 OFFSET 1; } {2 b 3 c 4 d} do_execsql_test offset1-1.4.4 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 4 OFFSET 1; } {2 b 3 c 4 d 5 e} do_execsql_test offset1-1.4.5 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 5 OFFSET 1; } {2 b 3 c 4 d 5 e 6 w} do_execsql_test offset1-1.4.6 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 6 OFFSET 1; } {2 b 3 c 4 d 5 e 6 w 7 x} do_execsql_test offset1-1.4.7 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 7 OFFSET 1; } {2 b 3 c 4 d 5 e 6 w 7 x 8 y} do_execsql_test offset1-1.4.8 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 8 OFFSET 1; } {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z} do_execsql_test offset1-1.4.9 { SELECT a, b FROM t1 UNION ALL SELECT * FROM (SELECT x, y FROM t2 ORDER BY y) LIMIT 9 OFFSET 1; } {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z} finish_test