AryaWu/sqlite
0
1# 2007 May 102#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# This file implements regression tests for SQLite library. The12# focus of this file is generating semi-random strings of SQL13# (a.k.a. "fuzz") and sending it into the parser to try to 14# generate errors.15#16# The tests in this file are really about testing fuzzily generated17# SQL parse-trees. The majority of the fuzzily generated SQL is 18# valid as far as the parser is concerned. 19#20# The most complicated trees are for SELECT statements.21#22# $Id: fuzz.test,v 1.19 2009/04/28 11:10:39 danielk1977 Exp $23 24set testdir [file dirname $argv0]25source $testdir/tester.tcl26 27set ::REPEATS 500028 29# If running quick.test, don't do so many iterations.30if {[info exists ::G(isquick)]} {31 if {$::G(isquick)} { set ::REPEATS 20 }32}33 34source $testdir/fuzz_common.tcl35expr srand(0)36 37#----------------------------------------------------------------38# These tests caused errors that were first caught by the tests39# in this file. They are still here.40do_test fuzz-1.1 {41 execsql {42 SELECT 'abc' LIKE X'ABCD';43 }44} {0}45do_test fuzz-1.2 {46 execsql {47 SELECT 'abc' LIKE zeroblob(10);48 }49} {0}50do_test fuzz-1.3 {51 execsql {52 SELECT zeroblob(10) LIKE 'abc';53 }54} {0}55do_test fuzz-1.4 {56 execsql {57 SELECT (- -21) % NOT (456 LIKE zeroblob(10));58 }59} {0}60do_test fuzz-1.5 {61 execsql {62 SELECT (SELECT (63 SELECT (SELECT -2147483648) FROM (SELECT 1) ORDER BY 164 ))65 }66} {-2147483648}67do_test fuzz-1.6 {68 execsql {69 SELECT 'abc', zeroblob(1) FROM (SELECT 1) ORDER BY 170 }71} [execsql {SELECT 'abc', zeroblob(1)}]72 73do_test fuzz-1.7 {74 execsql {75 SELECT ( SELECT zeroblob(1000) FROM ( 76 SELECT * FROM (SELECT 'first') ORDER BY NOT 'in') 77 )78 }79} [execsql {SELECT zeroblob(1000)}]80 81do_test fuzz-1.8 {82 # Problems with opcode OP_ToText (did not account for MEM_Zero).83 # Also MemExpandBlob() was marking expanded blobs as nul-terminated.84 # They are not.85 execsql {86 SELECT CAST(zeroblob(1000) AS text);87 }88} {{}}89 90do_test fuzz-1.9 {91 # This was causing a NULL pointer dereference of Expr.pList.92 execsql {93 SELECT 1 FROM (SELECT * FROM sqlite_master WHERE random())94 }95} {}96 97do_test fuzz-1.10 {98 # Bug in calculation of Parse.ckOffset causing an assert() 99 # to fail. Probably harmless.100 execsql {101 SELECT coalesce(1, substr( 1, 2, length('in' IN (SELECT 1))))102 }103} {1}104 105do_test fuzz-1.11 {106 # The literals (A, B, C, D) are not important, they are just used107 # to make the EXPLAIN output easier to read.108 #109 # The problem here is that the EXISTS(...) expression leaves an110 # extra value on the VDBE stack. This is confusing the parent and111 # leads to an assert() failure when OP_Insert encounters an integer112 # when it expects a record blob.113 #114 # Update: Any query with (LIMIT 0) was leaking stack.115 #116 execsql {117 SELECT 'A' FROM (SELECT 'B') ORDER BY EXISTS (118 SELECT 'C' FROM (SELECT 'D' LIMIT 0)119 )120 }121} {A}122 123do_test fuzz-1.12.1 {124 # Create a table with a single row.125 execsql {126 CREATE TABLE abc(b);127 INSERT INTO abc VALUES('ABCDE');128 }129 130 # The following query was crashing. The later subquery (in the FROM)131 # clause was flattened into the parent, but the code was not repairng132 # the "b" reference in the other sub-query. When the query was executed,133 # that "b" referred to a non-existant vdbe table-cursor.134 #135 execsql {136 SELECT 1 IN ( SELECT b UNION SELECT 1 ) FROM (SELECT b FROM abc);137 }138} {1}139do_test fuzz-1.12.2 {140 # Clean up after the previous query.141 execsql {142 DROP TABLE abc;143 }144} {}145 146 147do_test fuzz-1.13 {148 # The problem here was that when there were more expressions in149 # the ORDER BY list than the result-set list. The temporary b-tree150 # used for sorting was being misconfigured in this case.151 #152 execsql {153 SELECT 'abcd' UNION SELECT 'efgh' ORDER BY 1 ASC, 1 ASC;154 }155} {abcd efgh}156 157do_test fuzz-1.14.1 {158 execsql {159 CREATE TABLE abc(a, b, c);160 INSERT INTO abc VALUES(123, 456, 789);161 }162 163 # The [a] reference in the sub-select was causing a problem. Because164 # the internal walkSelectExpr() function was not considering compound165 # SELECT operators.166 execsql {167 SELECT 1 FROM abc168 GROUP BY c HAVING EXISTS (SELECT a UNION SELECT 123);169 }170} {1}171do_test fuzz-1.14.2 {172 execsql {173 DROP TABLE abc;174 }175} {}176 177# Making sure previously discovered errors have been fixed.178#179do_test fuzz-1.15 {180 execsql {181 SELECT hex(CAST(zeroblob(1000) AS integer))182 }183} {30}184 185do_test fuzz-1.16.1 {186 execsql {187 CREATE TABLE abc(a, b, c);188 CREATE TABLE def(a, b, c);189 CREATE TABLE ghi(a, b, c);190 }191} {}192do_test fuzz-1.16.2 {193 catchsql {194 SELECT DISTINCT EXISTS(195 SELECT 1196 FROM (197 SELECT C FROM (SELECT 1)198 )199 WHERE (SELECT c)200 )201 FROM abc202 }203} {0 {}}204do_test fuzz-1.16.3 {205 catchsql {206 SELECT DISTINCT substr(-456 ISNULL,zeroblob(1000), EXISTS(207 SELECT DISTINCT EXISTS(208 SELECT DISTINCT b FROM abc209 ORDER BY EXISTS (210 SELECT DISTINCT 2147483647 UNION ALL SELECT -2147483648211 ) ASC212 )213 FROM (214 SELECT c, c FROM (215 SELECT 456, 'injection' ORDER BY 56.1 ASC, -56.1 DESC216 )217 )218 GROUP BY (SELECT ALL (SELECT DISTINCT 'hardware')) 219 HAVING (220 SELECT DISTINCT c221 FROM (222 SELECT ALL -2147483648, 'experiments'223 ORDER BY -56.1 ASC, -56.1 DESC224 )225 GROUP BY (SELECT DISTINCT 456) IN 226 (SELECT DISTINCT 'injection') NOT IN (SELECT ALL -456)227 HAVING EXISTS (228 SELECT ALL 'injection'229 )230 )231 UNION ALL232 SELECT a IN (233 SELECT -2147483647234 UNION ALL235 SELECT ALL 'injection'236 )237 FROM sqlite_master238 ) -- end EXISTS239 ) /* end SUBSTR() */, c NOTNULL ISNULL240 FROM abc241 ORDER BY CAST(-56.1 AS blob) ASC242 }243} {0 {}}244do_test fuzz-1.16.4 {245 execsql {246 DROP TABLE abc; DROP TABLE def; DROP TABLE ghi;247 }248} {}249 250do_test fuzz-1.17 {251 catchsql {252 SELECT 'hardware', 56.1 NOTNULL, random()&0253 FROM (254 SELECT ALL lower(~ EXISTS (255 SELECT 1 NOT IN (SELECT ALL 1)256 )), CAST(456 AS integer), -2147483647257 FROM (258 SELECT DISTINCT -456, CAST(1 AS integer) ISNULL259 FROM (SELECT ALL 2147483647, typeof(2147483649))260 )261 )262 GROUP BY CAST(CAST('experiments' AS blob) AS blob)263 HAVING random()264 }265} {0 {hardware 1 0}}266 267do_test fuzz-1.18 {268 catchsql {269 SELECT -2147483649 << upper('fault' NOT IN (270 SELECT ALL (271 SELECT ALL -1272 ORDER BY -2147483649273 LIMIT (274 SELECT ALL (275 SELECT 0 EXCEPT SELECT DISTINCT 'experiments' ORDER BY 1 ASC276 )277 )278 OFFSET EXISTS (279 SELECT ALL 280 (SELECT ALL -2147483648) NOT IN (281 SELECT ALL 123456789.1234567899282 ) IN (SELECT 2147483649) 283 FROM sqlite_master284 ) NOT IN (SELECT ALL 'The')285 )286 ))287 }288} {0 {{}}}289 290# At one point the following INSERT statement caused an assert() to fail.291# 292do_test fuzz-1.19 {293 execsql { CREATE TABLE t1(a) }294 catchsql {295 INSERT INTO t1 VALUES( 296 CASE WHEN NULL THEN NULL ELSE ( SELECT 0 ORDER BY 456 ) END 297 )298 }299} {1 {1st ORDER BY term out of range - should be between 1 and 1}}300do_test fuzz-1.20 {301 execsql { DROP TABLE t1 }302} {}303 304#----------------------------------------------------------------305# Test some fuzzily generated expressions.306#307do_fuzzy_test fuzz-2 -template { SELECT [Expr] }308 309do_test fuzz-3.1 {310 execsql {311 CREATE TABLE abc(a, b, c);312 CREATE TABLE def(a, b, c);313 CREATE TABLE ghi(a, b, c);314 }315} {}316set ::TableList [list abc def ghi]317 318#----------------------------------------------------------------319# Test some fuzzily generated SELECT statements.320#321do_fuzzy_test fuzz-3.2 -template {[Select]}322 323#----------------------------------------------------------------324# Insert a small amount of data into the database and then run 325# some more generated SELECT statements.326#327do_test fuzz-4.1 {328 execsql {329 INSERT INTO abc VALUES(1, 2, 3);330 INSERT INTO abc VALUES(4, 5, 6);331 INSERT INTO abc VALUES(7, 8, 9);332 INSERT INTO def VALUES(1, 2, 3);333 INSERT INTO def VALUES(4, 5, 6);334 INSERT INTO def VALUES(7, 8, 9);335 INSERT INTO ghi VALUES(1, 2, 3);336 INSERT INTO ghi VALUES(4, 5, 6);337 INSERT INTO ghi VALUES(7, 8, 9);338 CREATE INDEX abc_i ON abc(a, b, c);339 CREATE INDEX def_i ON def(c, a, b);340 CREATE INDEX ghi_i ON ghi(b, c, a);341 }342} {}343do_fuzzy_test fuzz-4.2 -template {[Select]}344 345#----------------------------------------------------------------346# Test some fuzzy INSERT statements:347#348do_test fuzz-5.1 {execsql BEGIN} {}349do_fuzzy_test fuzz-5.2 -template {[Insert]} -errorlist table350integrity_check fuzz-5.2.integrity351do_test fuzz-5.3 {execsql COMMIT} {}352integrity_check fuzz-5.4.integrity353 354#----------------------------------------------------------------355# Now that there is data in the database, run some more SELECT 356# statements357#358set ::ColumnList [list a b c]359set E {{no such col} {ambiguous column name}}360do_fuzzy_test fuzz-6.1 -template {[Select]} -errorlist $E361 362#----------------------------------------------------------------363# Run some SELECTs, INSERTs, UPDATEs and DELETEs in a transaction.364#365set E {{no such col} {ambiguous column name} {table}}366do_test fuzz-7.1 {execsql BEGIN} {}367do_fuzzy_test fuzz-7.2 -template {[Statement]} -errorlist $E368integrity_check fuzz-7.3.integrity369do_test fuzz-7.4 {execsql COMMIT} {}370integrity_check fuzz-7.5.integrity371 372#----------------------------------------------------------------373# Many CREATE and DROP TABLE statements:374#375set E [list table view duplicate {no such col} {ambiguous column name} {use DROP}]376do_fuzzy_test fuzz-8.1 -template {[CreateOrDropTableOrView]} -errorlist $E377 378close $::log379finish_test380