Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | SQLite now always chooses the column names for compound selects using the left-most select. This makes SQLite work like other SQL database, but it also is a change from historical behavior and may break some scripts. Ticket #1721. (CVS 3153) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
80cda9f7ce83f2de6cd2fdaf6150bbc3 |
User & Date: | drh 2006-03-26 01:21:23.000 |
Context
2006-03-26
| ||
20:49 | Work around a bug in GCC 4.0.2. (CVS 3154) (check-in: fe0920c792 user: drh tags: trunk) | |
01:21 | SQLite now always chooses the column names for compound selects using the left-most select. This makes SQLite work like other SQL database, but it also is a change from historical behavior and may break some scripts. Ticket #1721. (CVS 3153) (check-in: 80cda9f7ce user: drh tags: trunk) | |
2006-03-25
| ||
15:52 | Add code to report an error on a recursive trigger, but leave it commented out - turning it on will likely break a lot of code. (CVS 3152) (check-in: c83ec81d53 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.310 2006/03/26 01:21:23 drh Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 | */ Table *sqlite3ResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){ Table *pTab; int i, j; ExprList *pEList; Column *aCol, *pCol; if( prepSelectStmt(pParse, pSelect) ){ return 0; } if( sqlite3SelectResolve(pParse, pSelect, 0) ){ return 0; } pTab = sqliteMalloc( sizeof(Table) ); | > | 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 | */ Table *sqlite3ResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){ Table *pTab; int i, j; ExprList *pEList; Column *aCol, *pCol; while( pSelect->pPrior ) pSelect = pSelect->pPrior; if( prepSelectStmt(pParse, pSelect) ){ return 0; } if( sqlite3SelectResolve(pParse, pSelect, 0) ){ return 0; } pTab = sqliteMalloc( sizeof(Table) ); |
︙ | ︙ | |||
1761 1762 1763 1764 1765 1766 1767 | /* Convert the data in the temporary table into whatever form ** it is that we currently need. */ if( eDest!=priorOp || unionTab!=iParm ){ int iCont, iBreak, iStart; assert( p->pEList ); if( eDest==SRT_Callback ){ | > > | | 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 | /* Convert the data in the temporary table into whatever form ** it is that we currently need. */ if( eDest!=priorOp || unionTab!=iParm ){ int iCont, iBreak, iStart; assert( p->pEList ); if( eDest==SRT_Callback ){ Select *pFirst = p; while( pFirst->pPrior ) pFirst = pFirst->pPrior; generateColumnNames(pParse, 0, pFirst->pEList); } iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); sqlite3VdbeAddOp(v, OP_Rewind, unionTab, iBreak); iStart = sqlite3VdbeCurrentAddr(v); rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr, |
︙ | ︙ | |||
1837 1838 1839 1840 1841 1842 1843 | } /* Generate code to take the intersection of the two temporary ** tables. */ assert( p->pEList ); if( eDest==SRT_Callback ){ | > > | | 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 | } /* Generate code to take the intersection of the two temporary ** tables. */ assert( p->pEList ); if( eDest==SRT_Callback ){ Select *pFirst = p; while( pFirst->pPrior ) pFirst = pFirst->pPrior; generateColumnNames(pParse, 0, pFirst->pEList); } iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); sqlite3VdbeAddOp(v, OP_Rewind, tab1, iBreak); iStart = sqlite3VdbeAddOp(v, OP_RowKey, tab1, 0); sqlite3VdbeAddOp(v, OP_NotFound, tab2, iCont); |
︙ | ︙ |
Changes to test/func.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 built-in functions. # | | | 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 built-in functions. # # $Id: func.test,v 1.51 2006/03/26 01:21:23 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # do_test func-0.0 { |
︙ | ︙ | |||
407 408 409 410 411 412 413 414 415 416 417 418 419 420 | DROP TABLE t4; } } {} # Test that the auxdata API for scalar functions works. This test uses # a special user-defined function only available in test builds, # test_auxdata(). Function test_auxdata() takes any number of arguments. do_test func-13.1 { execsql { SELECT test_auxdata('hello world'); } } {0} do_test func-13.2 { | > | 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 | DROP TABLE t4; } } {} # Test that the auxdata API for scalar functions works. This test uses # a special user-defined function only available in test builds, # test_auxdata(). Function test_auxdata() takes any number of arguments. btree_breakpoint do_test func-13.1 { execsql { SELECT test_auxdata('hello world'); } } {0} do_test func-13.2 { |
︙ | ︙ |
Changes to test/minmax.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # # $Id: minmax.test,v 1.19 2006/03/26 01:21:23 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax-1.0 { execsql { BEGIN; |
︙ | ︙ | |||
140 141 142 143 144 145 146 | SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y') } } {1 20} do_test minmax-4.2 { execsql { SELECT y, coalesce(sum(x),0) FROM | | | | 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 | SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y') } } {1 20} do_test minmax-4.2 { execsql { SELECT y, coalesce(sum(x),0) FROM (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1) GROUP BY y ORDER BY y; } } {1 1 2 5 3 22 4 92 5 90 6 0} do_test minmax-4.3 { execsql { SELECT y, count(x), count(*) FROM (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1) GROUP BY y ORDER BY y; } } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1} } ;# ifcapable compound # Make sure the min(x) and max(x) optimizations work on empty tables # including empty tables with indices. Ticket #296. |
︙ | ︙ |
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.50 2006/03/26 01:21:23 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. # do_test select1-1.1 { |
︙ | ︙ | |||
469 470 471 472 473 474 475 | ifcapable compound { do_test select1-6.10 { set v [catch {execsql2 { SELECT f1 FROM test1 UNION SELECT f2 FROM test1 ORDER BY f2; }} msg] lappend v $msg | | | | 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 | ifcapable compound { do_test select1-6.10 { set v [catch {execsql2 { SELECT f1 FROM test1 UNION SELECT f2 FROM test1 ORDER BY f2; }} msg] lappend v $msg } {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+100; }} msg] lappend v $msg } {0 {f1 11 f1 33 f1 122 f1 144}} } ;#ifcapable compound do_test select1-7.1 { set v [catch {execsql { SELECT f1 FROM test1 WHERE f2=; }} msg] lappend v $msg |
︙ | ︙ | |||
783 784 785 786 787 788 789 | } {} } ifcapable {compound && subquery} { do_test select1-12.9 { execsql2 { SELECT x FROM ( | | | | 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 | } {} } ifcapable {compound && subquery} { do_test select1-12.9 { execsql2 { SELECT x FROM ( SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b ) ORDER BY x; } } {x 1 x 3} do_test select1-12.10 { execsql2 { SELECT z.x FROM ( SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b ) AS 'z' ORDER BY x; } } {x 1 x 3} } ;# ifcapable compound # Check for a VDBE stack growth problem that existed at one point. |
︙ | ︙ |
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.19 2006/03/26 01:21:23 drh 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. # |
︙ | ︙ | |||
506 507 508 509 510 511 512 | } {1.1 1.2 1.3} do_test select4-8.2 { execsql { SELECT DISTINCT c FROM t3 ORDER BY c; } } {1.1 1.10 1.2 1.3} | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 | } {1.1 1.2 1.3} do_test select4-8.2 { execsql { SELECT DISTINCT c FROM t3 ORDER BY c; } } {1.1 1.10 1.2 1.3} # Make sure the names of columns are takenf rom the right-most subquery # right in a compound query. Ticket #1721 # do_test select4-9.1 { execsql2 { SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1 } } {x 0 y 1} do_test select4-9.2 { execsql2 { SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1 } } {x 0 y 1} do_test select4-9.3 { execsql2 { SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1 } } {x 0 y 1} do_test select4-9.4 { execsql2 { SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b; } } {x 0 y 1} do_test select4-9.5 { execsql2 { SELECT 0 AS x, 1 AS y UNION SELECT 2 AS p, 3 AS q UNION SELECT 4 AS a, 5 AS b ORDER BY x LIMIT 1 } } {x 0 y 1} do_test select4-9.6 { execsql2 { SELECT * FROM ( SELECT 0 AS x, 1 AS y UNION SELECT 2 AS p, 3 AS q UNION SELECT 4 AS a, 5 AS b ) ORDER BY 1 LIMIT 1; } } {x 0 y 1} do_test select4-9.7 { execsql2 { SELECT * FROM ( SELECT 0 AS x, 1 AS y UNION SELECT 2 AS p, 3 AS q UNION SELECT 4 AS a, 5 AS b ) ORDER BY x LIMIT 1; } } {x 0 y 1} do_test select4-9.8 { execsql2 { SELECT 0 AS x, 1 AS y UNION SELECT 2 AS y, -3 AS x ORDER BY x LIMIT 1; } } {x 0 y 1} do_test select4-9.9 { 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} do_test select4-9.9 { execsql2 { SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a) WHERE b=3 } } {} do_test select4-9.10 { execsql2 { SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a) WHERE b=2 } } {a 1 b 2} do_test select4-9.11 { execsql2 { SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b) WHERE b=2 } } {a 1 b 2} do_test select4-9.12 { execsql2 { SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b) WHERE b>0 } } {a 1 b 2 a 3 b 4} finish_test |
Changes to test/view.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2002 February 26 # # 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 VIEW statements. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2002 February 26 # # 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 VIEW statements. # # $Id: view.test,v 1.31 2006/03/26 01:21:23 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Omit this entire file if the library is not configured with views enabled. ifcapable !view { finish_test return |
︙ | ︙ | |||
149 150 151 152 153 154 155 | ifcapable compound { do_test view-3.4 { execsql2 { CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b; SELECT * FROM v3 LIMIT 4; } | | | | | 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | ifcapable compound { do_test view-3.4 { execsql2 { CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b; SELECT * FROM v3 LIMIT 4; } } {a 2 a 3 a 5 a 6} do_test view-3.5 { execsql2 { CREATE VIEW v4 AS SELECT a, b FROM t1 UNION SELECT b AS 'x', a AS 'y' FROM t1 ORDER BY x, y; SELECT b FROM v4 ORDER BY b LIMIT 4; } } {b 2 b 3 b 5 b 6} } ;# ifcapable compound do_test view-4.1 { catchsql { DROP VIEW t1; } |
︙ | ︙ |