wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 September 24 |
2 | # |
||
3 | # The author disclaims copyright to this source code. In place of |
||
4 | # a legal notice, here is a blessing: |
||
5 | # |
||
6 | # May you do good and not evil. |
||
7 | # May you find forgiveness for yourself and forgive others. |
||
8 | # May you share freely, never taking more than you give. |
||
9 | # |
||
10 | #*********************************************************************** |
||
11 | # |
||
12 | # This file implements tests to verify that the "testable statements" in |
||
13 | # the lang_select.html document are correct. |
||
14 | # |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | #------------------------------------------------------------------------- |
||
20 | # te_* commands: |
||
21 | # |
||
22 | # |
||
23 | # te_read_sql DB SELECT-STATEMENT |
||
24 | # te_read_tbl DB TABLENAME |
||
25 | # |
||
26 | # These two commands are used to read a dataset from the database. A dataset |
||
27 | # consists of N rows of M named columns of values each, where each value has a |
||
28 | # type (null, integer, real, text or blob) and a value within the types domain. |
||
29 | # The tcl format for a "dataset" is a list of two elements: |
||
30 | # |
||
31 | # * A list of the column names. |
||
32 | # * A list of data rows. Each row is itself a list, where each element is |
||
33 | # the contents of a column of the row. Each of these is a list of two |
||
34 | # elements, the type name and the actual value. |
||
35 | # |
||
36 | # For example, the contents of table [t1] as a dataset is: |
||
37 | # |
||
38 | # CREATE TABLE t1(a, b); |
||
39 | # INSERT INTO t1 VALUES('abc', NULL); |
||
40 | # INSERT INTO t1 VALUES(43.1, 22); |
||
41 | # |
||
42 | # {a b} {{{TEXT abc} {NULL {}}} {{REAL 43.1} {INTEGER 22}}} |
||
43 | # |
||
44 | # The [te_read_tbl] command returns a dataset read from a table. The |
||
45 | # [te_read_sql] returns the dataset that results from executing a SELECT |
||
46 | # command. |
||
47 | # |
||
48 | # |
||
49 | # te_tbljoin ?SWITCHES? LHS-TABLE RHS-TABLE |
||
50 | # te_join ?SWITCHES? LHS-DATASET RHS-DATASET |
||
51 | # |
||
52 | # This command joins the two datasets and returns the resulting dataset. If |
||
53 | # there are no switches specified, then the results is the cartesian product |
||
54 | # of the two inputs. The [te_tbljoin] command reads the left and right-hand |
||
55 | # datasets from the specified tables. The [te_join] command is passed the |
||
56 | # datasets directly. |
||
57 | # |
||
58 | # Optional switches are as follows: |
||
59 | # |
||
60 | # -on SCRIPT |
||
61 | # -using COLUMN-LIST |
||
62 | # -left |
||
63 | # |
||
64 | # The -on option specifies a tcl script that is executed for each row in the |
||
65 | # cartesian product of the two datasets. The script has 4 arguments appended |
||
66 | # to it, in the following order: |
||
67 | # |
||
68 | # * The list of column-names from the left-hand dataset. |
||
69 | # * A single row from the left-hand dataset (one "data row" list as |
||
70 | # described above. |
||
71 | # * The list of column-names from the right-hand dataset. |
||
72 | # * A single row from the right-hand dataset. |
||
73 | # |
||
74 | # The script must return a boolean value - true if the combination of rows |
||
75 | # should be included in the output dataset, or false otherwise. |
||
76 | # |
||
77 | # The -using option specifies a list of the columns from the right-hand |
||
78 | # dataset that should be omitted from the output dataset. |
||
79 | # |
||
80 | # If the -left option is present, the join is done LEFT JOIN style. |
||
81 | # Specifically, an extra row is inserted if after the -on script is run there |
||
82 | # exist rows in the left-hand dataset that have no corresponding rows in |
||
83 | # the output. See the implementation for more specific comments. |
||
84 | # |
||
85 | # |
||
86 | # te_equals ?SWITCHES? COLNAME1 COLNAME2 <-on script args> |
||
87 | # |
||
88 | # The only supported switch is "-nocase". If it is present, then text values |
||
89 | # are compared in a case-independent fashion. Otherwise, they are compared |
||
90 | # as if using the SQLite BINARY collation sequence. |
||
91 | # |
||
92 | # |
||
93 | # te_and ONSCRIPT1 ONSCRIPT2... |
||
94 | # |
||
95 | # |
||
96 | |||
97 | |||
98 | # |
||
99 | # te_read_tbl DB TABLENAME |
||
100 | # te_read_sql DB SELECT-STATEMENT |
||
101 | # |
||
102 | # These two procs are used to extract datasets from the database, either |
||
103 | # by reading the contents of a named table (te_read_tbl), or by executing |
||
104 | # a SELECT statement (t3_read_sql). |
||
105 | # |
||
106 | # See the comment above, describing "te_* commands", for details of the |
||
107 | # return values. |
||
108 | # |
||
109 | proc te_read_tbl {db tbl} { |
||
110 | te_read_sql $db "SELECT * FROM '$tbl'" |
||
111 | } |
||
112 | proc te_read_sql {db sql} { |
||
113 | set S [sqlite3_prepare_v2 $db $sql -1 DUMMY] |
||
114 | |||
115 | set cols [list] |
||
116 | for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} { |
||
117 | lappend cols [sqlite3_column_name $S $i] |
||
118 | } |
||
119 | |||
120 | set rows [list] |
||
121 | while {[sqlite3_step $S] == "SQLITE_ROW"} { |
||
122 | set r [list] |
||
123 | for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} { |
||
124 | lappend r [list [sqlite3_column_type $S $i] [sqlite3_column_text $S $i]] |
||
125 | } |
||
126 | lappend rows $r |
||
127 | } |
||
128 | sqlite3_finalize $S |
||
129 | |||
130 | return [list $cols $rows] |
||
131 | } |
||
132 | |||
133 | #------- |
||
134 | # Usage: te_join <table-data1> <table-data2> <join spec>... |
||
135 | # |
||
136 | # Where a join-spec is an optional list of arguments as follows: |
||
137 | # |
||
138 | # ?-left? |
||
139 | # ?-using colname-list? |
||
140 | # ?-on on-expr-proc? |
||
141 | # |
||
142 | proc te_join {data1 data2 args} { |
||
143 | |||
144 | set testproc "" |
||
145 | set usinglist [list] |
||
146 | set isleft 0 |
||
147 | for {set i 0} {$i < [llength $args]} {incr i} { |
||
148 | set a [lindex $args $i] |
||
149 | if { $a != "{}" } { |
||
150 | switch -- $a { |
||
151 | -on { set testproc [lindex $args [incr i]] } |
||
152 | -using { set usinglist [lindex $args [incr i]] } |
||
153 | -left { set isleft 1 } |
||
154 | default { |
||
155 | error "Unknown argument: $a" |
||
156 | } |
||
157 | } |
||
158 | } |
||
159 | } |
||
160 | set c1 [lindex $data1 0] |
||
161 | set c2 [lindex $data2 0] |
||
162 | set omitlist [list] |
||
163 | set nullrowlist [list] |
||
164 | set cret $c1 |
||
165 | |||
166 | set cidx 0 |
||
167 | foreach col $c2 { |
||
168 | set idx [lsearch $usinglist $col] |
||
169 | if {$idx>=0} {lappend omitlist $cidx} |
||
170 | if {$idx<0} { |
||
171 | lappend nullrowlist {NULL {}} |
||
172 | lappend cret $col |
||
173 | } |
||
174 | incr cidx |
||
175 | } |
||
176 | set omitlist [lsort -integer -decreasing $omitlist] |
||
177 | |||
178 | |||
179 | set rret [list] |
||
180 | foreach r1 [lindex $data1 1] { |
||
181 | set one 0 |
||
182 | foreach r2 [lindex $data2 1] { |
||
183 | set ok 1 |
||
184 | if {$testproc != ""} { |
||
185 | set ok [eval $testproc [list $c1 $r1 $c2 $r2]] |
||
186 | } |
||
187 | if {$ok} { |
||
188 | set one 1 |
||
189 | foreach idx $omitlist {set r2 [lreplace $r2 $idx $idx]} |
||
190 | lappend rret [concat $r1 $r2] |
||
191 | } |
||
192 | } |
||
193 | |||
194 | if {$isleft && $one==0} { |
||
195 | lappend rret [concat $r1 $nullrowlist] |
||
196 | } |
||
197 | } |
||
198 | |||
199 | list $cret $rret |
||
200 | } |
||
201 | |||
202 | proc te_tbljoin {db t1 t2 args} { |
||
203 | te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args |
||
204 | } |
||
205 | |||
206 | proc te_apply_affinity {affinity typevar valvar} { |
||
207 | upvar $typevar type |
||
208 | upvar $valvar val |
||
209 | |||
210 | switch -- $affinity { |
||
211 | integer { |
||
212 | if {[string is double $val]} { set type REAL } |
||
213 | if {[string is wideinteger $val]} { set type INTEGER } |
||
214 | if {$type == "REAL" && int($val)==$val} { |
||
215 | set type INTEGER |
||
216 | set val [expr {int($val)}] |
||
217 | } |
||
218 | } |
||
219 | text { |
||
220 | set type TEXT |
||
221 | } |
||
222 | none { } |
||
223 | |||
224 | default { error "invalid affinity: $affinity" } |
||
225 | } |
||
226 | } |
||
227 | |||
228 | #---------- |
||
229 | # te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2 |
||
230 | # |
||
231 | proc te_equals {args} { |
||
232 | |||
233 | if {[llength $args]<6} {error "invalid arguments to te_equals"} |
||
234 | foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break |
||
235 | |||
236 | set nocase 0 |
||
237 | set affinity none |
||
238 | |||
239 | for {set i 0} {$i < ([llength $args]-6)} {incr i} { |
||
240 | set a [lindex $args $i] |
||
241 | switch -- $a { |
||
242 | -nocase { |
||
243 | set nocase 1 |
||
244 | } |
||
245 | -affinity { |
||
246 | set affinity [string tolower [lindex $args [incr i]]] |
||
247 | } |
||
248 | default { |
||
249 | error "invalid arguments to te_equals" |
||
250 | } |
||
251 | } |
||
252 | } |
||
253 | |||
254 | set idx2 [if {[string is integer $c2]} { set c2 } else { lsearch $cols2 $c2 }] |
||
255 | set idx1 [if {[string is integer $c1]} { set c1 } else { lsearch $cols1 $c1 }] |
||
256 | |||
257 | set t1 [lindex $row1 $idx1 0] |
||
258 | set t2 [lindex $row2 $idx2 0] |
||
259 | set v1 [lindex $row1 $idx1 1] |
||
260 | set v2 [lindex $row2 $idx2 1] |
||
261 | |||
262 | te_apply_affinity $affinity t1 v1 |
||
263 | te_apply_affinity $affinity t2 v2 |
||
264 | |||
265 | if {$t1 == "NULL" || $t2 == "NULL"} { return 0 } |
||
266 | if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] } |
||
267 | if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] } |
||
268 | |||
269 | |||
270 | set res [expr {$t1 == $t2 && [string equal $v1 $v2]}] |
||
271 | return $res |
||
272 | } |
||
273 | |||
274 | proc te_false {args} { return 0 } |
||
275 | proc te_true {args} { return 1 } |
||
276 | |||
277 | proc te_and {args} { |
||
278 | foreach a [lrange $args 0 end-4] { |
||
279 | set res [eval $a [lrange $args end-3 end]] |
||
280 | if {$res == 0} {return 0} |
||
281 | } |
||
282 | return 1 |
||
283 | } |
||
284 | |||
285 | |||
286 | proc te_dataset_eq {testname got expected} { |
||
287 | uplevel #0 [list do_test $testname [list set {} $got] $expected] |
||
288 | } |
||
289 | proc te_dataset_eq_unordered {testname got expected} { |
||
290 | lset got 1 [lsort [lindex $got 1]] |
||
291 | lset expected 1 [lsort [lindex $expected 1]] |
||
292 | te_dataset_eq $testname $got $expected |
||
293 | } |
||
294 | |||
295 | proc te_dataset_ne {testname got unexpected} { |
||
296 | uplevel #0 [list do_test $testname [list string equal $got $unexpected] 0] |
||
297 | } |
||
298 | proc te_dataset_ne_unordered {testname got unexpected} { |
||
299 | lset got 1 [lsort [lindex $got 1]] |
||
300 | lset unexpected 1 [lsort [lindex $unexpected 1]] |
||
301 | te_dataset_ne $testname $got $unexpected |
||
302 | } |
||
303 | |||
304 | |||
305 | #------------------------------------------------------------------------- |
||
306 | # |
||
307 | proc test_join {tn sqljoin tbljoinargs} { |
||
308 | set sql [te_read_sql db "SELECT * FROM $sqljoin"] |
||
309 | set te [te_tbljoin db {*}$tbljoinargs] |
||
310 | te_dataset_eq_unordered $tn $sql $te |
||
311 | } |
||
312 | |||
313 | drop_all_tables |
||
314 | do_execsql_test e_select-2.0 { |
||
315 | CREATE TABLE t1(a, b); |
||
316 | CREATE TABLE t2(a, b); |
||
317 | CREATE TABLE t3(b COLLATE nocase); |
||
318 | |||
319 | INSERT INTO t1 VALUES(2, 'B'); |
||
320 | INSERT INTO t1 VALUES(1, 'A'); |
||
321 | INSERT INTO t1 VALUES(4, 'D'); |
||
322 | INSERT INTO t1 VALUES(NULL, NULL); |
||
323 | INSERT INTO t1 VALUES(3, NULL); |
||
324 | |||
325 | INSERT INTO t2 VALUES(1, 'A'); |
||
326 | INSERT INTO t2 VALUES(2, NULL); |
||
327 | INSERT INTO t2 VALUES(5, 'E'); |
||
328 | INSERT INTO t2 VALUES(NULL, NULL); |
||
329 | INSERT INTO t2 VALUES(3, 'C'); |
||
330 | |||
331 | INSERT INTO t3 VALUES('a'); |
||
332 | INSERT INTO t3 VALUES('c'); |
||
333 | INSERT INTO t3 VALUES('b'); |
||
334 | } {} |
||
335 | |||
336 | foreach {tn indexes} { |
||
337 | e_select-2.1.1 { } |
||
338 | e_select-2.1.2 { CREATE INDEX i1 ON t1(a) } |
||
339 | e_select-2.1.3 { CREATE INDEX i1 ON t2(a) } |
||
340 | e_select-2.1.4 { CREATE INDEX i1 ON t3(b) } |
||
341 | } { |
||
342 | |||
343 | catchsql { DROP INDEX i1 } |
||
344 | catchsql { DROP INDEX i2 } |
||
345 | catchsql { DROP INDEX i3 } |
||
346 | execsql $indexes |
||
347 | |||
348 | # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER |
||
349 | # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, |
||
350 | # then the result of the join is simply the cartesian product of the |
||
351 | # left and right-hand datasets. |
||
352 | # |
||
353 | # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER |
||
354 | # JOIN", "JOIN" and "," join operators. |
||
355 | # |
||
356 | # EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the |
||
357 | # same data as the "INNER JOIN", "JOIN" and "," operators |
||
358 | # |
||
359 | test_join $tn.1.1 "t1, t2" {t1 t2} |
||
360 | test_join $tn.1.2 "t1 INNER JOIN t2" {t1 t2} |
||
361 | test_join $tn.1.3 "t1 CROSS JOIN t2" {t1 t2} |
||
362 | test_join $tn.1.4 "t1 JOIN t2" {t1 t2} |
||
363 | test_join $tn.1.5 "t2, t3" {t2 t3} |
||
364 | test_join $tn.1.6 "t2 INNER JOIN t3" {t2 t3} |
||
365 | test_join $tn.1.7 "t2 CROSS JOIN t3" {t2 t3} |
||
366 | test_join $tn.1.8 "t2 JOIN t3" {t2 t3} |
||
367 | test_join $tn.1.9 "t2, t2 AS x" {t2 t2} |
||
368 | test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2} |
||
369 | test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2} |
||
370 | test_join $tn.1.12 "t2 JOIN t2 AS x" {t2 t2} |
||
371 | |||
372 | # EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then |
||
373 | # the ON expression is evaluated for each row of the cartesian product |
||
374 | # as a boolean expression. All rows for which the expression evaluates |
||
375 | # to false are excluded from the dataset. |
||
376 | # |
||
377 | test_join $tn.2.1 "t1, t2 ON (t1.a=t2.a)" {t1 t2 -on {te_equals a a}} |
||
378 | test_join $tn.2.2 "t2, t1 ON (t1.a=t2.a)" {t2 t1 -on {te_equals a a}} |
||
379 | test_join $tn.2.3 "t2, t1 ON (1)" {t2 t1 -on te_true} |
||
380 | test_join $tn.2.4 "t2, t1 ON (NULL)" {t2 t1 -on te_false} |
||
381 | test_join $tn.2.5 "t2, t1 ON (1.1-1.1)" {t2 t1 -on te_false} |
||
382 | test_join $tn.2.6 "t1, t2 ON (1.1-1.0)" {t1 t2 -on te_true} |
||
383 | |||
384 | |||
385 | test_join $tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}} |
||
386 | test_join $tn.4 "t1 LEFT JOIN t2 USING (a)" { |
||
387 | t1 t2 -left -using a -on {te_equals a a} |
||
388 | } |
||
389 | test_join $tn.5 "t1 CROSS JOIN t2 USING(b, a)" { |
||
390 | t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
391 | } |
||
392 | test_join $tn.6 "t1 NATURAL JOIN t2" { |
||
393 | t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
394 | } |
||
395 | test_join $tn.7 "t1 NATURAL INNER JOIN t2" { |
||
396 | t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
397 | } |
||
398 | test_join $tn.8 "t1 NATURAL CROSS JOIN t2" { |
||
399 | t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
400 | } |
||
401 | test_join $tn.9 "t1 NATURAL INNER JOIN t2" { |
||
402 | t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
403 | } |
||
404 | test_join $tn.10 "t1 NATURAL LEFT JOIN t2" { |
||
405 | t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
406 | } |
||
407 | test_join $tn.11 "t1 NATURAL LEFT OUTER JOIN t2" { |
||
408 | t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
409 | } |
||
410 | test_join $tn.12 "t2 NATURAL JOIN t1" { |
||
411 | t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
412 | } |
||
413 | test_join $tn.13 "t2 NATURAL INNER JOIN t1" { |
||
414 | t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
415 | } |
||
416 | test_join $tn.14 "t2 NATURAL CROSS JOIN t1" { |
||
417 | t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
418 | } |
||
419 | test_join $tn.15 "t2 NATURAL INNER JOIN t1" { |
||
420 | t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
421 | } |
||
422 | test_join $tn.16 "t2 NATURAL LEFT JOIN t1" { |
||
423 | t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
424 | } |
||
425 | test_join $tn.17 "t2 NATURAL LEFT OUTER JOIN t1" { |
||
426 | t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} |
||
427 | } |
||
428 | test_join $tn.18 "t1 LEFT JOIN t2 USING (b)" { |
||
429 | t1 t2 -left -using b -on {te_equals b b} |
||
430 | } |
||
431 | test_join $tn.19 "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}} |
||
432 | test_join $tn.20 "t3 JOIN t1 USING(b)" { |
||
433 | t3 t1 -using b -on {te_equals -nocase b b} |
||
434 | } |
||
435 | test_join $tn.21 "t1 NATURAL JOIN t3" { |
||
436 | t1 t3 -using b -on {te_equals b b} |
||
437 | } |
||
438 | test_join $tn.22 "t3 NATURAL JOIN t1" { |
||
439 | t3 t1 -using b -on {te_equals -nocase b b} |
||
440 | } |
||
441 | test_join $tn.23 "t1 NATURAL LEFT JOIN t3" { |
||
442 | t1 t3 -left -using b -on {te_equals b b} |
||
443 | } |
||
444 | test_join $tn.24 "t3 NATURAL LEFT JOIN t1" { |
||
445 | t3 t1 -left -using b -on {te_equals -nocase b b} |
||
446 | } |
||
447 | test_join $tn.25 "t1 LEFT JOIN t3 ON (t3.b=t1.b)" { |
||
448 | t1 t3 -left -on {te_equals -nocase b b} |
||
449 | } |
||
450 | test_join $tn.26 "t1 LEFT JOIN t3 ON (t1.b=t3.b)" { |
||
451 | t1 t3 -left -on {te_equals b b} |
||
452 | } |
||
453 | test_join $tn.27 "t1 JOIN t3 ON (t1.b=t3.b)" { t1 t3 -on {te_equals b b} } |
||
454 | |||
455 | # EVIDENCE-OF: R-28760-53843 When more than two tables are joined |
||
456 | # together as part of a FROM clause, the join operations are processed |
||
457 | # in order from left to right. In other words, the FROM clause (A |
||
458 | # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C). |
||
459 | # |
||
460 | # Tests 28a and 28b show that the statement above is true for this case. |
||
461 | # Test 28c shows that if the parenthesis force a different order of |
||
462 | # evaluation the result is different. Test 28d verifies that the result |
||
463 | # of the query with the parenthesis forcing a different order of evaluation |
||
464 | # is as calculated by the [te_*] procs. |
||
465 | # |
||
466 | set t3_natural_left_join_t2 [ |
||
467 | te_tbljoin db t3 t2 -left -using {b} -on {te_equals -nocase b b} |
||
468 | ] |
||
469 | set t1 [te_read_tbl db t1] |
||
470 | te_dataset_eq_unordered $tn.28a [ |
||
471 | te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1" |
||
472 | ] [te_join $t3_natural_left_join_t2 $t1 \ |
||
473 | -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ |
||
474 | ] |
||
475 | |||
476 | te_dataset_eq_unordered $tn.28b [ |
||
477 | te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1" |
||
478 | ] [te_join $t3_natural_left_join_t2 $t1 \ |
||
479 | -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ |
||
480 | ] |
||
481 | |||
482 | te_dataset_ne_unordered $tn.28c [ |
||
483 | te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1" |
||
484 | ] [ |
||
485 | te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" |
||
486 | ] |
||
487 | |||
488 | set t2_natural_join_t1 [te_tbljoin db t2 t1 -using {a b} \ |
||
489 | -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ |
||
490 | ] |
||
491 | set t3 [te_read_tbl db t3] |
||
492 | te_dataset_eq_unordered $tn.28d [ |
||
493 | te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" |
||
494 | ] [te_join $t3 $t2_natural_join_t1 \ |
||
495 | -left -using {b} -on {te_equals -nocase b b} \ |
||
496 | ] |
||
497 | } |
||
498 | |||
499 | do_execsql_test e_select-2.2.0 { |
||
500 | CREATE TABLE t4(x TEXT COLLATE nocase); |
||
501 | CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary); |
||
502 | |||
503 | INSERT INTO t4 VALUES('2.0'); |
||
504 | INSERT INTO t4 VALUES('TWO'); |
||
505 | INSERT INTO t5 VALUES(2, 'two'); |
||
506 | } {} |
||
507 | |||
508 | # EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source |
||
509 | # following the FROM clause in a simple SELECT statement is handled as |
||
510 | # if it was a table containing the data returned by executing the |
||
511 | # sub-select statement. |
||
512 | # |
||
513 | # EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset |
||
514 | # inherits the collation sequence and affinity of the corresponding |
||
515 | # expression in the sub-select statement. |
||
516 | # |
||
517 | foreach {tn subselect select spec} { |
||
518 | 1 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss%" |
||
519 | {t1 %ss%} |
||
520 | |||
521 | 2 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" |
||
522 | {t1 %ss% -on {te_equals 0 0}} |
||
523 | |||
524 | 3 "SELECT * FROM t2" "SELECT * FROM %ss% AS x JOIN t1 ON (t1.a=x.a)" |
||
525 | {%ss% t1 -on {te_equals 0 0}} |
||
526 | |||
527 | 4 "SELECT * FROM t1, t2" "SELECT * FROM %ss% AS x JOIN t3" |
||
528 | {%ss% t3} |
||
529 | |||
530 | 5 "SELECT * FROM t1, t2" "SELECT * FROM %ss% NATURAL JOIN t3" |
||
531 | {%ss% t3 -using b -on {te_equals 1 0}} |
||
532 | |||
533 | 6 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL JOIN %ss%" |
||
534 | {t3 %ss% -using b -on {te_equals -nocase 0 1}} |
||
535 | |||
536 | 7 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL LEFT JOIN %ss%" |
||
537 | {t3 %ss% -left -using b -on {te_equals -nocase 0 1}} |
||
538 | |||
539 | 8 "SELECT count(*) AS y FROM t4" "SELECT * FROM t5, %ss% USING (y)" |
||
540 | {t5 %ss% -using y -on {te_equals -affinity text 0 0}} |
||
541 | |||
542 | 9 "SELECT count(*) AS y FROM t4" "SELECT * FROM %ss%, t5 USING (y)" |
||
543 | {%ss% t5 -using y -on {te_equals -affinity text 0 0}} |
||
544 | |||
545 | 10 "SELECT x AS y FROM t4" "SELECT * FROM %ss% JOIN t5 USING (y)" |
||
546 | {%ss% t5 -using y -on {te_equals -nocase -affinity integer 0 0}} |
||
547 | |||
548 | 11 "SELECT x AS y FROM t4" "SELECT * FROM t5 JOIN %ss% USING (y)" |
||
549 | {t5 %ss% -using y -on {te_equals -nocase -affinity integer 0 0}} |
||
550 | |||
551 | 12 "SELECT y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" |
||
552 | {%ss% t4 -using x -on {te_equals -nocase -affinity integer 0 0}} |
||
553 | |||
554 | 13 "SELECT y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" |
||
555 | {t4 %ss% -using x -on {te_equals -nocase -affinity integer 0 0}} |
||
556 | |||
557 | 14 "SELECT +y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" |
||
558 | {%ss% t4 -using x -on {te_equals -nocase -affinity text 0 0}} |
||
559 | |||
560 | 15 "SELECT +y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" |
||
561 | {t4 %ss% -using x -on {te_equals -nocase -affinity text 0 0}} |
||
562 | } { |
||
563 | |||
564 | # Create a temporary table named %ss% containing the data returned by |
||
565 | # the sub-select. Then have the [te_tbljoin] proc use this table to |
||
566 | # compute the expected results of the $select query. Drop the temporary |
||
567 | # table before continuing. |
||
568 | # |
||
569 | execsql "CREATE TEMP TABLE '%ss%' AS $subselect" |
||
570 | set te [eval te_tbljoin db $spec] |
||
571 | execsql "DROP TABLE '%ss%'" |
||
572 | |||
573 | # Check that the actual data returned by the $select query is the same |
||
574 | # as the expected data calculated using [te_tbljoin] above. |
||
575 | # |
||
576 | te_dataset_eq_unordered e_select-2.2.1.$tn [ |
||
577 | te_read_sql db [string map [list %ss% "($subselect)"] $select] |
||
578 | ] $te |
||
579 | } |
||
580 | |||
581 | finish_test |