sqllogictest
Check-in [e9c99b6893]
Not logged in

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

Overview
Comment:Extend the select4 test script to do lots of multi-way joins.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e9c99b6893e5dd1aafd3cbe7387d3ddc4b1cbbec
User & Date: drh 2008-12-02 23:08:46
Context
2008-12-03
00:44
Add the select5 test script that stresses multi-way joins up to 64 deep. check-in: 599e260e37 user: drh tags: trunk
2008-12-02
23:08
Extend the select4 test script to do lots of multi-way joins. check-in: e9c99b6893 user: drh tags: trunk
20:57
Revise the format of the hash string when the result set size exceeds the hash threshold. Use a named file for the SQLite database. Added select4. check-in: c236fecb76 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to proto/select4.proto.

more than 10,000 changes

Changes to proto/select4.tcl.

5
6
7
8
9
10
11


12
13
14
15
16
17
18
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


41
42
43
44
45
46
47
..
52
53
54
55
56
57
58

59
60
61
62
63
64
65
..
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
...
194
195
196
197
198
199
200












































































































#
# Nine separate tables, T1 through T9, each with integer data.
# Data for table T1 is values in the range 1000..1999.  Data for
# table T2 is values in the range 2000.2999.  And so forth.  The
# data is random.
#
expr {srand(0)}



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

# Construct the schema.  9 tables, each with 5 integer columns and one
# text column.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts {statement ok}
  puts [subst {CREATE TABLE t${tn}(
  a$tn INTEGER,
  b$tn INTEGER,
  c$tn INTEGER,
  d$tn INTEGER,
  e$tn INTEGER,
  x$tn VARCHAR(30)
)}]


  puts {}
}

# Populate the tables with data
#
for {set tn 1} {$tn<=9} {incr tn} {set nrow($tn) 0}
for {set i 0} {$i<1000} {incr i} {
................................................................................
  foreach column {a b c d e} {
    set v [expr {$base+int(rand()*999)}]
    lappend cdata($column$tn) $v
    set $column $v
  }
  lappend tdata($tn) [list $a $b $c $d $e $x]
  set sql "INSERT INTO t$tn VALUES($a,$b,$c,$d,$e,'$x')"

  puts "statement ok\n$sql\n"
}

# Queries to make sure all the data got in correctly.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts "query IIIIIT rowsort all$tn\nSELECT * FROM t$tn\n"
................................................................................

# Create indices
#
# t1 gets prefix indices...
#
for {set i 0} {$i<5} {incr i} {
  puts "statement ok"

  puts "CREATE INDEX t1i$i ON t1([join [lrange {a1 b1 c1 d1 e1 x1} $i end] ,])"
  puts ""

}

# t2 gets a separate index on each column
#
foreach c {a2 b2 c2 d2 e2} {
  puts "statement ok"
  puts "CREATE INDEX t2$c ON t2($c)"
  puts ""

}

# t3 through t7 get a single index on one column
#
for {set i 0} {$i<5} {incr i} {
  set tn [expr {$i+3}]
  set cn [string index {abcde} $i]$tn
  puts "statement ok"
  puts "CREATE INDEX t$tn$cn ON t${tn}($cn)"
  puts ""


}
# t8 gets a single reverse-order index.
#
puts "statement ok"
puts "CREATE INDEX t8all ON t8(e8 DESC, d8 ASC, c8 DESC, b8 ASC, a8 DESC)"

puts ""

# t9 does not get an index.
#


# Repeat the data dumps.  The table contents should not have changed.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts "query IIIIIT rowsort all$tn\nSELECT * FROM t$tn\n"
}

................................................................................
      set w [few_row_where $tn]
      set op "\n      OR "
      append sql "   WHERE [join $w $op]\n"
    }
  }
  puts "query T valuesort\n$sql" 
}



















































































































>
>







 







|







>
>







 







>







 







>
|
|
>

>




|
|
>

>






|
|
>
>




|
>
|
>


>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
..
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
118
119
120
121
...
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
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
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
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
#
# Nine separate tables, T1 through T9, each with integer data.
# Data for table T1 is values in the range 1000..1999.  Data for
# table T2 is values in the range 2000.2999.  And so forth.  The
# data is random.
#
expr {srand(0)}
package require sqlite3
sqlite3 db :memory:

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

# Construct the schema.  9 tables, each with 5 integer columns and one
# text column.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts {statement ok}
  set sql [subst {CREATE TABLE t${tn}(
  a$tn INTEGER,
  b$tn INTEGER,
  c$tn INTEGER,
  d$tn INTEGER,
  e$tn INTEGER,
  x$tn VARCHAR(30)
)}]
  puts $sql
  db eval $sql
  puts {}
}

# Populate the tables with data
#
for {set tn 1} {$tn<=9} {incr tn} {set nrow($tn) 0}
for {set i 0} {$i<1000} {incr i} {
................................................................................
  foreach column {a b c d e} {
    set v [expr {$base+int(rand()*999)}]
    lappend cdata($column$tn) $v
    set $column $v
  }
  lappend tdata($tn) [list $a $b $c $d $e $x]
  set sql "INSERT INTO t$tn VALUES($a,$b,$c,$d,$e,'$x')"
  db eval $sql
  puts "statement ok\n$sql\n"
}

# Queries to make sure all the data got in correctly.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts "query IIIIIT rowsort all$tn\nSELECT * FROM t$tn\n"
................................................................................

# Create indices
#
# t1 gets prefix indices...
#
for {set i 0} {$i<5} {incr i} {
  puts "statement ok"
  set sql \
    "CREATE INDEX t1i$i ON t1([join [lrange {a1 b1 c1 d1 e1 x1} $i end] ,])"
  puts "$sql\n"
  db eval $sql
}
set fast(t1) {a1} ;# {a1 b1} {a1 b1 c1} {a1 b1 c1 d1} {a1 b1 c1 d3 e1}}
# t2 gets a separate index on each column
#
foreach c {a2 b2 c2 d2 e2} {
  puts "statement ok"
  set sql "CREATE INDEX t2$c ON t2($c)"
  puts "$sql\n"
  db eval $sql
}
set fast(t2) {a2 b2 c2 d2 e2}
# t3 through t7 get a single index on one column
#
for {set i 0} {$i<5} {incr i} {
  set tn [expr {$i+3}]
  set cn [string index {abcde} $i]$tn
  puts "statement ok"
  set sql "CREATE INDEX t$tn$cn ON t${tn}($cn)"
  puts "$sql\n"
  db eval $sql
  set fast(t$tn) $cn
}
# t8 gets a single reverse-order index.
#
puts "statement ok"
set sql "CREATE INDEX t8all ON t8(e8 DESC, d8 ASC, c8 DESC, b8 ASC, a8 DESC)"
db eval $sql
puts "$sql\n"
set fast(t8) {e8} ;# {e8 d8} {e8 d8 c8} {e8 d8 c8 b8} {e8 d8 c8 b8 a8}}
# t9 does not get an index.
#
set fast(t9) {}

# Repeat the data dumps.  The table contents should not have changed.
#
for {set tn 1} {$tn<=9} {incr tn} {
  puts "query IIIIIT rowsort all$tn\nSELECT * FROM t$tn\n"
}

................................................................................
      set w [few_row_where $tn]
      set op "\n      OR "
      append sql "   WHERE [join $w $op]\n"
    }
  }
  puts "query T valuesort\n$sql" 
}

# Return a single WHERE clause term that restricts table $tn to just
# a handful or rows.  Try to use an index.
#
# Feel free to use columns from any tables in $tnset as right-hand side.
#
proc oneortwo_where_term {tn tnset} {
  global cdata fast
  set n [llength $fast(t$tn)]
  if {$n==0} {
    set cn [string index abcde [expr {int(rand()*5)}]]$tn
  } else {
    set cn [lindex [scramble $fast(t$tn)] 0]
  }
  set ntnset [llength $tnset]
  set rhs [lindex $tnset [expr {int(rand()*$ntnset)}]]
  if {$rhs==$tn} {set rhs 0}
  set p [expr {rand()}]
  if {$p<0.3333} {
    set m [expr {int(rand()*6)+2}]
    set term "$cn in ([join [lrange [scramble $::cdata($cn)] 0 $m] ,])"
  } elseif {$p<0.6666 && $rhs>0} {
    set rcn [string index abcde [expr {int(rand()*5)}]]$rhs
    set term "$cn=$rcn"
  } else {
    if {$p<0.9} {
      set m 1
      set conn ""
    } else {
      set m [expr {int(rand()*3)+2}]
      set conn (
    }
    set term {}
    for {set k 0} {$k<$m} {incr k} {
      set nv [llength $cdata($cn)]
      set idx [expr {int(rand()*$nv)}]
      set vx [lindex $cdata($cn) $idx]
      if {rand()<0.5} {
        append term $conn$vx=$cn
      } else {
        append term $conn$cn=$vx
      }
      set conn " OR "
    }
    if {$m>1} {
      append term )
    }
  }
  return $term
}

# Do some joins
#
for {set i 0} {$i<500} {incr i} {
  while {1} {
    set n [expr {int(rand()*7)+1}]
    set tnset [lrange [scramble {1 2 3 4 5 6 7 8 9}] 0 $n]
    set rset {}
    set typestr {}
    set w {}
    set tnnames {}
    foreach tn $tnset {
      lappend tnnames t$tn
      set cn [string index abcdex [expr {int(rand()*6)}]]
      if {$cn=="x"} {
        append typestr T
        append cn $tn
      } else {
        append typestr I
        append cn $tn
        set p [expr {rand()}]
        if {$p<0.2} {
          append cn "*[expr {int(rand()*1000)+1}]"
        } elseif {$p<0.4} {
          append cn "+[expr {int(rand()*1000)+1}]"
        }
        if {rand()<0.3} {
          set tn2 [lindex $tnset [expr {int(rand()*$n)}]]
          set cn2 [string index abcde [expr {int(rand()*5)}]]
          append cn +$cn2$tn2
        }
      }
      lappend rset $cn
      lappend w [oneortwo_where_term $tn $tnset]
    }
    set sql "SELECT count(*) FROM [join $tnnames ,] WHERE [join $w { AND }]"
    append sql " LIMIT [expr {(100000+$n*2)/$n}]"
    set njoin [db eval $sql]
    if {$njoin<1 || $njoin*$n>100000} {
      puts -nonewline stderr .
      continue
    }
    puts stderr "join-$i is [expr {$n+1}]-way has $njoin rows"
  
    unset -nocomplain seen
    for {set j 0} {$j<4} {incr j} {
      set sql "SELECT [join $rset {, }]\n"
      append sql "  FROM [join [scramble $tnnames] {, }]\n"
      append sql " WHERE [join [scramble $w] "\n   AND "]"
      if {[info exists seen($sql)]} continue
      set seen($sql) 1
      puts "query $typestr rowsort join$i"
      puts $sql
      puts ""
    }
    break
  }
}

Changes to test/select4.test.

more than 10,000 changes