Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not flatten the right term of a LEFT join. Ticket #3300. (CVS 5565) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
8947c72f93d0b79c8061a3bfd5ab595e |
User & Date: | drh 2008-08-14 00:19:49.000 |
Context
2008-08-20
| ||
14:49 | Add the pcache module from the experimental branch. Also change things so that most of the built-in SQL functions are kept in single static hash-table, rather than creating and populating a separate hash table for each open database connection. (CVS 5566) (check-in: cb494e10d7 user: danielk1977 tags: trunk) | |
2008-08-14
| ||
00:19 | Do not flatten the right term of a LEFT join. Ticket #3300. (CVS 5565) (check-in: 8947c72f93 user: drh tags: trunk) | |
2008-08-13
| ||
20:09 | Explicit casts of the return from strlen() to int in lemon. This has no effect on SQLite. It has no effect on any lemon-generated parser with a grammar that is less than 2GB in size. Ticket #3293. (CVS 5564) (check-in: a519cdb2f4 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.465 2008/08/14 00:19:49 drh Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
2911 2912 2913 2914 2915 2916 2917 | ** ** 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. ** | | | | 2911 2912 2913 2914 2915 2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 | ** ** 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 ** (Originally ticket #306. Strenghtened by ticket #3300) ** ** (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 |
︙ | ︙ | |||
2934 2935 2936 2937 2938 2939 2940 | ** 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. ** | | | | 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 | ** 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) Not implemented. Subsumed into restriction (3). Was previously ** a separate restriction deriving from ticket #350. ** ** (13) The subquery and outer query do not both use LIMIT ** ** (14) The subquery does not use OFFSET ** ** (15) The outer query is not part of a compound select or the ** subquery does not have both an ORDER BY and a LIMIT clause. |
︙ | ︙ | |||
3029 3030 3031 3032 3033 3034 3035 | } if( p->isDistinct && subqueryIsAgg ) return 0; /* Restriction (6) */ if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ){ return 0; /* Restriction (11) */ } if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */ | > | | < < < | | > > > > > | | 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 3075 | } if( p->isDistinct && subqueryIsAgg ) return 0; /* Restriction (6) */ if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ){ return 0; /* Restriction (11) */ } if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */ /* OBSOLETE COMMENT 1: ** 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. ** ** OBSOLETE COMMENT 2: ** 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. ** ** THIS OVERRIDES OBSOLETE COMMENTS 1 AND 2 ABOVE: ** Ticket #3300 shows that flattening the right term of a LEFT JOIN ** is fraught with danger. Best to avoid the whole thing. If the ** subquery is the right term of a LEFT JOIN, then do not flatten. */ if( (pSubitem->jointype & JT_OUTER)!=0 ){ return 0; } /* Restriction 17: If the sub-query is a compound SELECT, then it must ** use only the UNION ALL operator. And none of the simple select queries ** that make up the compound SELECT are allowed to be aggregate or distinct ** queries. |
︙ | ︙ |
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.25 2008/08/14 00:19:49 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test join-1.1 { execsql { CREATE TABLE t1(a,b,c); |
︙ | ︙ | |||
418 419 420 421 422 423 424 425 426 427 428 429 430 431 | } {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 {} {}} } ;# ifcapable view # 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 | > > > > > > > > > > | 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 | } {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 {} {}} ifcapable subquery { # Constant expressions in a subquery that is the right element of a # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not # match. Ticket #3300 do_test join-8.4 { execsql { SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a } } {1 11 {} {} {} 2 22 44 2 111} } } ;# ifcapable view # 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 |
︙ | ︙ |