Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Adjust the test suite to account for recent changes related to #2822. Most changes are related to English language error messages only. (CVS 4622) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
2f88b9b3e3c9abc3ae4a5dcef82707dd |
User & Date: | danielk1977 2007-12-13 07:58:51.000 |
Context
2007-12-13
| ||
08:15 | Return an error if the user attempts to rename a view. Related to (but not a fix for) #2831. (CVS 4623) (check-in: 19d56d997f user: danielk1977 tags: trunk) | |
07:58 | Adjust the test suite to account for recent changes related to #2822. Most changes are related to English language error messages only. (CVS 4622) (check-in: 2f88b9b3e3 user: danielk1977 tags: trunk) | |
03:45 | ORDER BY in a compound SELECT will first match against the left-most SELECT. If there is no match there, it begins working its way to the right. (CVS 4621) (check-in: 56063ec84b user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.368 2007/12/13 07:58:51 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
2821 2822 2823 2824 2825 2826 2827 | */ sNC.pEList = p->pEList; if( sqlite3ExprResolveNames(&sNC, p->pWhere) || sqlite3ExprResolveNames(&sNC, p->pHaving) ){ return SQLITE_ERROR; } if( p->pPrior==0 ){ | | | 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 | */ sNC.pEList = p->pEList; if( sqlite3ExprResolveNames(&sNC, p->pWhere) || sqlite3ExprResolveNames(&sNC, p->pHaving) ){ return SQLITE_ERROR; } if( p->pPrior==0 ){ if( processOrderGroupBy(pParse, p, p->pOrderBy, 1, &sNC.hasAgg) ){ return SQLITE_ERROR; } } if( processOrderGroupBy(pParse, p, pGroupBy, 0, &sNC.hasAgg) ){ return SQLITE_ERROR; } |
︙ | ︙ |
Changes to test/insert5.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # # The tests in this file ensure that a temporary table is used # when required by an "INSERT INTO ... SELECT ..." statement. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # # The tests in this file ensure that a temporary table is used # when required by an "INSERT INTO ... SELECT ..." statement. # # $Id: insert5.test,v 1.3 2007/12/13 07:58:51 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !subquery { finish_test return |
︙ | ︙ | |||
81 82 83 84 85 86 87 | } {0} do_test insert5-2.8 { uses_temp_table { INSERT INTO b SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10; } } {1} | > > > > | | > > > > > > > | | 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 | } {0} do_test insert5-2.8 { uses_temp_table { INSERT INTO b SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10; } } {1} # UPDATE: Using a column from the outer query (main.id) in the GROUP BY # or ORDER BY of a sub-query is no longer supported. # # do_test insert5-2.9 { # uses_temp_table { # INSERT INTO b # SELECT * FROM main # WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id) # } # } {} do_test insert5-2.9 { catchsql { INSERT INTO b SELECT * FROM main WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id) } } {1 {no such column: main.id}} finish_test |
Changes to test/misc5.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for miscellanous features that were # left out of other test files. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for miscellanous features that were # left out of other test files. # # $Id: misc5.test,v 1.19 2007/12/13 07:58:51 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build records using the MakeRecord opcode such that the size of the # header is at the transition point in the size of a varint. # |
︙ | ︙ | |||
554 555 556 557 558 559 560 | } } {1 {no such table: blah}} do_test misc5-6.2 { execsql { CREATE TABLE logs(msg TEXT, timestamp INTEGER, dbtime TEXT); } catchsql { | | | | 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 | } } {1 {no such table: blah}} do_test misc5-6.2 { execsql { CREATE TABLE logs(msg TEXT, timestamp INTEGER, dbtime TEXT); } catchsql { SELECT * FROM logs WHERE logs.oid >= (SELECT head FROM logs_base) UNION ALL SELECT * FROM logs LIMIT (SELECT lmt FROM logs_base) ; } } {1 {no such table: logs_base}} } # Overflow the lemon parser stack by providing an overly complex # expression. Make sure that the overflow is detected and reported. # do_test misc5-7.1 { execsql {CREATE TABLE t1(x)} |
︙ | ︙ |
Changes to test/null.test.
︙ | ︙ | |||
176 177 178 179 180 181 182 | } {{} 0 1} do_test null-6.4 { execsql { select b from t1 union select c from t1 order by main.t1.b; } } {{} 0 1} do_test null-6.5 { | | | | | | 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 | } {{} 0 1} do_test null-6.4 { execsql { select b from t1 union select c from t1 order by main.t1.b; } } {{} 0 1} do_test null-6.5 { catchsql { select b from t1 union select c from t1 order by t1.a; } } {1 {1st ORDER BY term does not match any column in the result set}} do_test null-6.6 { catchsql { select b from t1 union select c from t1 order by main.t1.a; } } {1 {1st ORDER BY term does not match any column in the result set}} } ;# ifcapable compound # The UNIQUE constraint only applies to non-null values # ifcapable conflict { do_test null-7.1 { execsql { |
︙ | ︙ |
Changes to test/select1.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # # $Id: select1.test,v 1.57 2007/12/13 07:58:51 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. # do_test select1-1.1 { |
︙ | ︙ | |||
302 303 304 305 306 307 308 | do_test select1-4.3 { set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] lappend v $msg } {0 {11 33}} do_test select1-4.4 { set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] lappend v $msg | | | 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 | do_test select1-4.3 { set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg] lappend v $msg } {0 {11 33}} do_test select1-4.4 { set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg] lappend v $msg } {1 {misuse of aggregate: min(f1)}} # The restriction not allowing constants in the ORDER BY clause # has been removed. See ticket #1768 #do_test select1-4.5 { # catchsql { # SELECT f1 FROM test1 ORDER BY 8.4; # } |
︙ | ︙ | |||
370 371 372 373 374 375 376 | SELECT * FROM t5 ORDER BY +2; } } {2 9 1 10} do_test select1-4.10.1 { catchsql { SELECT * FROM t5 ORDER BY 3; } | | | | 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 | SELECT * FROM t5 ORDER BY +2; } } {2 9 1 10} do_test select1-4.10.1 { catchsql { SELECT * FROM t5 ORDER BY 3; } } {1 {1st ORDER BY term out of range - should be between 1 and 2}} do_test select1-4.10.2 { catchsql { SELECT * FROM t5 ORDER BY -1; } } {1 {1st ORDER BY term out of range - should be between 1 and 2}} do_test select1-4.11 { execsql { INSERT INTO t5 VALUES(3,10); SELECT * FROM t5 ORDER BY 2, 1 DESC; } } {2 9 3 10 1 10} do_test select1-4.12 { |
︙ | ︙ | |||
516 517 518 519 520 521 522 | } {0 {f1 11 f1 22 f1 33 f1 44}} do_test select1-6.11 { set v [catch {execsql2 { SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 ORDER BY f2+101; }} msg] lappend v $msg | | | 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 | } {0 {f1 11 f1 22 f1 33 f1 44}} do_test select1-6.11 { set v [catch {execsql2 { SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1 ORDER BY f2+101; }} msg] lappend v $msg } {1 {1st ORDER BY term does not match any column in the result set}} # Ticket #2296 ifcapable subquery { do_test select1-6.20 { execsql { CREATE TABLE t6(a TEXT, b TEXT); INSERT INTO t6 VALUES('a','0'); |
︙ | ︙ |
Changes to test/select3.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing aggregate functions and the # GROUP BY and HAVING clauses of SELECT statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing aggregate functions and the # GROUP BY and HAVING clauses of SELECT statements. # # $Id: select3.test,v 1.22 2007/12/13 07:58:51 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test select3-1.0 { |
︙ | ︙ | |||
89 90 91 92 93 94 95 | # SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log; # } #} {1 {GROUP BY terms must not be non-integer constants}} do_test select3-2.10 { catchsql { SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log; } | | | | 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | # SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log; # } #} {1 {GROUP BY terms must not be non-integer constants}} do_test select3-2.10 { catchsql { SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log; } } {1 {1st GROUP BY term out of range - should be between 1 and 2}} do_test select3-2.11 { catchsql { SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log; } } {1 {1st GROUP BY term out of range - should be between 1 and 2}} do_test select3-2.12 { catchsql { SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log; } } {0 {0 1 1 1 2 2 3 4 4 8 5 15}} # Cannot have an empty GROUP BY |
︙ | ︙ |
Changes to test/select4.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing UNION, INTERSECT and EXCEPT operators # in SELECT statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing UNION, INTERSECT and EXCEPT operators # in SELECT statements. # # $Id: select4.test,v 1.24 2007/12/13 07:58:51 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Most tests in this file depend on compound-select. But there are a couple # right at the end that test DISTINCT, so we cannot omit the entire file. # |
︙ | ︙ | |||
294 295 296 297 298 299 300 | set v [catch {execsql { SELECT DISTINCT log FROM t1 UNION ALL SELECT n FROM t1 WHERE log=3 ORDER BY "xyzzy"; }} msg] lappend v $msg | | | | 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 | set v [catch {execsql { SELECT DISTINCT log FROM t1 UNION ALL SELECT n FROM t1 WHERE log=3 ORDER BY "xyzzy"; }} msg] lappend v $msg } {1 {1st ORDER BY term does not match any column in the result set}} do_test select4-5.2d { set v [catch {execsql { SELECT DISTINCT log FROM t1 INTERSECT SELECT n FROM t1 WHERE log=3 ORDER BY "xyzzy"; }} msg] lappend v $msg } {1 {1st ORDER BY term does not match any column in the result set}} do_test select4-5.2e { set v [catch {execsql { SELECT DISTINCT log FROM t1 UNION ALL SELECT n FROM t1 WHERE log=3 ORDER BY n; }} msg] |
︙ | ︙ | |||
336 337 338 339 340 341 342 | do_test select4-5.2h { catchsql { SELECT DISTINCT log FROM t1 UNION ALL SELECT n FROM t1 WHERE log=3 ORDER BY 2; } | | | 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 | do_test select4-5.2h { catchsql { SELECT DISTINCT log FROM t1 UNION ALL SELECT n FROM t1 WHERE log=3 ORDER BY 2; } } {1 {1st ORDER BY term out of range - should be between 1 and 1}} do_test select4-5.2i { catchsql { SELECT DISTINCT 1, log FROM t1 UNION ALL SELECT 2, n FROM t1 WHERE log=3 ORDER BY 2, 1; } |
︙ | ︙ | |||
571 572 573 574 575 576 577 | SELECT 4 AS a, 5 AS b ) ORDER BY x LIMIT 1; } } {x 0 y 1} } ;# ifcapable subquery do_test select4-9.8 { | | | | 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 | SELECT 4 AS a, 5 AS b ) ORDER BY x LIMIT 1; } } {x 0 y 1} } ;# ifcapable subquery do_test select4-9.8 { execsql { SELECT 0 AS x, 1 AS y UNION SELECT 2 AS y, -3 AS x ORDER BY x LIMIT 1; } } {0 1} do_test select4-9.9.1 { execsql2 { SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a } } {a 1 b 2 a 3 b 4} |
︙ | ︙ |
Changes to test/tkt2822.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # # This file is to test that the issues surrounding expressions in # ORDER BY clauses on compound SELECT statements raised by ticket # #2822 have been dealt with. # | | > > > > > > > > > > > > > > > > > > > > > | | > > | > > | > | | | | < < < < < < | < < < | > > | < < | < < < < | < | < < < < < | | | | > | > | > > < > | | > | > | | > > > > > > > > | | > > > > | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 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 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | # #*********************************************************************** # # This file is to test that the issues surrounding expressions in # ORDER BY clauses on compound SELECT statements raised by ticket # #2822 have been dealt with. # # $Id: tkt2822.test,v 1.3 2007/12/13 07:58:51 danielk1977 Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # The ORDER BY matching algorithm is three steps: # # (1) If the ORDER BY term is an integer constant i, then # sort by the i-th column of the result set. # # (2) If the ORDER BY term is an identifier (not x.y or x.y.z # but simply x) then look for a column alias with the same # name. If found, then sort by that column. # # (3) Evaluate the term as an expression and sort by the # value of the expression. # # For a compound SELECT the rules are modified slightly. # In the third rule, the expression must exactly match one # of the result columns. The sequences of three rules is # attempted first on the left-most SELECT. If that doesn't # work, we move to the right, one by one. This is not standard # SQL, it is an SQLite extension. # # Test plan: # # tkt2822-1.* - Simple identifier as ORDER BY expression. # tkt2822-2.* - More complex ORDER BY expressions. do_test tkt2822-0.1 { execsql { CREATE TABLE t1(a, b, c); CREATE TABLE t2(a, b, c); INSERT INTO t1 VALUES(1, 3, 9); INSERT INTO t1 VALUES(3, 9, 27); INSERT INTO t1 VALUES(5, 15, 45); INSERT INTO t2 VALUES(2, 6, 18); INSERT INTO t2 VALUES(4, 12, 36); INSERT INTO t2 VALUES(6, 18, 54); } } {} # Test the "ORDER BY <integer>" syntax. # do_test tkt2822-1.1 { execsql { SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY 1; } } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} do_test tkt2822-1.2 { execsql { SELECT a, CAST (b AS TEXT), c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY 2; } } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} # Test the "ORDER BY <identifier>" syntax. # do_test tkt2822-2.1 { execsql { SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY a; } } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} do_test tkt2822-2.2 { execsql { SELECT a, CAST (b AS TEXT) AS x, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x; } } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} do_test tkt2822-2.3 { execsql { SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY a; } } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} # Test the "ORDER BY <expression>" syntax. # do_test tkt2822-3.1 { execsql { SELECT a, CAST (b AS TEXT) AS x, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY CAST (b AS TEXT); } } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} do_test tkt2822-3.2 { execsql { SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY t1.a; } } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} # Test that if a match cannot be found in the leftmost SELECT, an # attempt is made to find a match in subsequent SELECT statements. # do_test tkt2822-3.1 { execsql { SELECT a, b, c FROM t1 UNION ALL SELECT a AS x, b, c FROM t2 ORDER BY x; } } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} do_test tkt2822-3.2 { # But the leftmost SELECT takes precedence. execsql { SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY a; } } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} do_test tkt2822-3.3 { execsql { SELECT a, b, c FROM t2 UNION ALL SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 ORDER BY a; } } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} # Test some error conditions (ORDER BY clauses that match no column). # do_test tkt2822-4.1 { catchsql { SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x } } {1 {1st ORDER BY term does not match any column in the result set}} do_test tkt2822-4.2 { catchsql { SELECT a, CAST (b AS TEXT) AS x, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY CAST (b AS INTEGER); } } {1 {1st ORDER BY term does not match any column in the result set}} finish_test |