sqllogictest

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

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 ""
}