Artifact c9778689f9b04840972f0f6093802b6ba03084ad:

• File proto/select3.tcl — part of check-in [cdee9945da] at 2008-12-02 14:05:05 on branch trunk — Add the ability to label query results. Demonstrate the behavior with select3.tcl which permutes the terms of the WHERE clause. (user: drh size: 2782)

#!/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 ""
  }
}