000001  # 2001 November 6
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.  The
000012  # focus of this file is testing the LIMIT ... OFFSET ... clause
000013  #  of SELECT statements.
000014  #
000015  # $Id: limit.test,v 1.32 2008/08/02 03:50:39 drh Exp $
000016  
000017  set testdir [file dirname $argv0]
000018  source $testdir/tester.tcl
000019  
000020  # Build some test data
000021  #
000022  execsql {
000023    CREATE TABLE t1(x int, y int);
000024    BEGIN;
000025  }
000026  for {set i 1} {$i<=32} {incr i} {
000027    for {set j 0} {(1<<$j)<$i} {incr j} {}
000028    execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
000029  }
000030  execsql {
000031    COMMIT;
000032  }
000033  
000034  do_test limit-1.0 {
000035    execsql {SELECT count(*) FROM t1}
000036  } {32}
000037  do_test limit-1.1 {
000038    execsql {SELECT count(*) FROM t1 LIMIT  5}
000039  } {32}
000040  do_test limit-1.2.1 {
000041    execsql {SELECT x FROM t1 ORDER BY x LIMIT 5}
000042  } {0 1 2 3 4}
000043  do_test limit-1.2.2 {
000044    execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
000045  } {2 3 4 5 6}
000046  do_test limit-1.2.3 {
000047    execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 5 OFFSET -2}
000048  } {0 1 2 3 4}
000049  do_test limit-1.2.4 {
000050    execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 2, -5}
000051  } {2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31}
000052  do_test limit-1.2.5 {
000053    execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, 5}
000054  } {0 1 2 3 4}
000055  do_test limit-1.2.6 {
000056    execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, -5}
000057  } {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31}
000058  do_test limit-1.2.7 {
000059    execsql {SELECT x FROM t1 ORDER BY x LIMIT 2, 5}
000060  } {2 3 4 5 6}
000061  do_test limit-1.3 {
000062    execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
000063  } {5 6 7 8 9}
000064  do_test limit-1.4.1 {
000065    execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
000066  } {30 31}
000067  do_test limit-1.4.2 {
000068    execsql {SELECT x FROM t1 ORDER BY x LIMIT 30, 50}
000069  } {30 31}
000070  do_test limit-1.5 {
000071    execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
000072  } {}
000073  do_test limit-1.6 {
000074    execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5}
000075  } {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5}
000076  do_test limit-1.7 {
000077    execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5 OFFSET 32}
000078  } {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5}
000079  
000080  ifcapable {view && subquery} {
000081    do_test limit-2.1 {
000082      execsql {
000083        CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
000084        SELECT count(*) FROM (SELECT * FROM v1);
000085      }
000086    } 2
000087  } ;# ifcapable view
000088  do_test limit-2.2 {
000089    execsql {
000090      CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2;
000091      SELECT count(*) FROM t2;
000092    }
000093  } 2
000094  ifcapable subquery {
000095    do_test limit-2.3 {
000096      execsql {
000097        SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
000098      }
000099    } 2
000100  }
000101  
000102  ifcapable subquery {
000103    do_test limit-3.1 {
000104      execsql {
000105        SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
000106        ORDER BY z LIMIT 5;
000107      }
000108    } {50 51 52 53 54}
000109  }
000110  
000111  do_test limit-4.1 {
000112    ifcapable subquery { 
000113      execsql {
000114        BEGIN;
000115        CREATE TABLE t3(x);
000116        INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
000117        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000118        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000119        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000120        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000121        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000122        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000123        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000124        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000125        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000126        INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000127        END;
000128        SELECT count(*) FROM t3;
000129      }
000130    } else {
000131      execsql {
000132        BEGIN;
000133        CREATE TABLE t3(x);
000134        INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
000135      }
000136      for {set i 0} {$i<10} {incr i} {
000137        set max_x_t3 [execsql {SELECT max(x) FROM t3}]
000138        execsql "INSERT INTO t3 SELECT x+$max_x_t3 FROM t3;"
000139      }
000140      execsql {
000141        END;
000142        SELECT count(*) FROM t3;
000143      }
000144    }
000145  } {10240}
000146  do_test limit-4.2 {
000147    execsql {
000148      SELECT x FROM t3 LIMIT 2 OFFSET 10000
000149    }
000150  } {10001 10002}
000151  do_test limit-4.3 {
000152    execsql {
000153      CREATE TABLE t4 AS SELECT x,
000154         'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000155         'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000156         'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000157         'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000158         'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y
000159      FROM t3 LIMIT 1000;
000160      SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999;
000161    }
000162  } {1000}
000163  
000164  do_test limit-5.1 {
000165    execsql {
000166      CREATE TABLE t5(x,y);
000167      INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
000168          ORDER BY x LIMIT 2;
000169      SELECT * FROM t5 ORDER BY x;
000170    }
000171  } {5 15 6 16}
000172  do_test limit-5.2 {
000173    execsql {
000174      DELETE FROM t5;
000175      INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
000176          ORDER BY x DESC LIMIT 2;
000177      SELECT * FROM t5 ORDER BY x;
000178    }
000179  } {9 19 10 20}
000180  do_test limit-5.3 {
000181    execsql {
000182      DELETE FROM t5;
000183      INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x ORDER BY x DESC LIMIT 31;
000184      SELECT * FROM t5 ORDER BY x LIMIT 2;
000185    }
000186  } {-4 6 -3 7}
000187  do_test limit-5.4 {
000188    execsql {
000189      SELECT * FROM t5 ORDER BY x DESC, y DESC LIMIT 2;
000190    }
000191  } {21 41 21 39}
000192  do_test limit-5.5 {
000193    execsql {
000194      DELETE FROM t5;
000195      INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
000196                     ORDER BY 1, 2 LIMIT 1000;
000197      SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
000198    }
000199  } {1000 1528204 593161 0 3107 505 1005}
000200  
000201  # There is some contraversy about whether LIMIT 0 should be the same as
000202  # no limit at all or if LIMIT 0 should result in zero output rows.
000203  #
000204  do_test limit-6.1 {
000205    execsql {
000206      BEGIN;
000207      CREATE TABLE t6(a);
000208      INSERT INTO t6 VALUES(1);
000209      INSERT INTO t6 VALUES(2);
000210      INSERT INTO t6 SELECT a+2 FROM t6;
000211      COMMIT;
000212      SELECT * FROM t6;
000213    }
000214  } {1 2 3 4}
000215  do_test limit-6.2 {
000216    execsql {
000217      SELECT * FROM t6 LIMIT -1 OFFSET -1;
000218    }
000219  } {1 2 3 4}
000220  do_test limit-6.3 {
000221    execsql {
000222      SELECT * FROM t6 LIMIT 2 OFFSET -123;
000223    }
000224  } {1 2}
000225  do_test limit-6.4 {
000226    execsql {
000227      SELECT * FROM t6 LIMIT -432 OFFSET 2;
000228    }
000229  } {3 4}
000230  do_test limit-6.5 {
000231    execsql {
000232      SELECT * FROM t6 LIMIT -1
000233    }
000234  } {1 2 3 4}
000235  do_test limit-6.6 {
000236    execsql {
000237      SELECT * FROM t6 LIMIT -1 OFFSET 1
000238    }
000239  } {2 3 4}
000240  do_test limit-6.7 {
000241    execsql {
000242      SELECT * FROM t6 LIMIT 0
000243    }
000244  } {}
000245  do_test limit-6.8 {
000246    execsql {
000247      SELECT * FROM t6 LIMIT 0 OFFSET 1
000248    }
000249  } {}
000250  
000251  # Make sure LIMIT works well with compound SELECT statements.
000252  # Ticket #393
000253  #
000254  # EVIDENCE-OF: R-13512-64012 In a compound SELECT, only the last or
000255  # right-most simple SELECT may contain a LIMIT clause.
000256  #
000257  # EVIDENCE-OF: R-03782-50113 In a compound SELECT, the LIMIT clause
000258  # applies to the entire compound, not just the final SELECT.
000259  #
000260  ifcapable compound {
000261  do_test limit-7.1.1 {
000262    catchsql {
000263      SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6;
000264    }
000265  } {1 {LIMIT clause should come after UNION ALL not before}}
000266  do_test limit-7.1.2 {
000267    catchsql {
000268      SELECT x FROM t2 LIMIT 5 UNION SELECT a FROM t6;
000269    }
000270  } {1 {LIMIT clause should come after UNION not before}}
000271  do_test limit-7.1.3 {
000272    catchsql {
000273      SELECT x FROM t2 LIMIT 5 EXCEPT SELECT a FROM t6 LIMIT 3;
000274    }
000275  } {1 {LIMIT clause should come after EXCEPT not before}}
000276  do_test limit-7.1.4 {
000277    catchsql {
000278      SELECT x FROM t2 LIMIT 0,5 INTERSECT SELECT a FROM t6;
000279    }
000280  } {1 {LIMIT clause should come after INTERSECT not before}}
000281  do_test limit-7.2 {
000282    execsql {
000283      SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 5;
000284    }
000285  } {31 30 1 2 3}
000286  do_test limit-7.3 {
000287    execsql {
000288      SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 3 OFFSET 1;
000289    }
000290  } {30 1 2}
000291  do_test limit-7.4 {
000292    execsql {
000293      SELECT x FROM t2 UNION ALL SELECT a FROM t6 ORDER BY 1 LIMIT 3 OFFSET 1;
000294    }
000295  } {2 3 4}
000296  do_test limit-7.5 {
000297    execsql {
000298      SELECT x FROM t2 UNION SELECT x+2 FROM t2 LIMIT 2 OFFSET 1;
000299    }
000300  } {31 32}
000301  do_test limit-7.6 {
000302    execsql {
000303      SELECT x FROM t2 UNION SELECT x+2 FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 1;
000304    }
000305  } {32 31}
000306  do_test limit-7.7 {
000307    execsql {
000308      SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 LIMIT 2;
000309    }
000310  } {11 12}
000311  do_test limit-7.8 {
000312    execsql {
000313      SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 ORDER BY 1 DESC LIMIT 2;
000314    }
000315  } {13 12}
000316  do_test limit-7.9 {
000317    execsql {
000318      SELECT a+26 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
000319    }
000320  } {30}
000321  do_test limit-7.10 {
000322    execsql {
000323      SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
000324    }
000325  } {30}
000326  do_test limit-7.11 {
000327    execsql {
000328      SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1 OFFSET 1;
000329    }
000330  } {31}
000331  do_test limit-7.12 {
000332    execsql {
000333      SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 
000334         ORDER BY 1 DESC LIMIT 1 OFFSET 1;
000335    }
000336  } {30}
000337  } ;# ifcapable compound
000338  
000339  # Tests for limit in conjunction with distinct.  The distinct should
000340  # occur before both the limit and the offset.  Ticket #749.
000341  #
000342  do_test limit-8.1 {
000343    execsql {
000344      SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5;
000345    }
000346  } {0 1 2 3 4}
000347  do_test limit-8.2 {
000348    execsql {
000349      SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 5;
000350    }
000351  } {5 6 7 8 9}
000352  do_test limit-8.3 {
000353    execsql {
000354      SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 25;
000355    }
000356  } {25 26 27 28 29}
000357  
000358  # Make sure limits on multiple subqueries work correctly.
000359  # Ticket #1035
000360  #
000361  ifcapable subquery {
000362    do_test limit-9.1 {
000363      execsql {
000364        SELECT * FROM (SELECT * FROM t6 LIMIT 3);
000365      }
000366    } {1 2 3}
000367  }
000368  do_test limit-9.2.1 {
000369    execsql {
000370      CREATE TABLE t7 AS SELECT * FROM t6;
000371    }
000372  } {}
000373  ifcapable subquery {
000374    do_test limit-9.2.2 {
000375      execsql {
000376        SELECT * FROM (SELECT * FROM t7 LIMIT 3);
000377      }
000378    } {1 2 3}
000379  }
000380  ifcapable compound {
000381    ifcapable subquery {
000382      do_test limit-9.3 {
000383        execsql {
000384          SELECT * FROM (SELECT * FROM t6 LIMIT 3)
000385          UNION
000386          SELECT * FROM (SELECT * FROM t7 LIMIT 3)
000387          ORDER BY 1
000388        }
000389      } {1 2 3}
000390      do_test limit-9.4 {
000391        execsql {
000392          SELECT * FROM (SELECT * FROM t6 LIMIT 3)
000393          UNION
000394          SELECT * FROM (SELECT * FROM t7 LIMIT 3)
000395          ORDER BY 1
000396          LIMIT 2
000397        }
000398      } {1 2}
000399    }
000400    do_test limit-9.5 {
000401      catchsql {
000402        SELECT * FROM t6 LIMIT 3
000403        UNION
000404        SELECT * FROM t7 LIMIT 3
000405      }
000406    } {1 {LIMIT clause should come after UNION not before}}
000407  }
000408  
000409  # Test LIMIT and OFFSET using SQL variables.
000410  do_test limit-10.1 {
000411    set limit 10
000412    db eval {
000413      SELECT x FROM t1 LIMIT :limit;
000414    }
000415  } {31 30 29 28 27 26 25 24 23 22}
000416  do_test limit-10.2 {
000417    set limit 5
000418    set offset 5
000419    db eval {
000420      SELECT x FROM t1 LIMIT :limit OFFSET :offset;
000421    }
000422  } {26 25 24 23 22}
000423  do_test limit-10.3 {
000424    set limit -1
000425    db eval {
000426      SELECT x FROM t1 WHERE x<10 LIMIT :limit;
000427    }
000428  } {9 8 7 6 5 4 3 2 1 0}
000429  do_test limit-10.4 {
000430    set limit 1.5
000431    set rc [catch {
000432    db eval {
000433      SELECT x FROM t1 WHERE x<10 LIMIT :limit;
000434    } } msg]
000435    list $rc $msg
000436  } {1 {datatype mismatch}}
000437  do_test limit-10.5 {
000438    set limit "hello world"
000439    set rc [catch {
000440    db eval {
000441      SELECT x FROM t1 WHERE x<10 LIMIT :limit;
000442    } } msg]
000443    list $rc $msg
000444  } {1 {datatype mismatch}}
000445  
000446  ifcapable subquery {
000447  do_test limit-11.1 {
000448    db eval {
000449       SELECT x FROM (SELECT x FROM t1 ORDER BY x LIMIT 0) ORDER BY x
000450    }
000451  } {}
000452  } ;# ifcapable subquery
000453  
000454  # Test error processing.
000455  #
000456  do_test limit-12.1 {
000457    catchsql {
000458       SELECT * FROM t1 LIMIT replace(1)
000459    }
000460  } {1 {wrong number of arguments to function replace()}}
000461  do_test limit-12.2 {
000462    catchsql {
000463       SELECT * FROM t1 LIMIT 5 OFFSET replace(1)
000464    }
000465  } {1 {wrong number of arguments to function replace()}}
000466  do_test limit-12.3 {
000467    catchsql {
000468       SELECT * FROM t1 LIMIT x
000469    }
000470  } {1 {no such column: x}}
000471  do_test limit-12.4 {
000472    catchsql {
000473       SELECT * FROM t1 LIMIT 1 OFFSET x
000474    }
000475  } {1 {no such column: x}}
000476  
000477  # Ticket [db4d96798da8b]
000478  # LIMIT does not work with nested views containing UNION ALL 
000479  #
000480  do_test limit-13.1 {
000481    db eval {
000482      CREATE TABLE t13(x);
000483      INSERT INTO t13 VALUES(1),(2);
000484      CREATE VIEW v13a AS SELECT x AS y FROM t13;
000485      CREATE VIEW v13b AS SELECT y AS z FROM v13a UNION ALL SELECT y+10 FROM v13a;
000486      CREATE VIEW v13c AS SELECT z FROM v13b UNION ALL SELECT z+20 FROM v13b;
000487    }
000488  } {}
000489  do_test limit-13.2 {
000490    db eval {SELECT z FROM v13c LIMIT 1}
000491  } {1}
000492  do_test limit-13.3 {
000493    db eval {SELECT z FROM v13c LIMIT 2}
000494  } {1 2}
000495  do_test limit-13.4 {
000496    db eval {SELECT z FROM v13c LIMIT 3}
000497  } {1 2 11}
000498  do_test limit-13.5 {
000499    db eval {SELECT z FROM v13c LIMIT 4}
000500  } {1 2 11 12}
000501  do_test limit-13.6 {
000502    db eval {SELECT z FROM v13c LIMIT 5}
000503  } {1 2 11 12 21}
000504  do_test limit-13.7 {
000505    db eval {SELECT z FROM v13c LIMIT 6}
000506  } {1 2 11 12 21 22}
000507  do_test limit-13.8 {
000508    db eval {SELECT z FROM v13c LIMIT 7}
000509  } {1 2 11 12 21 22 31}
000510  do_test limit-13.9 {
000511    db eval {SELECT z FROM v13c LIMIT 8}
000512  } {1 2 11 12 21 22 31 32}
000513  do_test limit-13.10 {
000514    db eval {SELECT z FROM v13c LIMIT 9}
000515  } {1 2 11 12 21 22 31 32}
000516  do_test limit-13.11 {
000517    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 1}
000518  } {2}
000519  do_test limit-13.12 {
000520    db eval {SELECT z FROM v13c LIMIT 2 OFFSET 1}
000521  } {2 11}
000522  do_test limit-13.13 {
000523    db eval {SELECT z FROM v13c LIMIT 3 OFFSET 1}
000524  } {2 11 12}
000525  do_test limit-13.14 {
000526    db eval {SELECT z FROM v13c LIMIT 4 OFFSET 1}
000527  } {2 11 12 21}
000528  do_test limit-13.15 {
000529    db eval {SELECT z FROM v13c LIMIT 5 OFFSET 1}
000530  } {2 11 12 21 22}
000531  do_test limit-13.16 {
000532    db eval {SELECT z FROM v13c LIMIT 6 OFFSET 1}
000533  } {2 11 12 21 22 31}
000534  do_test limit-13.17 {
000535    db eval {SELECT z FROM v13c LIMIT 7 OFFSET 1}
000536  } {2 11 12 21 22 31 32}
000537  do_test limit-13.18 {
000538    db eval {SELECT z FROM v13c LIMIT 8 OFFSET 1}
000539  } {2 11 12 21 22 31 32}
000540  do_test limit-13.21 {
000541    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 2}
000542  } {11}
000543  do_test limit-13.22 {
000544    db eval {SELECT z FROM v13c LIMIT 2 OFFSET 2}
000545  } {11 12}
000546  do_test limit-13.23 {
000547    db eval {SELECT z FROM v13c LIMIT 3 OFFSET 2}
000548  } {11 12 21}
000549  do_test limit-13.24 {
000550    db eval {SELECT z FROM v13c LIMIT 4 OFFSET 2}
000551  } {11 12 21 22}
000552  do_test limit-13.25 {
000553    db eval {SELECT z FROM v13c LIMIT 5 OFFSET 2}
000554  } {11 12 21 22 31}
000555  do_test limit-13.26 {
000556    db eval {SELECT z FROM v13c LIMIT 6 OFFSET 2}
000557  } {11 12 21 22 31 32}
000558  do_test limit-13.27 {
000559    db eval {SELECT z FROM v13c LIMIT 7 OFFSET 2}
000560  } {11 12 21 22 31 32}
000561  do_test limit-13.31 {
000562    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 3}
000563  } {12}
000564  do_test limit-13.32 {
000565    db eval {SELECT z FROM v13c LIMIT 2 OFFSET 3}
000566  } {12 21}
000567  do_test limit-13.33 {
000568    db eval {SELECT z FROM v13c LIMIT 3 OFFSET 3}
000569  } {12 21 22}
000570  do_test limit-13.34 {
000571    db eval {SELECT z FROM v13c LIMIT 4 OFFSET 3}
000572  } {12 21 22 31}
000573  do_test limit-13.35 {
000574    db eval {SELECT z FROM v13c LIMIT 5 OFFSET 3}
000575  } {12 21 22 31 32}
000576  do_test limit-13.36 {
000577    db eval {SELECT z FROM v13c LIMIT 6 OFFSET 3}
000578  } {12 21 22 31 32}
000579  do_test limit-13.41 {
000580    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 4}
000581  } {21}
000582  do_test limit-13.42 {
000583    db eval {SELECT z FROM v13c LIMIT 2 OFFSET 4}
000584  } {21 22}
000585  do_test limit-13.43 {
000586    db eval {SELECT z FROM v13c LIMIT 3 OFFSET 4}
000587  } {21 22 31}
000588  do_test limit-13.44 {
000589    db eval {SELECT z FROM v13c LIMIT 4 OFFSET 4}
000590  } {21 22 31 32}
000591  do_test limit-13.45 {
000592    db eval {SELECT z FROM v13c LIMIT 5 OFFSET 4}
000593  } {21 22 31 32}
000594  do_test limit-13.51 {
000595    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 5}
000596  } {22}
000597  do_test limit-13.52 {
000598    db eval {SELECT z FROM v13c LIMIT 2 OFFSET 5}
000599  } {22 31}
000600  do_test limit-13.53 {
000601    db eval {SELECT z FROM v13c LIMIT 3 OFFSET 5}
000602  } {22 31 32}
000603  do_test limit-13.54 {
000604    db eval {SELECT z FROM v13c LIMIT 4 OFFSET 5}
000605  } {22 31 32}
000606  do_test limit-13.61 {
000607    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 6}
000608  } {31}
000609  do_test limit-13.62 {
000610    db eval {SELECT z FROM v13c LIMIT 2 OFFSET 6}
000611  } {31 32}
000612  do_test limit-13.63 {
000613    db eval {SELECT z FROM v13c LIMIT 3 OFFSET 6}
000614  } {31 32}
000615  do_test limit-13.71 {
000616    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 7}
000617  } {32}
000618  do_test limit-13.72 {
000619    db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7}
000620  } {32}
000621  do_test limit-13.81 {
000622    db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8}
000623  } {}
000624  
000625  do_execsql_test limit-14.1 {
000626    SELECT 123 LIMIT 1 OFFSET 0
000627  } {123}
000628  do_execsql_test limit-14.2 {
000629    SELECT 123 LIMIT 1 OFFSET 1
000630  } {}
000631  do_execsql_test limit-14.3 {
000632    SELECT 123 LIMIT 0 OFFSET 0
000633  } {}
000634  do_execsql_test limit-14.4 {
000635    SELECT 123 LIMIT 0 OFFSET 1
000636  } {}
000637  do_execsql_test limit-14.6 {
000638    SELECT 123 LIMIT -1 OFFSET 0
000639  } {123}
000640  do_execsql_test limit-14.7 {
000641    SELECT 123 LIMIT -1 OFFSET 1
000642  } {}
000643  
000644  # 2021-03-05 dbsqlfuzz crash-d811039c9f44f2d43199d5889fcf4085ef6221b9
000645  #
000646  reset_db
000647  do_execsql_test limit-15.1 {
000648    CREATE TABLE t1(a PRIMARY KEY, b TEXT);
000649    CREATE TABLE t4(c PRIMARY KEY, d);
000650    CREATE TABLE t5(e PRIMARY KEY, f);
000651    CREATE TABLE t6(g, h);
000652    CREATE TABLE t3_a(k, v);
000653    CREATE TABLE t3_b(k, v);
000654    CREATE VIEW t3 AS SELECT * FROM t3_a UNION ALL SELECT * FROM t3_b;
000655    INSERT INTO t5(e,f) VALUES(500000,'orange');
000656    INSERT INTO t4(c,d) VALUES(300000,'blue'),(400,'green'),(8000,'grey');
000657    INSERT INTO t1(a,b) VALUES(300000,'purple');
000658    INSERT INTO t3_a VALUES(300000,'yellow'),(500,'pink'),(8000,'red');
000659    INSERT INTO t6 default values;
000660    SELECT (
000661        SELECT 100000 FROM
000662            (SELECT 200000 FROM t6 WHERE a = ( SELECT 300000 FROM t3 WHERE a ) ),
000663            (SELECT 400000 FROM t5 WHERE e=500000),
000664            (SELECT 600000 FROM t4 WHERE c=a)
000665    ) FROM t1;
000666  } {100000}
000667  
000668  finish_test