diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/e_select.test | 16 | ||||
| -rw-r--r-- | test/orderby1.test | 277 | ||||
| -rw-r--r-- | test/tester.tcl | 4 | ||||
| -rw-r--r-- | test/tkt-cbd054fa6b.test | 4 | ||||
| -rw-r--r-- | test/where.test | 6 |
5 files changed, 289 insertions, 18 deletions
diff --git a/test/e_select.test b/test/e_select.test index 9f26f6fcd..fb63d051d 100644 --- a/test/e_select.test +++ b/test/e_select.test @@ -1023,7 +1023,7 @@ do_execsql_test e_select-4.9.0 { # do_select_tests e_select-4.9 { 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { - 4,5 f 1 o 7,6 s 3,2 t + /#,# f 1 o #,# s #,# t/ } 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { 1,2,3,4 10 5,6,7 18 @@ -1040,7 +1040,7 @@ do_select_tests e_select-4.9 { # values are considered equal. # do_select_tests e_select-4.10 { - 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1 3 2,4} + 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/} 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} } @@ -1745,12 +1745,12 @@ do_select_tests e_select-8.4 { 1 2 7 1 2 8 1 4 93 1 5 -1 } 8 "SELECT z, x FROM d1 ORDER BY 2" { - 3 1 8 1 7 1 -20 1 - 93 1 -1 1 -1 2 93 2 + /# 1 # 1 # 1 # 1 + # 1 # 1 # 2 # 2/ } 9 "SELECT z, x FROM d1 ORDER BY 1" { - -20 1 -1 2 -1 1 3 1 - 7 1 8 1 93 2 93 1 + /-20 1 -1 # -1 # 3 1 + 7 1 8 1 93 # 93 #/ } } @@ -1766,10 +1766,10 @@ do_select_tests e_select-8.5 { 94 94 9 8 4 0 0 -19 } 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { - 3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2 + /# 1 # 1 # 1 # 1 # 1 # 1 # 2 # 2/ } 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { - -20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1 + /-20 1 -1 # -1 # 3 1 7 1 8 1 93 # 93 #/ } } diff --git a/test/orderby1.test b/test/orderby1.test index 2837dfcab..400659b47 100644 --- a/test/orderby1.test +++ b/test/orderby1.test @@ -114,7 +114,7 @@ do_test 1.4c { EXPLAIN QUERY PLAN SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn } -} {~/ORDER BY/} ;# ORDER BY optimized-out +} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC do_test 1.5a { @@ -130,9 +130,9 @@ do_test 1.5b { do_test 1.5c { db eval { EXPLAIN QUERY PLAN - SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC } -} {~/ORDER BY/} ;# ORDER BY optimized-out +} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC do_test 1.6a { db eval { @@ -152,4 +152,275 @@ do_test 1.6c { } {~/ORDER BY/} ;# ORDER BY optimized-out +# Reconstruct the test data to use indices rather than integer primary keys. +# +do_test 2.0 { + db eval { + BEGIN; + DROP TABLE album; + DROP TABLE track; + CREATE TABLE album( + aid INT PRIMARY KEY, + title TEXT NOT NULL + ); + CREATE INDEX album_i1 ON album(title, aid); + CREATE TABLE track( + aid INTEGER NOT NULL REFERENCES album, + tn INTEGER NOT NULL, + name TEXT, + UNIQUE(aid, tn) + ); + INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three'); + INSERT INTO track VALUES + (1, 1, 'one-a'), + (2, 2, 'two-b'), + (3, 3, 'three-c'), + (1, 3, 'one-c'), + (2, 1, 'two-a'), + (3, 1, 'three-a'); + COMMIT; + } +} {} +do_test 2.1a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} + +# Verify that the ORDER BY clause is optimized out +# +do_test 2.1b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {~/ORDER BY/} ;# ORDER BY optimized out + +# The same query with ORDER BY clause optimization disabled via + operators +# should give exactly the same answer. +# +do_test 2.2a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {one-a one-c two-a two-b three-a three-c} + +# The output is sorted manually in this case. +# +do_test 2.2b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms + +# The same query with ORDER BY optimizations turned off via built-in test. +# +do_test 2.3a { + optimization_control db order-by-idx-join 0 + db cache flush + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} +do_test 2.3b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {/ORDER BY/} ;# separate sorting pass due to disabled optimization +optimization_control db all 1 +db cache flush + +# Reverse order sorts +# +do_test 2.4a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {three-a three-c two-a two-b one-a one-c} +do_test 2.4b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn + } +} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting +do_test 2.4c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC + + +do_test 2.5a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {one-c one-a two-b two-a three-c three-a} +do_test 2.5b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC + } +} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting +do_test 2.5c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to mixed ASC/DESC + +do_test 2.6a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {three-c three-a two-b two-a one-c one-a} +do_test 2.6b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC + } +} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting +do_test 2.6c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {~/ORDER BY/} ;# ORDER BY optimized-out + + +# Generate another test dataset, but this time using mixed ASC/DESC indices. +# +do_test 3.0 { + db eval { + BEGIN; + DROP TABLE album; + DROP TABLE track; + CREATE TABLE album( + aid INTEGER PRIMARY KEY, + title TEXT UNIQUE NOT NULL + ); + CREATE TABLE track( + tid INTEGER PRIMARY KEY, + aid INTEGER NOT NULL REFERENCES album, + tn INTEGER NOT NULL, + name TEXT, + UNIQUE(aid ASC, tn DESC) + ); + INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three'); + INSERT INTO track VALUES + (NULL, 1, 1, 'one-a'), + (NULL, 2, 2, 'two-b'), + (NULL, 3, 3, 'three-c'), + (NULL, 1, 3, 'one-c'), + (NULL, 2, 1, 'two-a'), + (NULL, 3, 1, 'three-a'); + COMMIT; + } +} {} +do_test 3.1a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {one-c one-a two-b two-a three-c three-a} + +# Verify that the ORDER BY clause is optimized out +# +do_test 3.1b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {~/ORDER BY/} ;# ORDER BY optimized out + +# The same query with ORDER BY clause optimization disabled via + operators +# should give exactly the same answer. +# +do_test 3.2a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC + } +} {one-c one-a two-b two-a three-c three-a} + +# The output is sorted manually in this case. +# +do_test 3.2b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms + +# The same query with ORDER BY optimizations turned off via built-in test. +# +do_test 3.3a { + optimization_control db order-by-idx-join 0 + db cache flush + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {one-c one-a two-b two-a three-c three-a} +do_test 3.3b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to disabled optimization +optimization_control db all 1 +db cache flush + +# Without the mixed ASC/DESC on ORDER BY +# +do_test 3.4a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} +do_test 3.4b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {one-a one-c two-a two-b three-a three-c} ;# verify same order after sorting +do_test 3.4c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {/ORDER BY/} ;# separate sorting pass due to mismatched DESC/ASC + + +do_test 3.5a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {three-c three-a two-b two-a one-c one-a} +do_test 3.5b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC + } +} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting +do_test 3.5c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to mismatched ASC/DESC + + +do_test 3.6a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {three-a three-c two-a two-b one-a one-c} +do_test 3.6b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn + } +} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting +do_test 3.6c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {~/ORDER BY/} ;# inverted ASC/DESC is optimized out + + finish_test diff --git a/test/tester.tcl b/test/tester.tcl index 74f1e2a1b..d3fa607d3 100644 --- a/test/tester.tcl +++ b/test/tester.tcl @@ -538,10 +538,10 @@ proc do_test {name cmd expected} { } else { if {[regexp {^~?/.*/$} $expected]} { if {[string index $expected 0]=="~"} { - set re [string range $expected 2 end-1] + set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]] set ok [expr {![regexp $re $result]}] } else { - set re [string range $expected 1 end-1] + set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]] set ok [regexp $re $result] } } else { diff --git a/test/tkt-cbd054fa6b.test b/test/tkt-cbd054fa6b.test index 180acf56d..51e01991d 100644 --- a/test/tkt-cbd054fa6b.test +++ b/test/tkt-cbd054fa6b.test @@ -50,7 +50,7 @@ do_test tkt-cbd05-1.3 { WHERE idx = 't1_x' GROUP BY tbl,idx } -} {t1 t1_x { A B C D E F G H I}} +} {/t1 t1_x .[ ABCDEFGHI]{10}./} do_test tkt-cbd05-2.1 { db eval { @@ -82,6 +82,6 @@ do_test tkt-cbd05-2.3 { WHERE idx = 't1_x' GROUP BY tbl,idx } -} {t1 t1_x { A B C D E F G H I}} +} {/t1 t1_x .[ ABCDEFGHI]{10}./} finish_test diff --git a/test/where.test b/test/where.test index 1dab38ce5..e57722c43 100644 --- a/test/where.test +++ b/test/where.test @@ -383,7 +383,7 @@ ifcapable subquery { count { SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; } - } {1 0 4 2 1 9 3 1 16 14} + } {1 0 4 2 1 9 3 1 16 13} do_test where-5.4 { count { SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; @@ -1109,13 +1109,13 @@ do_test where-14.5 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b } -} {4/4 4/1 1/4 1/1 sort} +} {/4/[14] 4/[14] 1/[14] 1/[14] sort/} do_test where-14.6 { # This test case changed from "nosort" to "sort". See ticket 2a5629202f. cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC } -} {4/4 4/1 1/4 1/1 sort} +} {/4/[14] 4/[14] 1/[14] 1/[14] sort/} do_test where-14.7 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b |