From 5f0ff5d527c43f5c26e0589425c8648b92b3f48e Mon Sep 17 00:00:00 2001 From: danielk1977 Date: Tue, 30 Dec 2008 12:00:12 +0000 Subject: [PATCH] Add a couple of extra tests for the "WHERE ... OR" optimization. (CVS 6077) FossilOrigin-Name: 35c87585b81866e73a58adffe6af79ddcac57cf6 --- manifest | 15 ++++----- manifest.uuid | 2 +- src/where.c | 5 +-- test/where8.test | 77 +++++++++++++++++++++++++++++++++++++++++++++-- test/where8m.test | 41 +++++++++++++++++++++++++ 5 files changed, 126 insertions(+), 14 deletions(-) create mode 100644 test/where8m.test diff --git a/manifest b/manifest index 565be866fe..e1f021a4e3 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sa\sbug\sin\swhere.c\scausing\sa\smalfunction\swhen\san\sINDEXED\sBY\sclause\sspecified\san\sunusable\sindex\son\sother\sthan\sthe\sleftmost\stable\sin\sthe\sFROM\sclause.\sTicket\s#3560.\s(CVS\s6076) -D 2008-12-30T09:45:46 +C Add\sa\scouple\sof\sextra\stests\sfor\sthe\s"WHERE\s...\sOR"\soptimization.\s(CVS\s6077) +D 2008-12-30T12:00:12 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0 F Makefile.in 77635d0909c2067cee03889a1e04ce910d8fb809 F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654 @@ -207,7 +207,7 @@ F src/vdbeblob.c b0dcebfafedcf9c0addc7901ad98f6f986c08935 F src/vdbemem.c f9c859ac17e2e05a0f249868ce4f191f69edd31d F src/vtab.c e39e011d7443a8d574b1b9cde207a35522e6df43 F src/walker.c 488c2660e13224ff70c0c82761118efb547f8f0d -F src/where.c 1e1cadcfda4c2606c23c03844268d4b90f5f048d +F src/where.c 924e77c6ebeb5980a32a4653c77e6838efa47da9 F tclinstaller.tcl 4356d9d94d2b5ed5e68f9f0c80c4df3048dd7617 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/alias.test 597662c5d777a122f9a3df0047ea5c5bd383a911 @@ -656,7 +656,8 @@ F test/where4.test e9b9e2f2f98f00379e6031db6a6fca29bae782a2 F test/where5.test fdf66f96d29a064b63eb543e28da4dfdccd81ad2 F test/where6.test 42c4373595f4409d9c6a9987b4a60000ad664faf F test/where7.test 3ed6a8cb7d3e1e8c289e5e21f460c7a4d3f129e2 -F test/where8.test fa8782186387b3391266e5e7abacf9591dbb9ee8 +F test/where8.test 81036f3f79054309da744fcffc9ce7ed97b5b4a9 +F test/where8m.test c1010d61826412ff66abd29bfb32e5d6b37d965c F test/wherelimit.test 5e9fd41e79bb2b2d588ed999d641d9c965619b31 F test/zeroblob.test 792124852ec61458a2eb527b5091791215e0be95 F tool/diffdb.c 7524b1b5df217c20cd0431f6789851a4e0cb191b @@ -687,7 +688,7 @@ F tool/speedtest16.c c8a9c793df96db7e4933f0852abb7a03d48f2e81 F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e -P b8898d132e84888dc7c51b2f1ab67f78cc21f31b -R b7839f0e2e3e66238c8ec07900a2d5d4 +P f8ff0212129ce602a1e1e9ad702c47b9302f2f21 +R 5140a353d45cc5ebc0461adb0016c60d U danielk1977 -Z 3f55cc2a8dd0beb6d8e0657bdc1d6da5 +Z 8528f1006b3e76d7ecb271be0a1fddc3 diff --git a/manifest.uuid b/manifest.uuid index 29c63f3a8f..d5297adf3a 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -f8ff0212129ce602a1e1e9ad702c47b9302f2f21 \ No newline at end of file +35c87585b81866e73a58adffe6af79ddcac57cf6 \ No newline at end of file diff --git a/src/where.c b/src/where.c index 2efd6842c4..061b38a6d8 100644 --- a/src/where.c +++ b/src/where.c @@ -16,7 +16,7 @@ ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** -** $Id: where.c,v 1.351 2008/12/30 09:45:46 danielk1977 Exp $ +** $Id: where.c,v 1.352 2008/12/30 12:00:12 danielk1977 Exp $ */ #include "sqliteInt.h" @@ -522,14 +522,11 @@ static u16 operatorMask(int op){ c = WO_IN; }else if( op==TK_ISNULL ){ c = WO_ISNULL; - }else if( op==TK_OR ){ - c = WO_OR; }else{ assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff ); c = (u16)(WO_EQ<<(op-TK_EQ)); } assert( op!=TK_ISNULL || c==WO_ISNULL ); - assert( op!=TK_OR || c==WO_OR ); assert( op!=TK_IN || c==WO_IN ); assert( op!=TK_EQ || c==WO_EQ ); assert( op!=TK_LT || c==WO_LT ); diff --git a/test/where8.test b/test/where8.test index 276b893aed..ecda8cf8da 100644 --- a/test/where8.test +++ b/test/where8.test @@ -12,7 +12,7 @@ # is testing of where.c. More specifically, the focus is the optimization # of WHERE clauses that feature the OR operator. # -# $Id: where8.test,v 1.1 2008/12/29 18:33:33 danielk1977 Exp $ +# $Id: where8.test,v 1.2 2008/12/30 12:00:12 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -34,7 +34,6 @@ proc execsql_status {sql {db db}} { proc execsql_status2 {sql {db db}} { set ::sqlite_search_count 0 -breakpoint set result [uplevel [list execsql_status $sql $db]] concat $result $::sqlite_search_count } @@ -115,6 +114,32 @@ do_test where8-1.12.2 { } } {I II III V 9 0 9} +do_test where8-1.13 { + execsql_status2 { + SELECT c FROM t1 + WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 + ORDER BY rowid + } +} {II III IV V VI 0 0 15} +do_test where8-1.14 { + execsql_status2 { + SELECT c FROM t1 + WHERE + a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR + b = 'seven' OR a = 8 OR b = 'nine' OR a = 10 + ORDER BY rowid + } +} {II III IV V VI VII VIII IX X 0 0 26} + +do_test where8-1.15 { + execsql_status2 { + SELECT c FROM t1 WHERE + a BETWEEN 2 AND 4 OR b = 'nine' + ORDER BY rowid + } +} {II III IV IX 0 0 10} + + #-------------------------------------------------------------------------- # Tests where8-2.*: Virtual tables @@ -220,6 +245,7 @@ do_test where8-3.8 { } {2 2 2 4 3 3 3 4 0 0} do_test where8-3.9 { + # The "OR c = 'IX'" term forces a linear scan. execsql_status { SELECT a, d FROM t1, t2 @@ -228,5 +254,52 @@ do_test where8-3.9 { } } {2 2 2 4 3 3 3 4 9 4 9 9 9 0} +do_test where8-3.10 { + execsql_status { + SELECT d FROM t2 WHERE e IS NULL OR e = 'four' + } +} {1 2 3 5 10 0 0} + +do_test where8-3.11 { + execsql_status { + SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a + } +} {1 1 2 2 3 3 4 2 4 4 0 0} +do_test where8-3.12 { + execsql_status { + SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a + } +} {1 1 2 2 3 3 4 2 4 4 0 0} +do_test where8-3.13 { + execsql_status { + SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 + } +} {1 1 2 2 3 3 4 2 4 4 9 0} + +do_test where8-3.14 { + execsql_status { + SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5 + } +} {IV V 9 0} + +do_test where8-3.15 { + execsql_status { + SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = ( + SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d + ) + } +} {I I I I I I I I I I II II II II II II II II II II III III III III III 99 0} + +#----------------------------------------------------------------------- +# The following tests - where8-4.* - verify that adding or removing +# indexes does not change the results returned by various queries. +# +do_test where8-4.1 { + execsql { + CREATE TABLE t3(a INTEGER, b REAL, c TEXT); + CREATE TABLE t4(f INTEGER, g REAL, h TEXT); + } +} {} + finish_test diff --git a/test/where8m.test b/test/where8m.test new file mode 100644 index 0000000000..f573579900 --- /dev/null +++ b/test/where8m.test @@ -0,0 +1,41 @@ +# 2008 December 23 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The focus +# is testing of where.c. More specifically, the focus is the optimization +# of WHERE clauses that feature the OR operator. +# +# $Id: where8m.test,v 1.1 2008/12/30 12:00:12 danielk1977 Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +source $testdir/malloc_common.tcl + +do_malloc_test where8m-1 -sqlprep { + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(a); + CREATE INDEX i2 ON t1(b); +} -sqlbody { + SELECT c FROM t1 + WHERE + a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR + b = 'seven' OR a = 8 OR b = 'nine' OR a = 10 + ORDER BY rowid; + + SELECT c FROM t1 WHERE + a = 1 OR a = 2 OR a = 3 OR a = 4 OR a = 5 OR a = 6; + + SELECT c FROM t1 WHERE + a BETWEEN 1 AND 3 AND b < 5 AND b > 2 AND c = 4; +} + +finish_test + -- 2.47.2