AryaWu/sqlite

1# 2015-10-062#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# 12# This file implements test cases for the [b65cb2c8d91f6685841d7d1e13b6]13# bug:  Correct handling of LIMIT and OFFSET on a UNION ALL query where14# the right-hand SELECT contains an ORDER BY in a subquery.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable !compound {21  finish_test22  return23}24 25do_execsql_test offset1-1.1 {26  CREATE TABLE t1(a,b);27  INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');28  CREATE TABLE t2(x,y);29  INSERT INTO t2 VALUES(8,'y'),(9,'z'),(6,'w'),(7,'x');30  SELECT count(*) FROM t1, t2;31} {20}32 33do_execsql_test offset1-1.2.0 {34  SELECT a, b FROM t135  UNION ALL36  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)37  LIMIT 3 OFFSET 0;38} {1 a 2 b 3 c}39do_execsql_test offset1-1.2.1 {40  SELECT a, b FROM t141  UNION ALL42  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)43  LIMIT 3 OFFSET 1;44} {2 b 3 c 4 d}45do_execsql_test offset1-1.2.2 {46  SELECT a, b FROM t147  UNION ALL48  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)49  LIMIT 3 OFFSET 2;50} {3 c 4 d 5 e}51do_execsql_test offset1-1.2.3 {52  SELECT a, b FROM t153  UNION ALL54  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)55  LIMIT 3 OFFSET 3;56} {4 d 5 e 6 w}57do_execsql_test offset1-1.2.4 {58  SELECT a, b FROM t159  UNION ALL60  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)61  LIMIT 3 OFFSET 4;62} {5 e 6 w 7 x}63do_execsql_test offset1-1.2.5 {64  SELECT a, b FROM t165  UNION ALL66  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)67  LIMIT 3 OFFSET 5;68} {6 w 7 x 8 y}69do_execsql_test offset1-1.2.6 {70  SELECT a, b FROM t171  UNION ALL72  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)73  LIMIT 3 OFFSET 6;74} {7 x 8 y 9 z}75do_execsql_test offset1-1.2.7 {76  SELECT a, b FROM t177  UNION ALL78  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)79  LIMIT 3 OFFSET 7;80} {8 y 9 z}81do_execsql_test offset1-1.2.8 {82  SELECT a, b FROM t183  UNION ALL84  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)85  LIMIT 3 OFFSET 8;86} {9 z}87do_execsql_test offset1-1.2.9 {88  SELECT a, b FROM t189  UNION ALL90  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)91  LIMIT 3 OFFSET 9;92} {}93 94do_execsql_test offset1-1.3.0 {95  SELECT * FROM t1 LIMIT 0;96} {}97 98do_execsql_test offset1-1.4.0 {99  SELECT a, b FROM t1100  UNION ALL101  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)102  LIMIT 0 OFFSET 1;103} {}104do_execsql_test offset1-1.4.1 {105  SELECT a, b FROM t1106  UNION ALL107  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)108  LIMIT 1 OFFSET 1;109} {2 b}110do_execsql_test offset1-1.4.2 {111  SELECT a, b FROM t1112  UNION ALL113  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)114  LIMIT 2 OFFSET 1;115} {2 b 3 c}116do_execsql_test offset1-1.4.3 {117  SELECT a, b FROM t1118  UNION ALL119  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)120  LIMIT 3 OFFSET 1;121} {2 b 3 c 4 d}122do_execsql_test offset1-1.4.4 {123  SELECT a, b FROM t1124  UNION ALL125  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)126  LIMIT 4 OFFSET 1;127} {2 b 3 c 4 d 5 e}128do_execsql_test offset1-1.4.5 {129  SELECT a, b FROM t1130  UNION ALL131  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)132  LIMIT 5 OFFSET 1;133} {2 b 3 c 4 d 5 e 6 w}134do_execsql_test offset1-1.4.6 {135  SELECT a, b FROM t1136  UNION ALL137  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)138  LIMIT 6 OFFSET 1;139} {2 b 3 c 4 d 5 e 6 w 7 x}140do_execsql_test offset1-1.4.7 {141  SELECT a, b FROM t1142  UNION ALL143  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)144  LIMIT 7 OFFSET 1;145} {2 b 3 c 4 d 5 e 6 w 7 x 8 y}146do_execsql_test offset1-1.4.8 {147  SELECT a, b FROM t1148  UNION ALL149  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)150  LIMIT 8 OFFSET 1;151} {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}152do_execsql_test offset1-1.4.9 {153  SELECT a, b FROM t1154  UNION ALL155  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)156  LIMIT 9 OFFSET 1;157} {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}158 159# 2022-08-04160# https://sqlite.org/forum/forumpost/6b5e9188f0657616161#162do_execsql_test offset1-2.0 {163  CREATE TABLE employees (164    id integer primary key,165    name text,166    city text,167    department text,168    salary integer169  );170  INSERT INTO employees VALUES171    (11,'Diane','London','hr',70),172    (12,'Bob','London','hr',78),173    (21,'Emma','London','it',84),174    (22,'Grace','Berlin','it',90),175    (23,'Henry','London','it',104),176    (24,'Irene','Berlin','it',104),177    (25,'Frank','Berlin','it',120),178    (31,'Cindy','Berlin','sales',96),179    (32,'Dave','London','sales',96),180    (33,'Alice','Berlin','sales',100);181  CREATE VIEW v AS182    SELECT * FROM (183      SELECT * FROM employees 184       WHERE salary < 100185       ORDER BY salary desc)186     UNION ALL 187     SELECT * FROM (188       SELECT * FROM employees189       WHERE salary >= 100190       ORDER BY salary asc);191} {}192do_execsql_test offset1-2.1 {193  SELECT * FROM v LIMIT 5 OFFSET 2;194} {195  22  Grace  Berlin  it      90196  21  Emma   London  it      84197  12  Bob    London  hr      78198  11  Diane  London  hr      70199  33  Alice  Berlin  sales   100200}201 202finish_test203