Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not flatten a subquery which is the right term of a LEFT OUTER JOIN if the subquery contains a WHERE clause. Ticket #350. (CVS 1027) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
dd8943e8583cf7ed3b662570a5607856 |
User & Date: | drh 2003-06-16 00:40:35.000 |
Context
2003-06-16
| ||
03:08 | Add the %z format to the sqlite_mprintf() and related functions. (CVS 1028) (check-in: eca1398eaa user: drh tags: trunk) | |
00:40 | Do not flatten a subquery which is the right term of a LEFT OUTER JOIN if the subquery contains a WHERE clause. Ticket #350. (CVS 1027) (check-in: dd8943e858 user: drh tags: trunk) | |
00:16 | Preserve blank lines in the middle of SQL statements in the shell. Ticket #352 (CVS 1026) (check-in: bcf5eeecdf 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.141 2003/06/16 00:40:35 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 | ** (9) The subquery does not use LIMIT or the outer query does not use ** aggregates. ** ** (10) The subquery does not use aggregates or the outer query does not ** use LIMIT. ** ** (11) The subquery and the outer query do not both have ORDER BY clauses. ** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. ** ** If flattening is not attempted, this routine is a no-op and returns 0. ** If flattening is attempted this routine returns 1. | > > > | 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 | ** (9) The subquery does not use LIMIT or the outer query does not use ** aggregates. ** ** (10) The subquery does not use aggregates or the outer query does not ** use LIMIT. ** ** (11) The subquery and the outer query do not both have ORDER BY clauses. ** ** (12) The subquery is not the right term of a LEFT OUTER JOIN or the ** subquery has no WHERE clause. (added by ticket #350) ** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. ** ** If flattening is not attempted, this routine is a no-op and returns 0. ** If flattening is attempted this routine returns 1. |
︙ | ︙ | |||
1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 | ** (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 | > > > > > > > > > > > > > > > > > > | 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 | ** (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; } /* Restriction 12: If the subquery is the right operand of a left outer ** join, make sure the subquery has no WHERE clause. ** An examples of why this is not allowed: ** ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0) ** ** If we flatten the above, we would get ** ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0 ** ** But the t2.x>0 test will always fail on a NULL row of t2, which ** effectively converts the OUTER JOIN into an INNER JOIN. */ if( iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 && pSub->pWhere!=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 |
︙ | ︙ |
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.10 2003/06/16 00:40:35 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test join-1.1 { execsql { CREATE TABLE t1(a,b,c); |
︙ | ︙ | |||
394 395 396 397 398 399 400 401 402 403 404 405 406 | 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 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 | 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 t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) ON( a=x); } } {1 11 1 111 2 22 {} {}} do_test join-8.3 { execsql { SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); } } {1 111 1 11 3 333 {} {}} # Ticket #350 describes a scenario where LEFT OUTER JOIN does not # function correctly if the right table in the join is really # subquery. # # To test the problem, we generate the same LEFT OUTER JOIN in two # separate selects but with on using a subquery and the other calling # the table directly. Then connect the two SELECTs using an EXCEPT. # Both queries should generate the same results so the answer should # be an empty set. # do_test join-9.1 { execsql { BEGIN; CREATE TABLE t12(a,b); INSERT INTO t12 VALUES(1,11); INSERT INTO t12 VALUES(2,22); CREATE TABLE t13(b,c); INSERT INTO t13 VALUES(22,222); COMMIT; SELECT * FROM t12 NATURAL LEFT JOIN t13 EXCEPT SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); } } {} do_test join-9.2 { execsql { CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; SELECT * FROM t12 NATURAL LEFT JOIN t13 EXCEPT SELECT * FROM t12 NATURAL LEFT JOIN v13; } } {} finish_test |