Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Bug fixes and additional test cases for the distinct-NULL patch. (CVS 592) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
0e268d0c0faa02c3f95e1567cf631b7a |
| User & Date: | drh 2002-05-27 01:04:51 |
Context
|
2002-05-27
| ||
| 03:25 | Fix the "alias.*" bug found by Bernie Cosell and reported on the newsgroup. (CVS 593) check-in: f562d542 user: drh tags: trunk | |
| 01:04 | Bug fixes and additional test cases for the distinct-NULL patch. (CVS 592) check-in: 0e268d0c user: drh tags: trunk | |
|
2002-05-26
| ||
| 23:24 | Require the INSTEAD OF syntax to create triggers on database views. (CVS 591) check-in: d9e48cd5 user: danielk1977 tags: trunk | |
Changes
Changes to src/vdbe.c.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
|
** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** ** $Id: vdbe.c,v 1.149 2002/05/26 20:54:34 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** The following global variable is incremented every time a cursor ** moves, either by the OP_MoveTo or the OP_Next opcode. The test ................................................................................ } /* Opcode: And * * * ** ** Pop two values off the stack. Take the logical AND of the ** two values and push the resulting boolean value back onto the ** stack. ** If either operand is NULL, the result is NULL. */ /* Opcode: Or * * * ** ** Pop two values off the stack. Take the logical OR of the ** two values and push the resulting boolean value back onto the ** stack. ** If either operand is NULL, the result is NULL. */ case OP_And: case OP_Or: { int tos = p->tos; int nos = tos - 1; int c; VERIFY( if( nos<0 ) goto not_enough_stack; ) if( (aStack[tos].flags | aStack[nos].flags) & STK_Null ){ POPSTACK; Release(p, nos); aStack[nos].flags = STK_Null; break; } Integerify(p, tos); Integerify(p, nos); if( pOp->opcode==OP_And ){ c = aStack[tos].i && aStack[nos].i; }else{ c = aStack[tos].i || aStack[nos].i; } POPSTACK; Release(p, nos); aStack[nos].i = c; aStack[nos].flags = STK_Int; break; } /* Opcode: Negative * * * ** ** Treat the top of the stack as a numeric quantity. Replace it ** with its additive inverse. If the top of the stack is NULL |
|
<
<
|
>
|
<
|
|
|
>
<
>
>
>
|
>
>
<
>
>
<
>
>
|
>
>
>
|
|
>
|
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
|
** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** ** $Id: vdbe.c,v 1.150 2002/05/27 01:04:51 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** The following global variable is incremented every time a cursor ** moves, either by the OP_MoveTo or the OP_Next opcode. The test ................................................................................ } /* Opcode: And * * * ** ** Pop two values off the stack. Take the logical AND of the ** two values and push the resulting boolean value back onto the ** stack. */ /* Opcode: Or * * * ** ** Pop two values off the stack. Take the logical OR of the ** two values and push the resulting boolean value back onto the ** stack. */ case OP_And: case OP_Or: { int tos = p->tos; int nos = tos - 1; int v1, v2; /* 0==TRUE, 1==FALSE, 2==UNKNOWN or NULL */ VERIFY( if( nos<0 ) goto not_enough_stack; ) if( aStack[tos].flags & STK_Null ){ v1 = 2; }else{ Integerify(p, tos); v1 = aStack[tos].i==0; } if( aStack[nos].flags & STK_Null ){ v2 = 2; }else{ Integerify(p, nos); v2 = aStack[nos].i==0; } if( pOp->opcode==OP_And ){ static const unsigned char and_logic[] = { 0, 1, 2, 1, 1, 1, 2, 1, 2 }; v1 = and_logic[v1*3+v2]; }else{ static const unsigned char or_logic[] = { 0, 0, 0, 0, 1, 2, 0, 2, 2 }; v1 = or_logic[v1*3+v2]; } POPSTACK; Release(p, nos); if( v1==2 ){ aStack[nos].flags = STK_Null; }else{ aStack[nos].i = v1==0; aStack[nos].flags = STK_Int; } break; } /* Opcode: Negative * * * ** ** Treat the top of the stack as a numeric quantity. Replace it ** with its additive inverse. If the top of the stack is NULL |
Changes to test/expr.test.
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 .. 78 79 80 81 82 83 84 85 86 87 88 89 90 91 ... 103 104 105 106 107 108 109 110 111 112 113 114 115 116 ... 201 202 203 204 205 206 207 208 209 210 211 212 213 214 ... 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 ... 291 292 293 294 295 296 297 298 299 300 301 302 303 304 ... 340 341 342 343 344 345 346 347 348 |
# 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 of this file is testing expressions. # # $Id: expr.test,v 1.20 2002/05/26 20:54:34 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)} ................................................................................ test_expr expr-1.50 {i1=99999999999, i2=99999999998} {i1<i2} 0 test_expr expr-1.51 {i1=99999999999, i2=99999999998} {i1=i2} 0 test_expr expr-1.52 {i1=99999999999, i2=99999999998} {i1>i2} 1 test_expr expr-1.53 {i1=099999999999, i2=99999999999} {i1<i2} 0 test_expr expr-1.54 {i1=099999999999, i2=99999999999} {i1=i2} 1 test_expr expr-1.55 {i1=099999999999, i2=99999999999} {i1>i2} 0 test_expr expr-1.56 {i1=25, i2=11} {i1%i2} 3 test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57 test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11 test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782 test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026 test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024 test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0 ................................................................................ test_expr expr-2.18 {r1=2.34, r2=2.34} {r2!=r1} 0 test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1 test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0 test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1 test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11} test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57} test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3 test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1 test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0 test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0 test_expr expr-3.4 {t1='abc', t2='xyz'} {t1<=t2} 1 test_expr expr-3.5 {t1='xyz', t2='abc'} {t1<=t2} 0 test_expr expr-3.6 {t1='abc', t2='abc'} {t1<=t2} 1 ................................................................................ if {$go} { test_expr expr-5.50 "t1='a\266c', t2='A_C'" {t1 LIKE t2} 1 test_expr expr-5.51 "t1='a\347', t2='A_'" {t1 LIKE t2} 1 test_expr expr-5.52 "t1='ax\351', t2='A_\351'" {t1 LIKE t2} 1 test_expr expr-5.53 "t1='ax\241', t2='A_%'" {t1 LIKE t2} 1 } } test_expr expr-6.1 {t1='abc', t2='xyz'} {t1 GLOB t2} 0 test_expr expr-6.2 {t1='abc', t2='ABC'} {t1 GLOB t2} 0 test_expr expr-6.3 {t1='abc', t2='A?C'} {t1 GLOB t2} 0 test_expr expr-6.4 {t1='abc', t2='a?c'} {t1 GLOB t2} 1 test_expr expr-6.5 {t1='abc', t2='abc?'} {t1 GLOB t2} 0 test_expr expr-6.6 {t1='abc', t2='A*C'} {t1 GLOB t2} 0 ................................................................................ test_expr expr-6.20 {t1='abc', t2='a[^]b]c'} {t1 GLOB t2} 0 test_expr expr-6.21 {t1='abcdefg', t2='a*[de]g'} {t1 GLOB t2} 0 test_expr expr-6.22 {t1='abcdefg', t2='a*[^de]g'} {t1 GLOB t2} 1 test_expr expr-6.23 {t1='abcdefg', t2='a*?g'} {t1 GLOB t2} 1 test_expr expr-6.24 {t1='ac', t2='a*c'} {t1 GLOB t2} 1 test_expr expr-6.25 {t1='ac', t2='a*?c'} {t1 GLOB t2} 0 test_expr expr-case.1 {i1=1, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne test_expr expr-case.2 {i1=2, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} eq test_expr expr-case.3 {i1=2} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} two test_expr expr-case.4 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} error test_expr expr-case.5 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END} {{}} test_expr expr-case.6 {i1=7} \ { CASE WHEN i1 < 5 THEN 'low' WHEN i1 < 10 THEN 'medium' WHEN i1 < 15 THEN 'high' ELSE 'error' END} medium # These tests only work on versions of TCL that support Unicode # if {"\u1234"!="u1234" && [sqlite -encoding]=="UTF-8"} { test_expr expr-6.26 "t1='a\u0080c', t2='a?c'" {t1 GLOB t2} 1 test_expr expr-6.27 "t1='a\u07ffc', t2='a?c'" {t1 GLOB t2} 1 test_expr expr-6.28 "t1='a\u0800c', t2='a?c'" {t1 GLOB t2} 1 ................................................................................ test_expr expr-6.58 "t1='a\266b', t2='a\[\266-\270\]b'" {t1 GLOB t2} 1 test_expr expr-6.59 "t1='a\266b', t2='a\[\267-\270\]b'" {t1 GLOB t2} 0 test_expr expr-6.60 "t1='a\266b', t2='a\[x-\267\]b'" {t1 GLOB t2} 1 test_expr expr-6.61 "t1='a\266b', t2='a\[x-\266\]b'" {t1 GLOB t2} 1 test_expr expr-6.62 "t1='a\266b', t2='a\[x-\265\]b'" {t1 GLOB t2} 0 } } # The sqliteExprIfFalse and sqliteExprIfTrue routines are only # executed as part of a WHERE clause. Create a table suitable # for testing these functions. # execsql {DROP TABLE test1} execsql {CREATE TABLE test1(a int, b int);} ................................................................................ {2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} test_expr2 expr-7.23 {(a notnull AND a<4) OR a==8} {1 2 3 8} test_expr2 expr-7.24 {a LIKE '2_' OR a==8} {8 20} test_expr2 expr-7.25 {a GLOB '2?' OR a==8} {8 20} test_expr2 expr-7.26 {a isnull OR a=8} {{} 8} test_expr2 expr-7.27 {a notnull OR a=8} \ {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} finish_test |
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < < < < < < < < < < < < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 .. 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 ... 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 ... 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 ... 275 276 277 278 279 280 281 282 283 284 285 286 287 288 ... 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 ... 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 |
# 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 of this file is testing expressions. # # $Id: expr.test,v 1.21 2002/05/27 01:04:51 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)} ................................................................................ test_expr expr-1.50 {i1=99999999999, i2=99999999998} {i1<i2} 0 test_expr expr-1.51 {i1=99999999999, i2=99999999998} {i1=i2} 0 test_expr expr-1.52 {i1=99999999999, i2=99999999998} {i1>i2} 1 test_expr expr-1.53 {i1=099999999999, i2=99999999999} {i1<i2} 0 test_expr expr-1.54 {i1=099999999999, i2=99999999999} {i1=i2} 1 test_expr expr-1.55 {i1=099999999999, i2=99999999999} {i1>i2} 0 test_expr expr-1.56 {i1=25, i2=11} {i1%i2} 3 test_expr expr-1.58 {i1=NULL, i2=1} {coalesce(i1+i2,99)} 99 test_expr expr-1.59 {i1=1, i2=NULL} {coalesce(i1+i2,99)} 99 test_expr expr-1.60 {i1=NULL, i2=NULL} {coalesce(i1+i2,99)} 99 test_expr expr-1.61 {i1=NULL, i2=1} {coalesce(i1-i2,99)} 99 test_expr expr-1.62 {i1=1, i2=NULL} {coalesce(i1-i2,99)} 99 test_expr expr-1.63 {i1=NULL, i2=NULL} {coalesce(i1-i2,99)} 99 test_expr expr-1.64 {i1=NULL, i2=1} {coalesce(i1*i2,99)} 99 test_expr expr-1.65 {i1=1, i2=NULL} {coalesce(i1*i2,99)} 99 test_expr expr-1.66 {i1=NULL, i2=NULL} {coalesce(i1*i2,99)} 99 test_expr expr-1.67 {i1=NULL, i2=1} {coalesce(i1/i2,99)} 99 test_expr expr-1.68 {i1=1, i2=NULL} {coalesce(i1/i2,99)} 99 test_expr expr-1.69 {i1=NULL, i2=NULL} {coalesce(i1/i2,99)} 99 test_expr expr-1.70 {i1=NULL, i2=1} {coalesce(i1<i2,99)} 99 test_expr expr-1.71 {i1=1, i2=NULL} {coalesce(i1>i2,99)} 99 test_expr expr-1.72 {i1=NULL, i2=NULL} {coalesce(i1<=i2,99)} 99 test_expr expr-1.73 {i1=NULL, i2=1} {coalesce(i1>=i2,99)} 99 test_expr expr-1.74 {i1=1, i2=NULL} {coalesce(i1!=i2,99)} 99 test_expr expr-1.75 {i1=NULL, i2=NULL} {coalesce(i1==i2,99)} 99 test_expr expr-1.76 {i1=NULL, i2=NULL} {coalesce(not i1,99)} 99 test_expr expr-1.77 {i1=NULL, i2=NULL} {coalesce(-i1,99)} 99 test_expr expr-1.78 {i1=NULL, i2=NULL} {coalesce(i1 IS NULL AND i2=5,99)} 99 test_expr expr-1.79 {i1=NULL, i2=NULL} {coalesce(i1 IS NULL OR i2=5,99)} 1 test_expr expr-1.80 {i1=NULL, i2=NULL} {coalesce(i1=5 AND i2 IS NULL,99)} 99 test_expr expr-1.81 {i1=NULL, i2=NULL} {coalesce(i1=5 OR i2 IS NULL,99)} 1 test_expr expr-1.82 {i1=NULL, i2=3} {coalesce(min(i1,i2,1),99)} 99 test_expr expr-1.83 {i1=NULL, i2=3} {coalesce(max(i1,i2,1),99)} 99 test_expr expr-1.84 {i1=3, i2=NULL} {coalesce(min(i1,i2,1),99)} 99 test_expr expr-1.85 {i1=3, i2=NULL} {coalesce(max(i1,i2,1),99)} 99 test_expr expr-1.86 {i1=3, i2=8} {5 between i1 and i2} 1 test_expr expr-1.87 {i1=3, i2=8} {5 not between i1 and i2} 0 test_expr expr-1.88 {i1=3, i2=8} {55 between i1 and i2} 0 test_expr expr-1.89 {i1=3, i2=8} {55 not between i1 and i2} 1 test_expr expr-1.90 {i1=3, i2=NULL} {5 between i1 and i2} {{}} test_expr expr-1.91 {i1=3, i2=NULL} {5 not between i1 and i2} {{}} test_expr expr-1.92 {i1=3, i2=NULL} {2 between i1 and i2} 0 test_expr expr-1.93 {i1=3, i2=NULL} {2 not between i1 and i2} 1 test_expr expr-1.94 {i1=NULL, i2=8} {2 between i1 and i2} {{}} test_expr expr-1.95 {i1=NULL, i2=8} {2 not between i1 and i2} {{}} test_expr expr-1.94 {i1=NULL, i2=8} {55 between i1 and i2} 0 test_expr expr-1.95 {i1=NULL, i2=8} {55 not between i1 and i2} 1 test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57 test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11 test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782 test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026 test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024 test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0 ................................................................................ test_expr expr-2.18 {r1=2.34, r2=2.34} {r2!=r1} 0 test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1 test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0 test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1 test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11} test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57} test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3 test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99.0 test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1 test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0 test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0 test_expr expr-3.4 {t1='abc', t2='xyz'} {t1<=t2} 1 test_expr expr-3.5 {t1='xyz', t2='abc'} {t1<=t2} 0 test_expr expr-3.6 {t1='abc', t2='abc'} {t1<=t2} 1 ................................................................................ if {$go} { test_expr expr-5.50 "t1='a\266c', t2='A_C'" {t1 LIKE t2} 1 test_expr expr-5.51 "t1='a\347', t2='A_'" {t1 LIKE t2} 1 test_expr expr-5.52 "t1='ax\351', t2='A_\351'" {t1 LIKE t2} 1 test_expr expr-5.53 "t1='ax\241', t2='A_%'" {t1 LIKE t2} 1 } } test_expr expr-5.54 {t1='abc', t2=NULL} {t1 LIKE t2} {{}} test_expr expr-5.55 {t1='abc', t2=NULL} {t1 NOT LIKE t2} {{}} test_expr expr-5.56 {t1='abc', t2=NULL} {t2 LIKE t1} {{}} test_expr expr-5.57 {t1='abc', t2=NULL} {t2 NOT LIKE t1} {{}} test_expr expr-6.1 {t1='abc', t2='xyz'} {t1 GLOB t2} 0 test_expr expr-6.2 {t1='abc', t2='ABC'} {t1 GLOB t2} 0 test_expr expr-6.3 {t1='abc', t2='A?C'} {t1 GLOB t2} 0 test_expr expr-6.4 {t1='abc', t2='a?c'} {t1 GLOB t2} 1 test_expr expr-6.5 {t1='abc', t2='abc?'} {t1 GLOB t2} 0 test_expr expr-6.6 {t1='abc', t2='A*C'} {t1 GLOB t2} 0 ................................................................................ test_expr expr-6.20 {t1='abc', t2='a[^]b]c'} {t1 GLOB t2} 0 test_expr expr-6.21 {t1='abcdefg', t2='a*[de]g'} {t1 GLOB t2} 0 test_expr expr-6.22 {t1='abcdefg', t2='a*[^de]g'} {t1 GLOB t2} 1 test_expr expr-6.23 {t1='abcdefg', t2='a*?g'} {t1 GLOB t2} 1 test_expr expr-6.24 {t1='ac', t2='a*c'} {t1 GLOB t2} 1 test_expr expr-6.25 {t1='ac', t2='a*?c'} {t1 GLOB t2} 0 # These tests only work on versions of TCL that support Unicode # if {"\u1234"!="u1234" && [sqlite -encoding]=="UTF-8"} { test_expr expr-6.26 "t1='a\u0080c', t2='a?c'" {t1 GLOB t2} 1 test_expr expr-6.27 "t1='a\u07ffc', t2='a?c'" {t1 GLOB t2} 1 test_expr expr-6.28 "t1='a\u0800c', t2='a?c'" {t1 GLOB t2} 1 ................................................................................ test_expr expr-6.58 "t1='a\266b', t2='a\[\266-\270\]b'" {t1 GLOB t2} 1 test_expr expr-6.59 "t1='a\266b', t2='a\[\267-\270\]b'" {t1 GLOB t2} 0 test_expr expr-6.60 "t1='a\266b', t2='a\[x-\267\]b'" {t1 GLOB t2} 1 test_expr expr-6.61 "t1='a\266b', t2='a\[x-\266\]b'" {t1 GLOB t2} 1 test_expr expr-6.62 "t1='a\266b', t2='a\[x-\265\]b'" {t1 GLOB t2} 0 } } test_expr expr-6.63 {t1=NULL, t2='a*?c'} {t1 GLOB t2} {{}} test_expr expr-6.64 {t1='ac', t2=NULL} {t1 GLOB t2} {{}} test_expr expr-6.65 {t1=NULL, t2='a*?c'} {t1 NOT GLOB t2} {{}} test_expr expr-6.66 {t1='ac', t2=NULL} {t1 NOT GLOB t2} {{}} test_expr expr-case.1 {i1=1, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne test_expr expr-case.2 {i1=2, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} eq test_expr expr-case.3 {i1=NULL, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} {{}} test_expr expr-case.4 {i1=2, i2=NULL} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} {{}} test_expr expr-case.5 {i1=2} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} two test_expr expr-case.6 {i1=1} \ {CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} one test_expr expr-case.7 {i1=2} \ {CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} {{}} test_expr expr-case.8 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} {{}} test_expr expr-case.9 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} error test_expr expr-case.10 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END} {{}} test_expr expr-case.11 {i1=7} \ { CASE WHEN i1 < 5 THEN 'low' WHEN i1 < 10 THEN 'medium' WHEN i1 < 15 THEN 'high' ELSE 'error' END} medium # The sqliteExprIfFalse and sqliteExprIfTrue routines are only # executed as part of a WHERE clause. Create a table suitable # for testing these functions. # execsql {DROP TABLE test1} execsql {CREATE TABLE test1(a int, b int);} ................................................................................ {2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} test_expr2 expr-7.23 {(a notnull AND a<4) OR a==8} {1 2 3 8} test_expr2 expr-7.24 {a LIKE '2_' OR a==8} {8 20} test_expr2 expr-7.25 {a GLOB '2?' OR a==8} {8 20} test_expr2 expr-7.26 {a isnull OR a=8} {{} 8} test_expr2 expr-7.27 {a notnull OR a=8} \ {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} test_expr2 expr-7.28 {a<0 OR b=0} {{}} test_expr2 expr-7.29 {b=0 OR a<0} {{}} test_expr2 expr-7.30 {a<0 AND b=0} {} test_expr2 expr-7.31 {b=0 AND a<0} {} test_expr2 expr-7.32 {a IS NULL AND (a<0 OR b=0)} {{}} test_expr2 expr-7.33 {a IS NULL AND (b=0 OR a<0)} {{}} test_expr2 expr-7.34 {a IS NULL AND (a<0 AND b=0)} {} test_expr2 expr-7.35 {a IS NULL AND (b=0 AND a<0)} {} test_expr2 expr-7.32 {(a<0 OR b=0) AND a IS NULL} {{}} test_expr2 expr-7.33 {(b=0 OR a<0) AND a IS NULL} {{}} test_expr2 expr-7.34 {(a<0 AND b=0) AND a IS NULL} {} test_expr2 expr-7.35 {(b=0 AND a<0) AND a IS NULL} {} test_expr2 expr-7.36 {a<2 OR (a<0 OR b=0)} {{} 1} test_expr2 expr-7.37 {a<2 OR (b=0 OR a<0)} {{} 1} test_expr2 expr-7.38 {a<2 OR (a<0 AND b=0)} {1} test_expr2 expr-7.39 {a<2 OR (b=0 AND a<0)} {1} test_expr2 expr-7.40 {((a<2 OR a IS NULL) AND b<3) OR b>1e10} {{} 1} test_expr2 expr-7.41 {a BETWEEN -1 AND 1} {1} test_expr2 expr-7.42 {a NOT BETWEEN 2 AND 100} {1} test_expr2 expr-7.50 {((a between 1 and 2 OR 0) AND 1) OR 0} {1 2} test_expr2 expr-7.51 {((a not between 3 and 100 OR 0) AND 1) OR 0} {1 2} test_expr2 expr-7.52 {((a in (1,2) OR 0) AND 1) OR 0} {1 2} test_expr2 expr-7.53 {((a not in (3,4,5,6,7,8,9,10) OR 0) AND a<11) OR 0} {1 2} test_expr2 expr-7.54 {((a>0 OR 0) AND a<3) OR 0} {1 2} test_expr2 expr-7.55 {((a in (1,2) OR 0) IS NULL AND 1) OR 0} {{}} test_expr2 expr-7.56 {((a not in (3,4,5,6,7,8,9,10) IS NULL OR 0) AND 1) OR 0} \ {{}} test_expr2 expr-7.57 {((a>0 IS NULL OR 0) AND 1) OR 0} {{}} finish_test |
Changes to test/func.test.
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 .. 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 ... 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 |
# 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 of this file is testing built-in functions. # # $Id: func.test,v 1.10 2002/04/06 14:10:47 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # do_test func-0.0 { ................................................................................ lappend r $msg } {1 {wrong number of arguments to function length()}} do_test func-1.3 { execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1) ORDER BY length(t1)} } {2 1 4 2 7 1 8 1} do_test func-1.4 { execsql {SELECT length(a) FROM t2} } {1 0 3 0 5} # Check out the substr() function # do_test func-2.0 { execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} } {fr is pr so th} do_test func-2.1 { ................................................................................ do_test func-4.10 { catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} } {0 {x3.0y x-12345.68y x-5.000y}} do_test func-4.11 { catchsql {SELECT round() FROM t1 ORDER BY a} } {1 {wrong number of arguments to function round()}} do_test func-4.12 { execsql {SELECT round(a,2) FROM t2} } {1.00 0.00 345.00 0.00 67890.00} do_test func-4.13 { execsql {SELECT round(t1,2) FROM tbl1} } {0.00 0.00 0.00 0.00 0.00} # Test the upper() and lower() functions # do_test func-5.1 { |
| | | | | |
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 .. 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 ... 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 |
# 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 of this file is testing built-in functions. # # $Id: func.test,v 1.11 2002/05/27 01:04:51 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # do_test func-0.0 { ................................................................................ lappend r $msg } {1 {wrong number of arguments to function length()}} do_test func-1.3 { execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1) ORDER BY length(t1)} } {2 1 4 2 7 1 8 1} do_test func-1.4 { execsql {SELECT coalesce(length(a),-1) FROM t2} } {1 -1 3 -1 5} # Check out the substr() function # do_test func-2.0 { execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} } {fr is pr so th} do_test func-2.1 { ................................................................................ do_test func-4.10 { catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} } {0 {x3.0y x-12345.68y x-5.000y}} do_test func-4.11 { catchsql {SELECT round() FROM t1 ORDER BY a} } {1 {wrong number of arguments to function round()}} do_test func-4.12 { execsql {SELECT coalesce(round(a,2),'nil') FROM t2} } {1.00 nil 345.00 nil 67890.00} do_test func-4.13 { execsql {SELECT round(t1,2) FROM tbl1} } {0.00 0.00 0.00 0.00 0.00} # Test the upper() and lower() functions # do_test func-5.1 { |
Changes to test/minmax.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
118
119
120
121
122
123
124
125
126
|
# #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # # $Id: minmax.test,v 1.1 2002/02/19 22:42:06 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax-1.0 { execsql { BEGIN; ................................................................................ SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) } } {999} do_test minmax-3.3 { set sqlite_search_count } {0} finish_test |
|
>
>
>
>
|
>
>
|
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
# #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # # $Id: minmax.test,v 1.2 2002/05/27 01:04:51 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax-1.0 { execsql { BEGIN; ................................................................................ SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) } } {999} do_test minmax-3.3 { set sqlite_search_count } {0} do_test minmax-4.1 { execsql { SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y') } } {-1 -1} finish_test |
Changes to test/select1.test.
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 ... 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 ... 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 |
# 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 of this file is testing the SELECT statement. # # $Id: select1.test,v 1.26 2002/05/24 20:31:38 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. # do_test select1-1.1 { ................................................................................ } {0 {23 45}} do_test select1-2.13 { set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] lappend v $msg } {0 34} do_test select1-2.13.1 { execsql {SELECT coalesce(max(a),'xyzzy') FROM t3} } {abc} do_test select1-2.13.1 { execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3} } {xyzzy} do_test select1-2.14 { set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function SUM()}} do_test select1-2.15 { ................................................................................ execsql { SELECT f1 FROM test1 WHERE 5-3==2 ORDER BY f1 } } {11 33} do_test select1-8.4 { execsql { SELECT f1/(f1-11), min(f1/(f1-11),5), max(f1/(f1-33),6) FROM test1 ORDER BY f1 } } {{} {} 6 1.5 1.5 6} do_test select1-8.5 { execsql { SELECT min(1,2,3), -max(1,2,3) FROM test1 ORDER BY f1 } } {1 -3 1 -3} |
| | | > | > | |
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 ... 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 ... 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 |
# 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 of this file is testing the SELECT statement. # # $Id: select1.test,v 1.27 2002/05/27 01:04:51 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. # do_test select1-1.1 { ................................................................................ } {0 {23 45}} do_test select1-2.13 { set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] lappend v $msg } {0 34} do_test select1-2.13.1 { execsql {SELECT coalesce(max(a),'xyzzy') FROM t3} } {xyzzy} do_test select1-2.13.2 { execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3} } {xyzzy} do_test select1-2.14 { set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function SUM()}} do_test select1-2.15 { ................................................................................ execsql { SELECT f1 FROM test1 WHERE 5-3==2 ORDER BY f1 } } {11 33} do_test select1-8.4 { execsql { SELECT coalesce(f1/(f1-11),'x'), coalesce(min(f1/(f1-11),5),'y'), coalesce(max(f1/(f1-33),6),'z') FROM test1 ORDER BY f1 } } {x y 6 1.5 1.5 z} do_test select1-8.5 { execsql { SELECT min(1,2,3), -max(1,2,3) FROM test1 ORDER BY f1 } } {1 -3 1 -3} |
Changes to test/select4.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
285
286
287
288
289
290
291
292
293
294
295
296
297
298
|
# May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing UNION, INTERSECT and EXCEPT operators # in SELECT statements. # # $Id: select4.test,v 1.7 2002/05/26 20:54:35 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # set fd [open data1.txt w] ................................................................................ do_test select4-6.6 { execsql { SELECT DISTINCT * FROM ( SELECT 1,2 UNION ALL SELECT 1,2 ); } } {1 2} # Make sure column names are correct when a compound select appears as # an expression in the WHERE clause. # do_test select4-7.1 { execsql { |
|
>
>
>
>
>
>
>
>
|
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
|
# May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing UNION, INTERSECT and EXCEPT operators # in SELECT statements. # # $Id: select4.test,v 1.8 2002/05/27 01:04:51 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # set fd [open data1.txt w] ................................................................................ do_test select4-6.6 { execsql { SELECT DISTINCT * FROM ( SELECT 1,2 UNION ALL SELECT 1,2 ); } } {1 2} # Test distinctness of NULL in other ways. # do_test select4-6.7 { execsql { SELECT NULL EXCEPT SELECT NULL } } {{}} # Make sure column names are correct when a compound select appears as # an expression in the WHERE clause. # do_test select4-7.1 { execsql { |