sqllogictest
Check-in [cdee9945da]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the ability to label query results. Demonstrate the behavior with select3.tcl which permutes the terms of the WHERE clause.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: cdee9945daeb4a9acb596a397c45b7ab0ba9f2dd
User & Date: drh 2008-12-02 14:05:05
Context
2008-12-02
14:25
Make sure the number of columns returned by the query matches the number of columns that the test script expects. Generate an error if they disagree. Fix a bug in select2.tcl that was causing the wrong number of columns to be generated. check-in: fdbe3f356a user: drh tags: trunk
14:05
Add the ability to label query results. Demonstrate the behavior with select3.tcl which permutes the terms of the WHERE clause. check-in: cdee9945da user: drh tags: trunk
13:35
In select2.test, changed coalesce(a,b,c,d,e) in conditions to coalesce(a,b,c,d,e)<>0 to work with MSSQL. check-in: 1cc2a5df15 user: shaneh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added proto/select3.proto.

more than 10,000 changes

Added proto/select3.tcl.











































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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
#!/usr/bin/tclsh
#
# Run this script to generate a larger prototype test script for
# sqllogictest.
#
expr {srand(0)}

# Scramble the $inlist into a random order.
#
proc scramble {inlist} {
  set y {}
  foreach x $inlist {
    lappend y [list [expr {rand()}] $x]
  }
  set y [lsort $y]
  set outlist {}
  foreach x $y {
    lappend outlist [lindex $x 1]
  }
  return $outlist
}

puts {hash-threshold 8}
puts {}
puts {statement ok}
puts {CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER)}
puts {}

for {set i 0} {$i<30} {incr i} {
  set base [expr {$i*5+100}]
  set values {}
  for {set j 0} {$j<5} {incr j} {
    if {rand()<0.1} {
      lappend values NULL
    } else {
      lappend values [expr {$j+$base}]
    }
  }
  set values [scramble $values]
  set cols [scramble {a b c d e}]
  set sql "INSERT INTO t1([join $cols ,]) VALUES([join $values ,])"
  puts "statement ok"
  puts $sql
  puts ""
}

set rexpr {
  a b c d e
  a-b b-c c-d d-e
  a+b*2 a+b*2+c*3 a+b*2+c*3+d*4 a+b*2+c*3+d*4+e*5
  (a+b+c+d+e)/5
  abs(a) abs(b-c)
  {(SELECT count(*) FROM t1 AS x WHERE x.b<t1.b)}
  {(SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d)}
  {CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END}
  {CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
        WHEN a<b+3 THEN 333 ELSE 444 END}
  {CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333  WHEN e THEN 444 ELSE 555 END}
}
set nrexpr [llength $rexpr]
set sequence {}
set type {}
for {set i 1} {$i<=$nrexpr} {incr i} {
  lappend sequence $i
  append type I
}
set wexpr {
  a>b
  b>c
  c>d
  d>e
  {c BETWEEN b-2 AND d+2}
  {d NOT BETWEEN 110 AND 150}
  {e+d BETWEEN a+b-10 AND c+130}
  {(a>b-2 AND a<b+2)}
  {(e>a AND e<b)}
  {(c<=d-2 OR c>=d+2)}
  {(e>c OR e<d)}
  {EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)}
}
set nwexpr [llength $wexpr]

for {set i 0} {$i<1000} {incr i} {
  set n [expr {int(rand()*7)+1}]
  set r [lrange [scramble $rexpr] 1 $n]
  set select "SELECT [join $r ",\n       "]\n  FROM t1"
  set m [expr {int(rand()*4)}]
  if {$m>0} {
    set op [expr {rand()>0.5 ? "\n    OR " : "\n   AND "}]
    set w [lrange [scramble $wexpr] 1 $m]
    set where "\n WHERE [join $w $op]"
  }
  incr n -1
  set typestr [string range $type 0 $n]

  unset -nocomplain seen

  for {set j 0} {$j<=$m} {incr j} {
    if {$m>0} {
      set where "\n WHERE [join [scramble $w] $op]"
    } else {
      set where ""
    }
    if {[info exists seen($where)]} continue
    set seen($where) 1
    puts "query $typestr rowsort x$i"
    puts "$select$where"
    puts ""
    set rn [expr {int(rand()*$n)+1}]
    set seq [lrange [scramble [lrange $sequence 0 $n]] 0 $rn]
    set orderby "\n ORDER BY [join $seq ,]"
    puts "query $typestr rowsort x$i"
    puts "$select$where$orderby"
    puts ""
  }
}

Changes to src/sqllogictest.c.

84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
...
199
200
201
202
203
204
205


206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
...
239
240
241
242
243
244
245




















































246
247
248
249
250
251
252
...
478
479
480
481
482
483
484
485


486

487
488
489
490
491
492









493
494
495
496
497
498
499
  char *zLine;         /* Pointer to start of current line */
  int len;             /* Length of current line */
  int iNext;           /* index of start of next line */
  int nLine;           /* line number for the current line */
  int iEnd;            /* Index in zScript of '\000' at end of script */
  int startLine;       /* Line number of start of current record */
  int copyFlag;        /* If true, copy lines to output as they are read */
  char azToken[3][200]; /* tokenization of a line */
};

/*
** Advance the cursor to the start of the next non-comment line of the
** script.  Make p->zLine point to the start of the line.  Make p->len
** be the length of the line.  Zero-terminate the line.  Any \r at the
** end of the line is removed.
................................................................................
  int iStart;
  for(i=0; isspace(z[i]); i++){}
  *piStart = iStart = i;
  while( z[i] && !isspace(z[i]) ){ i++; }
  *pLen = i - iStart;
}



/*
** tokenize the current line in up to 3 tokens and store those values
** into p->azToken[0], p->azToken[1], and p->azToken[2].  Record the
** current line in p->startLine.
*/
static void tokenizeLine(Script *p){
  int i, j, k;
  int len, n;
  for(i=0; i<3; i++) p->azToken[i][0] = 0;
  p->startLine = p->nLine;
  for(i=j=0; j<p->len && i<3; i++){
    findToken(&p->zLine[j], &k, &len);
    j += k;
    n = len;
    if( n>=sizeof(p->azToken[0]) ){
      n = sizeof(p->azToken[0])-1;
    }
    memcpy(p->azToken[i], &p->zLine[j], n);
................................................................................
  const char **azB = (const char**)pB;
  int c = 0, i;
  for(i=0; c==0 && i<nColumn; i++){
    c = strcmp(azA[i], azB[i]);
  }
  return c;
}






















































/*
** This is the main routine.  This routine runs first.  It processes
** command-line arguments then runs the test.
*/
int main(int argc, char **argv){
................................................................................
        qsort(azResult, nResult, sizeof(azResult[0]), rowCompare);
      }else{
        fprintf(stderr, "%s:%d: unknown sort method: '%s'\n",
                zScriptFile, sScript.startLine, sScript.azToken[2]);
        nErr++;
      }

      /* Hash the results if we are over the hash threshold */


      if( hashThreshold>0 && nResult>hashThreshold ){

        for(i=0; i<nResult; i++){
          md5_add(azResult[i]);
          md5_add("\n");
        }
        sqlite3_snprintf(sizeof(zHash), zHash,
                         "%d rows hashing to %s", nResult, md5_finish());









      }

      if( verifyMode ){
        /* In verify mode, first skip over the ---- line if we are still
        ** pointing at it. */
        if( strcmp(sScript.zLine, "----")==0 ) nextLine(&sScript);








|







 







>
>








|

|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
>
>
|
>






>
>
>
>
>
>
>
>
>







84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
...
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
...
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
...
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
  char *zLine;         /* Pointer to start of current line */
  int len;             /* Length of current line */
  int iNext;           /* index of start of next line */
  int nLine;           /* line number for the current line */
  int iEnd;            /* Index in zScript of '\000' at end of script */
  int startLine;       /* Line number of start of current record */
  int copyFlag;        /* If true, copy lines to output as they are read */
  char azToken[4][200]; /* tokenization of a line */
};

/*
** Advance the cursor to the start of the next non-comment line of the
** script.  Make p->zLine point to the start of the line.  Make p->len
** be the length of the line.  Zero-terminate the line.  Any \r at the
** end of the line is removed.
................................................................................
  int iStart;
  for(i=0; isspace(z[i]); i++){}
  *piStart = iStart = i;
  while( z[i] && !isspace(z[i]) ){ i++; }
  *pLen = i - iStart;
}

#define count(X)  (sizeof(X)/sizeof(X[0]))

/*
** tokenize the current line in up to 3 tokens and store those values
** into p->azToken[0], p->azToken[1], and p->azToken[2].  Record the
** current line in p->startLine.
*/
static void tokenizeLine(Script *p){
  int i, j, k;
  int len, n;
  for(i=0; i<count(p->azToken); i++) p->azToken[i][0] = 0;
  p->startLine = p->nLine;
  for(i=j=0; j<p->len && i<count(p->azToken); i++){
    findToken(&p->zLine[j], &k, &len);
    j += k;
    n = len;
    if( n>=sizeof(p->azToken[0]) ){
      n = sizeof(p->azToken[0])-1;
    }
    memcpy(p->azToken[i], &p->zLine[j], n);
................................................................................
  const char **azB = (const char**)pB;
  int c = 0, i;
  for(i=0; c==0 && i<nColumn; i++){
    c = strcmp(azA[i], azB[i]);
  }
  return c;
}

/*
** Entry in a hash table of prior results
*/
typedef struct HashEntry HashEntry;
struct HashEntry {
  char zKey[24];      /* The search key */
  char zHash[33];     /* The hash value stored */
  HashEntry *pNext;   /* Next with same hash */
  HashEntry *pAll;    /* Next overall */
};

/*
** The hash table
*/
#define NHASH 1009
static HashEntry *aHash[NHASH];
static HashEntry *pAll;

/*
** Try to look up the value zKey in the hash table.  If the value
** does not exist, create it and return 0.  If the value does already
** exist return 0 if hash matches and 1 if the hash is different.
*/
static int checkValue(const char *zKey, const char *zHash){
  unsigned int h;
  HashEntry *p;
  unsigned int i;

  h = 0;
  for(i=0; zKey[i] && i<sizeof(p->zKey); i++){ h = h<<3 ^ h ^ zKey[i]; }
  h = h % NHASH;
  for(p = aHash[h]; p; p=p->pNext){
    if( strcmp(p->zKey, zKey)==0 ){
      return strcmp(p->zHash,zHash)!=0;
    }
  }
  p = malloc( sizeof(*p) );
  if( p==0 ){
    fprintf(stderr, "malloc failed on line %d\n", __LINE__);
    exit(1);
  }
  for(i=0; zKey[i] && i<sizeof(p->zKey)-1; i++){ p->zKey[i] = zKey[i]; }
  p->zKey[i] = 0;
  for(i=0; zHash[i] && i<sizeof(p->zHash)-1; i++){ p->zHash[i] = zHash[i]; }
  p->zHash[i] = 0;
  p->pAll = pAll;
  pAll = p;
  p->pNext = aHash[h];
  aHash[h] = p;
  return 0;
}


/*
** This is the main routine.  This routine runs first.  It processes
** command-line arguments then runs the test.
*/
int main(int argc, char **argv){
................................................................................
        qsort(azResult, nResult, sizeof(azResult[0]), rowCompare);
      }else{
        fprintf(stderr, "%s:%d: unknown sort method: '%s'\n",
                zScriptFile, sScript.startLine, sScript.azToken[2]);
        nErr++;
      }

      /* Hash the results if we are over the hash threshold or if we 
      ** there is a hash label */
      if( sScript.azToken[3][0]
       || (hashThreshold>0 && nResult>hashThreshold)
      ){
        for(i=0; i<nResult; i++){
          md5_add(azResult[i]);
          md5_add("\n");
        }
        sqlite3_snprintf(sizeof(zHash), zHash,
                         "%d rows hashing to %s", nResult, md5_finish());
        sScript.azToken[3][20] = 0;
        if( sScript.azToken[3][0]
         && checkValue(sScript.azToken[3], md5_finish())
        ){
          fprintf(stderr, "%s:%d: labeled result [%s] does not agree with "
                          "previous values\n", zScriptFile, 
                          sScript.startLine, sScript.azToken[3]);
          nErr++;
        }
      }

      if( verifyMode ){
        /* In verify mode, first skip over the ---- line if we are still
        ** pointing at it. */
        if( strcmp(sScript.zLine, "----")==0 ) nextLine(&sScript);