github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/offset1.test (about)

     1  # 2015-10-06
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # 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 where
    14  # the right-hand SELECT contains an ORDER BY in a subquery.
    15  #
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  
    20  ifcapable !compound {
    21    finish_test
    22    return
    23  }
    24  
    25  do_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  
    33  do_execsql_test offset1-1.2.0 {
    34    SELECT a, b FROM t1
    35    UNION ALL
    36    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    37    LIMIT 3 OFFSET 0;
    38  } {1 a 2 b 3 c}
    39  do_execsql_test offset1-1.2.1 {
    40    SELECT a, b FROM t1
    41    UNION ALL
    42    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    43    LIMIT 3 OFFSET 1;
    44  } {2 b 3 c 4 d}
    45  do_execsql_test offset1-1.2.2 {
    46    SELECT a, b FROM t1
    47    UNION ALL
    48    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    49    LIMIT 3 OFFSET 2;
    50  } {3 c 4 d 5 e}
    51  do_execsql_test offset1-1.2.3 {
    52    SELECT a, b FROM t1
    53    UNION ALL
    54    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    55    LIMIT 3 OFFSET 3;
    56  } {4 d 5 e 6 w}
    57  do_execsql_test offset1-1.2.4 {
    58    SELECT a, b FROM t1
    59    UNION ALL
    60    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    61    LIMIT 3 OFFSET 4;
    62  } {5 e 6 w 7 x}
    63  do_execsql_test offset1-1.2.5 {
    64    SELECT a, b FROM t1
    65    UNION ALL
    66    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    67    LIMIT 3 OFFSET 5;
    68  } {6 w 7 x 8 y}
    69  do_execsql_test offset1-1.2.6 {
    70    SELECT a, b FROM t1
    71    UNION ALL
    72    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    73    LIMIT 3 OFFSET 6;
    74  } {7 x 8 y 9 z}
    75  do_execsql_test offset1-1.2.7 {
    76    SELECT a, b FROM t1
    77    UNION ALL
    78    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    79    LIMIT 3 OFFSET 7;
    80  } {8 y 9 z}
    81  do_execsql_test offset1-1.2.8 {
    82    SELECT a, b FROM t1
    83    UNION ALL
    84    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    85    LIMIT 3 OFFSET 8;
    86  } {9 z}
    87  do_execsql_test offset1-1.2.9 {
    88    SELECT a, b FROM t1
    89    UNION ALL
    90    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
    91    LIMIT 3 OFFSET 9;
    92  } {}
    93  
    94  do_execsql_test offset1-1.3.0 {
    95    SELECT * FROM t1 LIMIT 0;
    96  } {}
    97  
    98  do_execsql_test offset1-1.4.0 {
    99    SELECT a, b FROM t1
   100    UNION ALL
   101    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
   102    LIMIT 0 OFFSET 1;
   103  } {}
   104  do_execsql_test offset1-1.4.1 {
   105    SELECT a, b FROM t1
   106    UNION ALL
   107    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
   108    LIMIT 1 OFFSET 1;
   109  } {2 b}
   110  do_execsql_test offset1-1.4.2 {
   111    SELECT a, b FROM t1
   112    UNION ALL
   113    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
   114    LIMIT 2 OFFSET 1;
   115  } {2 b 3 c}
   116  do_execsql_test offset1-1.4.3 {
   117    SELECT a, b FROM t1
   118    UNION ALL
   119    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
   120    LIMIT 3 OFFSET 1;
   121  } {2 b 3 c 4 d}
   122  do_execsql_test offset1-1.4.4 {
   123    SELECT a, b FROM t1
   124    UNION ALL
   125    SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
   126    LIMIT 4 OFFSET 1;
   127  } {2 b 3 c 4 d 5 e}
   128  do_execsql_test offset1-1.4.5 {
   129    SELECT a, b FROM t1
   130    UNION ALL
   131    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}
   134  do_execsql_test offset1-1.4.6 {
   135    SELECT a, b FROM t1
   136    UNION ALL
   137    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}
   140  do_execsql_test offset1-1.4.7 {
   141    SELECT a, b FROM t1
   142    UNION ALL
   143    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}
   146  do_execsql_test offset1-1.4.8 {
   147    SELECT a, b FROM t1
   148    UNION ALL
   149    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}
   152  do_execsql_test offset1-1.4.9 {
   153    SELECT a, b FROM t1
   154    UNION ALL
   155    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  
   160  
   161  finish_test