| # 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 |