Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Move the test for an (illegal) scalar sub-query that returns more than one column to earlier in SELECT processing in order to avoid an assert() that can happen later on. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
a55842cfb56b659c88832dce9ce7bafb |
| User & Date: | dan 2010-09-02 19:01:16 |
Context
|
2010-09-03
| ||
| 03:32 | Fix a discrepancy between the documented behavior of SQLITE_DBCONFIG_LOOKASIDE and what it actually does. Also add evidence marks on the DBCONFIG_LOOKASIDE implementation. check-in: f483be44 user: drh tags: trunk | |
|
2010-09-02
| ||
| 19:01 | Move the test for an (illegal) scalar sub-query that returns more than one column to earlier in SELECT processing in order to avoid an assert() that can happen later on. check-in: a55842cf user: dan tags: trunk | |
| 18:13 | Fix an off-by-one error in the scratch memory allocator. check-in: 5a959160 user: drh tags: trunk | |
Changes
Changes to src/select.c.
3621 3621 isAgg = (p->selFlags & SF_Aggregate)!=0; 3622 3622 assert( pEList!=0 ); 3623 3623 3624 3624 /* Begin generating code. 3625 3625 */ 3626 3626 v = sqlite3GetVdbe(pParse); 3627 3627 if( v==0 ) goto select_end; 3628 + 3629 + /* If writing to memory or generating a set 3630 + ** only a single column may be output. 3631 + */ 3632 +#ifndef SQLITE_OMIT_SUBQUERY 3633 + if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){ 3634 + goto select_end; 3635 + } 3636 +#endif 3628 3637 3629 3638 /* Generate code for all sub-queries in the FROM clause 3630 3639 */ 3631 3640 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) 3632 3641 for(i=0; !p->pPrior && i<pTabList->nSrc; i++){ 3633 3642 struct SrcList_item *pItem = &pTabList->a[i]; 3634 3643 SelectDest dest; ................................................................................ 3693 3702 if( mxSelect && cnt>mxSelect ){ 3694 3703 sqlite3ErrorMsg(pParse, "too many terms in compound SELECT"); 3695 3704 return 1; 3696 3705 } 3697 3706 } 3698 3707 return multiSelect(pParse, p, pDest); 3699 3708 } 3700 -#endif 3701 - 3702 - /* If writing to memory or generating a set 3703 - ** only a single column may be output. 3704 - */ 3705 -#ifndef SQLITE_OMIT_SUBQUERY 3706 - if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){ 3707 - goto select_end; 3708 - } 3709 3709 #endif 3710 3710 3711 3711 /* If possible, rewrite the query to use GROUP BY instead of DISTINCT. 3712 3712 ** GROUP BY might use an index, DISTINCT never does. 3713 3713 */ 3714 3714 assert( p->pGroupBy==0 || (p->selFlags & SF_Aggregate)!=0 ); 3715 3715 if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ){
Changes to test/e_expr.test.
1725 1725 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) } 1726 1726 } { 1727 1727 set res [db one "SELECT $e1"] 1728 1728 do_expr_test e_expr-34.5.${tn}a $e1 integer $res 1729 1729 do_expr_test e_expr-34.5.${tn}b $e2 integer $res 1730 1730 } 1731 1731 1732 - 1733 - 1734 1732 #------------------------------------------------------------------------- 1735 -# Test statements related to the IN and NOT IN operators. 1733 +# Test statements related to scalar sub-queries. 1734 +# 1735 + 1736 +catch { db close } 1737 +file delete -force test.db 1738 +sqlite3 db test.db 1739 +do_test e_expr-35.0 { 1740 + execsql { 1741 + CREATE TABLE t2(a, b); 1742 + INSERT INTO t2 VALUES('one', 'two'); 1743 + INSERT INTO t2 VALUES('three', NULL); 1744 + INSERT INTO t2 VALUES(4, 5.0); 1745 + } 1746 +} {} 1747 + 1748 +# EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses 1749 +# may appear as a scalar quantity. 1750 +# 1751 +# EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including 1752 +# aggregate and compound SELECT queries (queries with keywords like 1753 +# UNION or EXCEPT) are allowed as scalar subqueries. 1754 +# 1755 +do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35 1756 +do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {} 1757 + 1758 +do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3 1759 +do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4 1760 + 1761 +do_expr_test e_expr-35.1.5 { 1762 + (SELECT b FROM t2 UNION SELECT a+1 FROM t2) 1763 +} null {} 1764 +do_expr_test e_expr-35.1.6 { 1765 + (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1) 1766 +} integer 4 1767 + 1768 +# EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must 1769 +# return a result set with a single column. 1736 1770 # 1771 +set M {only a single result allowed for a SELECT that is part of an expression} 1772 +foreach {tn sql} { 1773 + 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) } 1774 + 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) } 1775 + 3 { SELECT (SELECT 1, 2) } 1776 + 4 { SELECT (SELECT NULL, NULL, NULL) } 1777 + 5 { SELECT (SELECT * FROM t2) } 1778 + 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) } 1779 +} { 1780 + do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M] 1781 +} 1737 1782 1738 1783 finish_test 1739 1784
Changes to test/in.test.
400 400 SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2 401 401 ); 402 402 } 403 403 } {1 {only a single result allowed for a SELECT that is part of an expression}} 404 404 do_test in-12.6 { 405 405 catchsql { 406 406 SELECT * FROM t2 WHERE a IN ( 407 - SELECT a FROM t3 UNION ALL SELECT a, b FROM t2 407 + SELECT a, b FROM t3 UNION ALL SELECT a FROM t2 408 408 ); 409 409 } 410 410 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} 411 411 do_test in-12.7 { 412 412 catchsql { 413 413 SELECT * FROM t2 WHERE a IN ( 414 - SELECT a FROM t3 UNION SELECT a, b FROM t2 414 + SELECT a, b FROM t3 UNION SELECT a FROM t2 415 415 ); 416 416 } 417 417 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} 418 418 do_test in-12.8 { 419 419 catchsql { 420 420 SELECT * FROM t2 WHERE a IN ( 421 - SELECT a FROM t3 EXCEPT SELECT a, b FROM t2 421 + SELECT a, b FROM t3 EXCEPT SELECT a FROM t2 422 422 ); 423 423 } 424 424 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}} 425 425 do_test in-12.9 { 426 426 catchsql { 427 427 SELECT * FROM t2 WHERE a IN ( 428 - SELECT a FROM t3 INTERSECT SELECT a, b FROM t2 428 + SELECT a, b FROM t3 INTERSECT SELECT a FROM t2 429 429 ); 430 430 } 431 431 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}} 432 432 } 433 433 434 +do_test in-12.10 { 435 + catchsql { 436 + SELECT * FROM t2 WHERE a IN ( 437 + SELECT a FROM t3 UNION ALL SELECT a, b FROM t2 438 + ); 439 + } 440 +} {1 {only a single result allowed for a SELECT that is part of an expression}} 441 +do_test in-12.11 { 442 + catchsql { 443 + SELECT * FROM t2 WHERE a IN ( 444 + SELECT a FROM t3 UNION SELECT a, b FROM t2 445 + ); 446 + } 447 +} {1 {only a single result allowed for a SELECT that is part of an expression}} 448 +do_test in-12.12 { 449 + catchsql { 450 + SELECT * FROM t2 WHERE a IN ( 451 + SELECT a FROM t3 EXCEPT SELECT a, b FROM t2 452 + ); 453 + } 454 +} {1 {only a single result allowed for a SELECT that is part of an expression}} 455 +do_test in-12.13 { 456 + catchsql { 457 + SELECT * FROM t2 WHERE a IN ( 458 + SELECT a FROM t3 INTERSECT SELECT a, b FROM t2 459 + ); 460 + } 461 +} {1 {only a single result allowed for a SELECT that is part of an expression}} 462 + 434 463 435 464 #------------------------------------------------------------------------ 436 465 # The following tests check that NULL is handled correctly when it 437 466 # appears as part of a set of values on the right-hand side of an 438 467 # IN or NOT IN operator. 439 468 # 440 469 # When it appears in such a set, NULL is handled as an "unknown value".