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: |
e9c99b6893e5dd1aafd3cbe7387d3ddc |
User & Date: | drh 2008-12-02 23:08:46.000 |
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
Changes to proto/select4.proto.
more than 10,000 changes
Changes to proto/select4.tcl.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | #!/usr/bin/tclsh # # Run this script to generate a larger prototype test script for # sqllogictest. # # 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] | > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #!/usr/bin/tclsh # # Run this script to generate a larger prototype test script for # sqllogictest. # # 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] |
︙ | ︙ | |||
26 27 28 29 30 31 32 | } # 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} | | > > > > | | > > | | > > | | > > | > | > > | 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 118 119 120 121 | } # 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} { set tn [expr {int(rand()*9)+1}] set base 0 ;# [expr {$tn*1000}] incr nrow($tn) set x "table tn$tn row $nrow($tn)" 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" } |
︙ | ︙ | |||
194 195 196 197 198 199 200 | set w [few_row_where $tn] set op "\n OR " append sql " WHERE [join $w $op]\n" } } puts "query T valuesort\n$sql" } | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | 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