Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Restrain the flattener in the presence of outer joins. Ticket #306. (CVS 958) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6d4b6597e560578253960d9876dc8c86 |
User & Date: | drh 2003-05-06 20:35:16.000 |
Context
2003-05-07
| ||
03:59 | Cleaned up keywords section. (CVS 959) (check-in: bb609f96e0 user: jplyon tags: trunk) | |
2003-05-06
| ||
20:35 | Restrain the flattener in the presence of outer joins. Ticket #306. (CVS 958) (check-in: 6d4b6597e5 user: drh tags: trunk) | |
2003-05-04
| ||
20:42 | Added tests trying (unsuccessfully) to reproduce ticket #304. (CVS 957) (check-in: fda637f453 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.138 2003/05/06 20:35:16 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
1551 1552 1553 1554 1555 1556 1557 | ** ** Flattening is only attempted if all of the following are true: ** ** (1) The subquery and the outer query do not both use aggregates. ** ** (2) The subquery is not an aggregate or the outer query is not a join. ** | | > | 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 | ** ** Flattening is only attempted if all of the following are true: ** ** (1) The subquery and the outer query do not both use aggregates. ** ** (2) The subquery is not an aggregate or the outer query is not a join. ** ** (3) The subquery is not the right operand of a left outer join, or ** the subquery is not itself a join. (Ticket #306) ** ** (4) The subquery is not DISTINCT or the outer query is not a join. ** ** (5) The subquery is not DISTINCT or the outer query does not use ** aggregates. ** ** (6) The subquery does not use aggregates or the outer query is not |
︙ | ︙ | |||
1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 | assert( pSubSrc ); if( pSubSrc->nSrc==0 ) return 0; if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){ return 0; } if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0; if( p->pOrderBy && pSub->pOrderBy ) return 0; /* If we reach this point, it means flattening is permitted for the ** iFrom-th entry of the FROM clause in the outer query. */ /* Move all of the FROM elements of the subquery into the ** the FROM clause of the outer query. Before doing this, remember ** the cursor number for the original outer query FROM element in ** iParent. The iParent cursor will never be used. Subsequent code ** will scan expressions looking for iParent references and replace ** those references with expressions that resolve to the subquery FROM ** elements we are now copying in. */ iParent = pSrc->a[iFrom].iCursor; { int nSubSrc = pSubSrc->nSrc; if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){ sqliteDeleteTable(0, pSrc->a[iFrom].pTab); } sqliteFree(pSrc->a[iFrom].zName); sqliteFree(pSrc->a[iFrom].zAlias); if( nSubSrc>1 ){ int extra = nSubSrc - 1; for(i=1; i<nSubSrc; i++){ pSrc = sqliteSrcListAppend(pSrc, 0, 0); } p->pSrc = pSrc; for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){ pSrc->a[i] = pSrc->a[i-extra]; } } for(i=0; i<nSubSrc; i++){ pSrc->a[i+iFrom] = pSubSrc->a[i]; memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i])); } } /* Now begin substituting subquery result set expressions for ** references to the iParent in the outer query. ** ** Example: ** | > > > > > > > > > > > > > > > > > > | 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 | assert( pSubSrc ); if( pSubSrc->nSrc==0 ) return 0; if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){ return 0; } if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0; if( p->pOrderBy && pSub->pOrderBy ) return 0; /* Restriction 3: If the subquery is a join, make sure the subquery is ** not used as the right operand of an outer join. Examples of why this ** is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) ** ** If we flatten the above, we would get ** ** (t1 LEFT OUTER JOIN t2) JOIN t3 ** ** which is not at all the same thing. */ if( pSubSrc->nSrc>1 && iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 ){ return 0; } /* If we reach this point, it means flattening is permitted for the ** iFrom-th entry of the FROM clause in the outer query. */ /* Move all of the FROM elements of the subquery into the ** the FROM clause of the outer query. Before doing this, remember ** the cursor number for the original outer query FROM element in ** iParent. The iParent cursor will never be used. Subsequent code ** will scan expressions looking for iParent references and replace ** those references with expressions that resolve to the subquery FROM ** elements we are now copying in. */ iParent = pSrc->a[iFrom].iCursor; { int nSubSrc = pSubSrc->nSrc; int jointype = pSrc->a[iFrom].jointype; if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){ sqliteDeleteTable(0, pSrc->a[iFrom].pTab); } sqliteFree(pSrc->a[iFrom].zName); sqliteFree(pSrc->a[iFrom].zAlias); if( nSubSrc>1 ){ int extra = nSubSrc - 1; for(i=1; i<nSubSrc; i++){ pSrc = sqliteSrcListAppend(pSrc, 0, 0); } p->pSrc = pSrc; for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){ pSrc->a[i] = pSrc->a[i-extra]; } } for(i=0; i<nSubSrc; i++){ pSrc->a[i+iFrom] = pSubSrc->a[i]; memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i])); } pSrc->a[iFrom+nSubSrc-1].jointype = jointype; } /* Now begin substituting subquery result set expressions for ** references to the iParent in the outer query. ** ** Example: ** |
︙ | ︙ |
Changes to test/join.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. # # This file implements tests for joins, including outer joins. # | | | 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. # # This file implements tests for joins, including outer joins. # # $Id: join.test,v 1.9 2003/05/06 20:35:17 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test join-1.1 { execsql { CREATE TABLE t1(a,b,c); |
︙ | ︙ | |||
372 373 374 375 376 377 378 379 380 | INSERT INTO t8 VALUES (4, NULL); INSERT INTO t8 VALUES (130, "pa31"); INSERT INTO t8 VALUES (131, "pa30"); SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; } } {1 999 999 2 131 130 999} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > | 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 | INSERT INTO t8 VALUES (4, NULL); INSERT INTO t8 VALUES (130, "pa31"); INSERT INTO t8 VALUES (131, "pa30"); SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; } } {1 999 999 2 131 130 999} # Make sure a left join where the right table is really a view that # is itself a join works right. Ticket #306. # do_test join-8.1 { execsql { BEGIN; CREATE TABLE t9(a INTEGER PRIMARY KEY, b); INSERT INTO t9 VALUES(1,11); INSERT INTO t9 VALUES(2,22); CREATE TABLE t10(x INTEGER PRIMARY KEY, y); INSERT INTO t10 VALUES(1,2); INSERT INTO t10 VALUES(3,3); CREATE TABLE t11(p INTEGER PRIMARY KEY, q); INSERT INTO t11 VALUES(2,111); INSERT INTO t11 VALUES(3,333); CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; COMMIT; SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); } } {1 11 1 111 2 22 {} {}} do_test join-8.2 { execsql { SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); } } {1 111 1 11 3 333 {} {}} finish_test |