gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/wherelimit.test (about)

     1  # 2008 October 6
     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  # This file implements regression tests for SQLite library.  The
    12  # focus of this file is testing the LIMIT ... OFFSET ... clause
    13  #  of UPDATE and DELETE statements.
    14  #
    15  # $Id: wherelimit.test,v 1.2 2008/10/10 18:25:46 shane Exp $
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  
    20  proc create_test_data {size} {
    21    # Build some test data
    22    #
    23    execsql {
    24      DROP TABLE IF EXISTS t1;
    25      CREATE TABLE t1(x int, y int);
    26      BEGIN;
    27    }
    28    for {set i 1} {$i<=$size} {incr i} {
    29      for {set j 1} {$j<=$size} {incr j} {
    30        execsql "INSERT INTO t1 VALUES([expr {$i}],[expr {$j}])"
    31      }
    32    }
    33    execsql {
    34      COMMIT;
    35    }
    36    return {}
    37  }
    38  
    39  ifcapable {update_delete_limit} {
    40  
    41    execsql { CREATE TABLE t1(x, y) }
    42  
    43    # check syntax error support
    44    do_test wherelimit-0.1 {
    45      catchsql {DELETE FROM t1 ORDER BY x}
    46    } {1 {ORDER BY without LIMIT on DELETE}}
    47    do_test wherelimit-0.2 {
    48      catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x}
    49    } {1 {ORDER BY without LIMIT on DELETE}}
    50    do_test wherelimit-0.3 {
    51      catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x}
    52    } {1 {ORDER BY without LIMIT on UPDATE}}
    53  
    54    # no AS on table sources
    55    #
    56    # UPDATE: As of version 3.24, AS clauses are allowed as part of
    57    # UPDATE or DELETE statements.
    58    do_test wherelimit-0.4 {
    59      catchsql {DELETE FROM t1 AS a WHERE a.x=1}
    60    } {0 {}}
    61    do_test wherelimit-0.5.1 {
    62      catchsql {UPDATE t1 AS a SET y=1 WHERE x=1}
    63    } {0 {}}
    64    do_test wherelimit-0.5.2 {
    65      catchsql {UPDATE t1 AS a SET y=1 WHERE t1.x=1}
    66    } {1 {no such column: t1.x}}
    67  
    68    # OFFSET w/o LIMIT
    69    do_test wherelimit-0.6 {
    70      catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2}
    71    } {1 {near "OFFSET": syntax error}}
    72    do_test wherelimit-0.7 {
    73      catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2}
    74    } {1 {near "OFFSET": syntax error}}
    75  
    76    execsql { DROP TABLE t1 }
    77  
    78    # check deletes w/o where clauses but with limit/offsets
    79    create_test_data 5
    80    do_test wherelimit-1.0 {
    81      execsql {SELECT count(*) FROM t1}
    82    } {25}
    83    do_test wherelimit-1.1 {
    84      execsql {DELETE FROM t1}
    85      execsql {SELECT count(*) FROM t1}
    86    } {0}
    87    create_test_data 5
    88    do_test wherelimit-1.2 {
    89      execsql {DELETE FROM t1 LIMIT 5}
    90      execsql {SELECT count(*) FROM t1}
    91    } {20}
    92    do_test wherelimit-1.3 {
    93      # limit 5
    94      execsql {DELETE FROM t1 ORDER BY x LIMIT 5}
    95      execsql {SELECT count(*) FROM t1}
    96    } {15}
    97    create_test_data 4
    98    do_test wherelimit-1.3b {
    99      # limit 5
   100      execsql {DELETE FROM t1 RETURNING x, y, '|' ORDER BY x, y LIMIT 5}
   101    } {1 1 | 1 2 | 1 3 | 1 4 | 2 1 |}
   102    do_test wherelimit-1.3c {
   103      execsql {SELECT count(*) FROM t1}
   104    } {11}
   105    do_test wherelimit-1.4 {
   106      # limit 5, offset 2
   107      execsql {DELETE FROM t1 RETURNING x, y, '|' ORDER BY x  LIMIT 5 OFFSET 2}
   108    } {2 4 | 3 1 | 3 2 | 3 3 | 3 4 |}
   109    do_test wherelimit-1.4cnt {
   110      execsql {SELECT count(*) FROM t1}
   111    } {6}
   112    do_test wherelimit-1.5 {
   113      # limit 5, offset -2
   114      execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2}
   115      execsql {SELECT count(*) FROM t1}
   116    } {1}
   117    do_test wherelimit-1.6 {
   118      # limit -5 (no limit), offset 2
   119      execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5}
   120      execsql {SELECT count(*) FROM t1}
   121    } {1}
   122    do_test wherelimit-1.7 {
   123      # limit 5, offset -2 (no offset)
   124      execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5}
   125      execsql {SELECT count(*) FROM t1}
   126    } {0}
   127    create_test_data 5
   128    do_test wherelimit-1.8 {
   129      # limit -5 (no limit), offset -2 (no offset)
   130      execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5}
   131      execsql {SELECT count(*) FROM t1}
   132    } {0}
   133    create_test_data 3
   134    do_test wherelimit-1.9 {
   135      # limit 5, offset 2
   136      execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5}
   137      execsql {SELECT count(*) FROM t1}
   138    } {4}
   139    do_test wherelimit-1.10 {
   140      # limit 5, offset 5
   141      execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
   142      execsql {SELECT count(*) FROM t1}
   143    } {4}
   144    do_test wherelimit-1.11 {
   145      # limit 50, offset 30
   146      execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
   147      execsql {SELECT count(*) FROM t1}
   148    } {4}
   149    do_test wherelimit-1.12 {
   150      # limit 50, offset 30
   151      execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50}
   152      execsql {SELECT count(*) FROM t1}
   153    } {4}
   154    do_test wherelimit-1.13 {
   155      execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
   156      execsql {SELECT count(*) FROM t1}
   157    } {4}
   158  
   159  
   160    create_test_data 6
   161    do_test wherelimit-2.0 {
   162      execsql {SELECT count(*) FROM t1}
   163    } {36}
   164    do_test wherelimit-2.1 {
   165      execsql {DELETE FROM t1 WHERE x=1}
   166      execsql {SELECT count(*) FROM t1}
   167    } {30}
   168    create_test_data 6
   169    do_test wherelimit-2.2 {
   170      execsql {DELETE FROM t1 WHERE x=1 LIMIT 5}
   171      execsql {SELECT count(*) FROM t1}
   172    } {31}
   173    do_test wherelimit-2.3 {
   174      # limit 5
   175      execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5}
   176      execsql {SELECT count(*) FROM t1}
   177    } {30}
   178    do_test wherelimit-2.4 {
   179      # limit 5, offset 2
   180      execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
   181      execsql {SELECT count(*) FROM t1}
   182    } {26}
   183    do_test wherelimit-2.5 {
   184      # limit 5, offset -2
   185      execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
   186      execsql {SELECT count(*) FROM t1}
   187    } {24}
   188    do_test wherelimit-2.6 {
   189      # limit -5 (no limit), offset 2
   190      execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5}
   191      execsql {SELECT count(*) FROM t1}
   192    } {20}
   193    do_test wherelimit-2.7 {
   194      # limit 5, offset -2 (no offset)
   195      execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5}
   196      execsql {SELECT count(*) FROM t1}
   197    } {18}
   198    do_test wherelimit-2.8 {
   199      # limit -5 (no limit), offset -2 (no offset)
   200      execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5}
   201      execsql {SELECT count(*) FROM t1}
   202    } {12}
   203    create_test_data 6
   204    do_test wherelimit-2.9 {
   205      # limit 5, offset 2
   206      execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5}
   207      execsql {SELECT count(*) FROM t1}
   208    } {32}
   209    do_test wherelimit-2.10 {
   210      # limit 5, offset 5
   211      execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
   212      execsql {SELECT count(*) FROM t1}
   213    } {31}
   214    do_test wherelimit-2.11 {
   215      # limit 50, offset 30
   216      execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
   217      execsql {SELECT count(*) FROM t1}
   218    } {31}
   219    do_test wherelimit-2.12 {
   220      # limit 50, offset 30
   221      execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50}
   222      execsql {SELECT count(*) FROM t1}
   223    } {31}
   224    do_test wherelimit-2.13 {
   225      execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
   226      execsql {SELECT count(*) FROM t1}
   227    } {31}
   228  
   229  
   230    create_test_data 6
   231    do_test wherelimit-3.0 {
   232      execsql {SELECT count(*) FROM t1}
   233    } {36}
   234    do_test wherelimit-3.1 {
   235      execsql {UPDATE t1 SET y=1 WHERE x=1}
   236      execsql {SELECT count(*) FROM t1 WHERE y=1}
   237    } {11}
   238    create_test_data 6
   239    do_test wherelimit-3.2 {
   240      execsql {UPDATE t1 SET y=1 WHERE x=1 RETURNING x, y, '|' LIMIT 5}
   241    } {1 1 | 1 1 | 1 1 | 1 1 | 1 1 |}
   242    do_test wherelimit-3.2cnt {
   243      execsql {SELECT count(*) FROM t1 WHERE y=1}
   244    } {10}
   245    do_test wherelimit-3.3 {
   246      # limit 5
   247      execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5}
   248      execsql {SELECT count(*) FROM t1 WHERE y=2}
   249    } {9}
   250    create_test_data 6
   251    do_test wherelimit-3.4 {
   252      # limit 5, offset 2
   253      execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
   254      execsql {SELECT count(*) FROM t1 WHERE y=1}
   255    } {6}
   256    do_test wherelimit-3.5 {
   257      # limit 5, offset -2
   258      execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
   259      execsql {SELECT count(*) FROM t1 WHERE y=1}
   260    } {5}
   261    do_test wherelimit-3.6 {
   262      # limit -5 (no limit), offset 2
   263      execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5}
   264      execsql {SELECT count(*) FROM t1 WHERE y=3}
   265    } {8}
   266    do_test wherelimit-3.7 {
   267      # limit 5, offset -2 (no offset)
   268      execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5}
   269      execsql {SELECT count(*) FROM t1 WHERE y=3}
   270    } {10}
   271  
   272    do_test wherelimit-3.8 {
   273      # limit -5 (no limit), offset -2 (no offset)
   274      execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5}
   275      execsql {SELECT count(*) FROM t1 WHERE y=4}
   276    } {9}
   277    create_test_data 6
   278    do_test wherelimit-3.9 {
   279      # limit 5, offset 2
   280      execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5}
   281      execsql {SELECT count(*) FROM t1 WHERE y=4}
   282    } {9}
   283    do_test wherelimit-3.10 {
   284      # limit 5, offset 5
   285      execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
   286      execsql {SELECT count(*) FROM t1 WHERE y=1}
   287    } {6}
   288    do_test wherelimit-3.11 {
   289      # limit 50, offset 30
   290      execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
   291      execsql {SELECT count(*) FROM t1 WHERE y=1}
   292    } {6}
   293    do_test wherelimit-3.12 {
   294      # limit 50, offset 30
   295      execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
   296      execsql {SELECT count(*) FROM t1 WHERE y=1}
   297    } {6}
   298    do_test wherelimit-3.13 {
   299      execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
   300      execsql {SELECT count(*) FROM t1 WHERE y=1}
   301    } {6}
   302  
   303    # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table
   304    # or a VIEW.  (We should fix this someday).
   305    #
   306    db close
   307    sqlite3 db :memory:
   308    do_execsql_test wherelimit-4.1 {
   309      CREATE TABLE t1(a int);
   310      INSERT INTO t1 VALUES(1);
   311      INSERT INTO t1 VALUES(2);
   312      INSERT INTO t1 VALUES(3);
   313      CREATE TABLE t2(a int);
   314      INSERT INTO t2 SELECT a+100 FROM t1;
   315      CREATE VIEW tv(r,a) AS
   316         SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1;
   317      CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv
   318      BEGIN
   319        DELETE FROM t1 WHERE rowid=old.r;
   320        DELETE FROM t2 WHERE rowid=old.r;
   321      END;
   322    } {}
   323    do_catchsql_test wherelimit-4.2 {
   324      DELETE FROM tv WHERE 1 LIMIT 2;
   325    } {0 {}}
   326    do_catchsql_test wherelimit-4.3 {
   327      DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2;
   328    } {0 {}}
   329    do_execsql_test wherelimit-4.10 {
   330      CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
   331      INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12);
   332    } {}
   333    do_catchsql_test wherelimit-4.11 {
   334      DELETE FROM t3 WHERE a=5 LIMIT 2;
   335    } {0 {}}
   336    do_execsql_test wherelimit-4.12 {
   337      SELECT a,b,c,d FROM t3 ORDER BY 1;
   338    } {1 2 3 4 9 10 11 12}
   339  
   340  }
   341  
   342  finish_test