sqllogictest
Check-in [f84e0eed40]
Not logged in

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

Overview
Comment:Fix the select1.proto test script so that avoids the use of NULL values. In about.wiki, warn user to not use NULL values together with ORDER BY.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f84e0eed40d53afc13917d636ea5e57caef6e647
User & Date: drh 2008-11-30 20:42:06
Context
2008-12-01
04:54
Broke out SQLite interface; Added ODBC3 interface; Minor bug fixes. check-in: 86ab9a0ad0 user: shaneh tags: trunk
2008-11-30
20:42
Fix the select1.proto test script so that avoids the use of NULL values. In about.wiki, warn user to not use NULL values together with ORDER BY. check-in: f84e0eed40 user: drh tags: trunk
13:09
Fix typos and poor wording in the about.wiki document. check-in: f30534fbb0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to about.wiki.

242
243
244
245
246
247
248
249
250




251
252
253
254
255
256
257
258
Several SELECT statements typical follow each UPDATE, DELETE, or INSERT
in order to verify that the content of the database is as expected.

It is useful to includes some NULL values in the initial data in order to
test the NULL handling logic.  Be careful, however, in that different
database engines interpret NULLs in a UNIQUE constraint differently.
SQLite, PostgreSQL, Oracle, MySQL, and Firebird do it one way.  Informix,
DB2, and MS-SQL do it another.  So while NULLs are useful test values, it
will be best to avoid using NULLs in UNIQUE columns.





One might have a large set of DELETE, INSERT, SELECT, and UPDATE statements
that are repeated multiple times, but with various CREATE INDEX and
DROP INDEX statements in between each iteration.  Such tests seeks to
prove that the same results appear regardless of whether or not indices
are present.  Remember, the purpose of sqllogictest is to verify that the
database engine gets correct results, not that it makes effective use
of indices.







|
|
>
>
>
>








242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
Several SELECT statements typical follow each UPDATE, DELETE, or INSERT
in order to verify that the content of the database is as expected.

It is useful to includes some NULL values in the initial data in order to
test the NULL handling logic.  Be careful, however, in that different
database engines interpret NULLs in a UNIQUE constraint differently.
SQLite, PostgreSQL, Oracle, MySQL, and Firebird do it one way.  Informix,
DB2, and MS-SQL do it another.  So it is best to avoid using NULLs in
UNIQUE column. Also, NULLs sort differently on different
engines.  SQLite sorts NULL values first.  PostgreSQL and MySQL and most
other database engiens sort NULL values last.  So one should avoid ORDER
BY and LIMIT clauses and use the "rowsort" or "valuesort" parameter on
queries that might return NULLs.

One might have a large set of DELETE, INSERT, SELECT, and UPDATE statements
that are repeated multiple times, but with various CREATE INDEX and
DROP INDEX statements in between each iteration.  Such tests seeks to
prove that the same results appear regardless of whether or not indices
are present.  Remember, the purpose of sqllogictest is to verify that the
database engine gets correct results, not that it makes effective use
of indices.

Changes to proto/select1.proto.

more than 10,000 changes

Changes to proto/select1.tcl.

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
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 a-c a-d a-e b-c b-d b-e c-d c-e d-e




           abs(a) abs(a+b) coalesce(a,b,c,d,e)
           {(SELECT max(a) FROM t1)}
           {(SELECT min(b) FROM t1)}
           {(SELECT max(c)-max(d) FROM t1)}







}
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 AND d}
  {d BETWEEN 110 AND 120}
  {e+d BETWEEN a+b+10 AND c+130}





}
set nwexpr [llength $wexpr]

for {set i 0} {$i<100} {incr i} {
  set n [expr {int(rand()*($nrexpr-1))+1}]
  set r [lrange [scramble $rexpr] 1 $n]
  set sql "SELECT [join $r ",\n       "]\n  FROM t1"
  set m [expr {int(rand()*$nwexpr)}]
  if {$m>0} {
    set op [expr {rand()>0.5 ? "\n    OR " : "\n   AND "}]
    set w [lrange [scramble $wexpr] 1 $m]
    append sql "\n WHERE [join $w $op]"
  }
  incr n -1
  append sql "\n ORDER BY [join [scramble [lrange $sequence 0 $n]] ,]"
  append sql "\n LIMIT 4"



  puts "query [string range $type 0 $n] nosort"
  puts "$sql"
  puts ""
}







|













|
>
>
>
>
|
<
<
<
>
>
>
>
>
>
>













|
|
|
>
>
>
>
>



|
|


|







|
>
>
>




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
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.0} {
      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 sql "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]
    append sql "\n WHERE [join $w $op]"
  }
  incr n -1
  append sql "\n ORDER BY [join [scramble [lrange $sequence 0 $n]] ,]"
  append sql "\n LIMIT [expr {int(rand()*5)+1}]"
  if {rand()>0.5} {
   append sql " OFFSET [expr {int(rand()*5)+1}]"
  }
  puts "query [string range $type 0 $n] nosort"
  puts "$sql"
  puts ""
}

Changes to test/select1.test.

more than 10,000 changes