Annotation of embedaddon/sqlite3/test/wherelimit.test, revision 1.1
1.1 ! misho 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: # check syntax error support
! 42: do_test wherelimit-0.1 {
! 43: catchsql {DELETE FROM t1 ORDER BY x}
! 44: } {1 {ORDER BY without LIMIT on DELETE}}
! 45: do_test wherelimit-0.2 {
! 46: catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x}
! 47: } {1 {ORDER BY without LIMIT on DELETE}}
! 48: do_test wherelimit-0.3 {
! 49: catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x}
! 50: } {1 {ORDER BY without LIMIT on UPDATE}}
! 51:
! 52: # no AS on table sources
! 53: do_test wherelimit-0.4 {
! 54: catchsql {DELETE FROM t1 AS a WHERE x=1}
! 55: } {1 {near "AS": syntax error}}
! 56: do_test wherelimit-0.5 {
! 57: catchsql {UPDATE t1 AS a SET y=1 WHERE x=1}
! 58: } {1 {near "AS": syntax error}}
! 59:
! 60: # OFFSET w/o LIMIT
! 61: do_test wherelimit-0.6 {
! 62: catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2}
! 63: } {1 {near "OFFSET": syntax error}}
! 64: do_test wherelimit-0.7 {
! 65: catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2}
! 66: } {1 {near "OFFSET": syntax error}}
! 67:
! 68:
! 69: # check deletes w/o where clauses but with limit/offsets
! 70: create_test_data 5
! 71: do_test wherelimit-1.0 {
! 72: execsql {SELECT count(*) FROM t1}
! 73: } {25}
! 74: do_test wherelimit-1.1 {
! 75: execsql {DELETE FROM t1}
! 76: execsql {SELECT count(*) FROM t1}
! 77: } {0}
! 78: create_test_data 5
! 79: do_test wherelimit-1.2 {
! 80: execsql {DELETE FROM t1 LIMIT 5}
! 81: execsql {SELECT count(*) FROM t1}
! 82: } {20}
! 83: do_test wherelimit-1.3 {
! 84: # limit 5
! 85: execsql {DELETE FROM t1 ORDER BY x LIMIT 5}
! 86: execsql {SELECT count(*) FROM t1}
! 87: } {15}
! 88: do_test wherelimit-1.4 {
! 89: # limit 5, offset 2
! 90: execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
! 91: execsql {SELECT count(*) FROM t1}
! 92: } {10}
! 93: do_test wherelimit-1.5 {
! 94: # limit 5, offset -2
! 95: execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2}
! 96: execsql {SELECT count(*) FROM t1}
! 97: } {5}
! 98: do_test wherelimit-1.6 {
! 99: # limit -5 (no limit), offset 2
! 100: execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5}
! 101: execsql {SELECT count(*) FROM t1}
! 102: } {2}
! 103: do_test wherelimit-1.7 {
! 104: # limit 5, offset -2 (no offset)
! 105: execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5}
! 106: execsql {SELECT count(*) FROM t1}
! 107: } {0}
! 108: create_test_data 5
! 109: do_test wherelimit-1.8 {
! 110: # limit -5 (no limit), offset -2 (no offset)
! 111: execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5}
! 112: execsql {SELECT count(*) FROM t1}
! 113: } {0}
! 114: create_test_data 3
! 115: do_test wherelimit-1.9 {
! 116: # limit 5, offset 2
! 117: execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5}
! 118: execsql {SELECT count(*) FROM t1}
! 119: } {4}
! 120: do_test wherelimit-1.10 {
! 121: # limit 5, offset 5
! 122: execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
! 123: execsql {SELECT count(*) FROM t1}
! 124: } {4}
! 125: do_test wherelimit-1.11 {
! 126: # limit 50, offset 30
! 127: execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
! 128: execsql {SELECT count(*) FROM t1}
! 129: } {4}
! 130: do_test wherelimit-1.12 {
! 131: # limit 50, offset 30
! 132: execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50}
! 133: execsql {SELECT count(*) FROM t1}
! 134: } {4}
! 135: do_test wherelimit-1.13 {
! 136: execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
! 137: execsql {SELECT count(*) FROM t1}
! 138: } {4}
! 139:
! 140:
! 141: create_test_data 6
! 142: do_test wherelimit-2.0 {
! 143: execsql {SELECT count(*) FROM t1}
! 144: } {36}
! 145: do_test wherelimit-2.1 {
! 146: execsql {DELETE FROM t1 WHERE x=1}
! 147: execsql {SELECT count(*) FROM t1}
! 148: } {30}
! 149: create_test_data 6
! 150: do_test wherelimit-2.2 {
! 151: execsql {DELETE FROM t1 WHERE x=1 LIMIT 5}
! 152: execsql {SELECT count(*) FROM t1}
! 153: } {31}
! 154: do_test wherelimit-2.3 {
! 155: # limit 5
! 156: execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5}
! 157: execsql {SELECT count(*) FROM t1}
! 158: } {30}
! 159: do_test wherelimit-2.4 {
! 160: # limit 5, offset 2
! 161: execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
! 162: execsql {SELECT count(*) FROM t1}
! 163: } {26}
! 164: do_test wherelimit-2.5 {
! 165: # limit 5, offset -2
! 166: execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
! 167: execsql {SELECT count(*) FROM t1}
! 168: } {24}
! 169: do_test wherelimit-2.6 {
! 170: # limit -5 (no limit), offset 2
! 171: execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5}
! 172: execsql {SELECT count(*) FROM t1}
! 173: } {20}
! 174: do_test wherelimit-2.7 {
! 175: # limit 5, offset -2 (no offset)
! 176: execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5}
! 177: execsql {SELECT count(*) FROM t1}
! 178: } {18}
! 179: do_test wherelimit-2.8 {
! 180: # limit -5 (no limit), offset -2 (no offset)
! 181: execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5}
! 182: execsql {SELECT count(*) FROM t1}
! 183: } {12}
! 184: create_test_data 6
! 185: do_test wherelimit-2.9 {
! 186: # limit 5, offset 2
! 187: execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5}
! 188: execsql {SELECT count(*) FROM t1}
! 189: } {32}
! 190: do_test wherelimit-2.10 {
! 191: # limit 5, offset 5
! 192: execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
! 193: execsql {SELECT count(*) FROM t1}
! 194: } {31}
! 195: do_test wherelimit-2.11 {
! 196: # limit 50, offset 30
! 197: execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
! 198: execsql {SELECT count(*) FROM t1}
! 199: } {31}
! 200: do_test wherelimit-2.12 {
! 201: # limit 50, offset 30
! 202: execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50}
! 203: execsql {SELECT count(*) FROM t1}
! 204: } {31}
! 205: do_test wherelimit-2.13 {
! 206: execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
! 207: execsql {SELECT count(*) FROM t1}
! 208: } {31}
! 209:
! 210:
! 211: create_test_data 6
! 212: do_test wherelimit-3.0 {
! 213: execsql {SELECT count(*) FROM t1}
! 214: } {36}
! 215: do_test wherelimit-3.1 {
! 216: execsql {UPDATE t1 SET y=1 WHERE x=1}
! 217: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 218: } {11}
! 219: create_test_data 6
! 220: do_test wherelimit-3.2 {
! 221: execsql {UPDATE t1 SET y=1 WHERE x=1 LIMIT 5}
! 222: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 223: } {10}
! 224: do_test wherelimit-3.3 {
! 225: # limit 5
! 226: execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5}
! 227: execsql {SELECT count(*) FROM t1 WHERE y=2}
! 228: } {9}
! 229: create_test_data 6
! 230: do_test wherelimit-3.4 {
! 231: # limit 5, offset 2
! 232: execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
! 233: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 234: } {6}
! 235: do_test wherelimit-3.5 {
! 236: # limit 5, offset -2
! 237: execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
! 238: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 239: } {5}
! 240: do_test wherelimit-3.6 {
! 241: # limit -5 (no limit), offset 2
! 242: execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5}
! 243: execsql {SELECT count(*) FROM t1 WHERE y=3}
! 244: } {8}
! 245: do_test wherelimit-3.7 {
! 246: # limit 5, offset -2 (no offset)
! 247: execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5}
! 248: execsql {SELECT count(*) FROM t1 WHERE y=3}
! 249: } {10}
! 250:
! 251: do_test wherelimit-3.8 {
! 252: # limit -5 (no limit), offset -2 (no offset)
! 253: execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5}
! 254: execsql {SELECT count(*) FROM t1 WHERE y=4}
! 255: } {9}
! 256: create_test_data 6
! 257: do_test wherelimit-3.9 {
! 258: # limit 5, offset 2
! 259: execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5}
! 260: execsql {SELECT count(*) FROM t1 WHERE y=4}
! 261: } {9}
! 262: do_test wherelimit-3.10 {
! 263: # limit 5, offset 5
! 264: execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
! 265: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 266: } {6}
! 267: do_test wherelimit-3.11 {
! 268: # limit 50, offset 30
! 269: execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
! 270: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 271: } {6}
! 272: do_test wherelimit-3.12 {
! 273: # limit 50, offset 30
! 274: execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
! 275: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 276: } {6}
! 277: do_test wherelimit-3.13 {
! 278: execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
! 279: execsql {SELECT count(*) FROM t1 WHERE y=1}
! 280: } {6}
! 281:
! 282: }
! 283:
! 284: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to wherelimit.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test