/ Check-in [0e268d0c]
Login

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:0e268d0c0faa02c3f95e1567cf631b7a04bfbdf0
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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 {