SQLite

All files named ”test/join.test”
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

History for test/join.test

2025-06-16
17:36
[2fcfd84640] part of check-in [9441fff52c] Fix an issue going back to version 3.39.0 with transitive IS constraints in queries that make use of RIGHT JOIN. Problem reported by forum post 68f29a2005. (check-in: [9441fff52c] user: drh branch: trunk, size: 39489)
2025-05-30
22:58
[255c1f42b7] part of check-in [cf5b37b3a3] Follow-up to the previous: The same optimization suppression needs to happen if the left-hand side is coming from a LEFT JOIN. (check-in: [cf5b37b3a3] user: drh branch: trunk, size: 38764)
19:55
[015ef539ea] part of check-in [60adc78a22] When synthesizing an ON constraint from a USING or NATURAL, if the left-hand side is coming from a RIGHT JOIN, be sure to set the EP_CanBeNull flag so that the optimizer knows to check for NULL even if the column has a NOT NULL constraint. Fix for the problem reported by forum post 4fc70203b61 (check-in: [60adc78a22] user: drh branch: trunk, size: 38361)
2025-05-29
18:44
[aca62194ad] part of check-in [29b1e1b976] Extend the fix for ticket [623eff57e76d45f6] so that it covers RIGHT JOIN in addition to LEFT JOIN. Problem reported by forum post 2025-05-29T15:10:14Z. (check-in: [29b1e1b976] user: drh branch: trunk, size: 37390)
2025-04-10
10:18
[0cc86e5fd5] part of check-in [20acd630b9] Remove unnecessary "www." prefixes on domain names in URLs. (check-in: [20acd630b9] user: drh branch: trunk, size: 36780)
2023-05-11
21:15
[f7abfef3fa] part of check-in [3e9c9bbdb5] Add restriction (5) to the omit-noop-join optimization. Fix for the issue reported by [forum/forumpost/49f2c7f690|forum post 49f2c7f690]. (check-in: [3e9c9bbdb5] user: drh branch: trunk, size: 36792)
2023-05-01
11:24
[aea7a4f55b] part of check-in [d095da0e7a] Do not apply the "AND false" optimization if either operand comes from the ON clause of a join. Fix for the problem identified by forum post 96cd4a7e9e. (check-in: [d095da0e7a] user: drh branch: trunk, size: 35867)
2023-02-27
14:48
[ed1daf9995] part of check-in [18ee689de3] Further to [46639f682975dac6], the parameters of a table valued function that is on the left side of a RIGHT JOIN do not need to be in the ON clause. Add new test cases and tags to associated test cases with the code. (check-in: [18ee689de3] user: drh branch: trunk, size: 35305)
2022-09-20
22:19
[e32cb9b149] part of check-in [f47aa74569] Do not allow EP_InnerON terms to be used on a outer join. (check-in: [f47aa74569] user: drh branch: flattener-omit-restriction-29, size: 33850)
21:07
[37d48f2d22] part of check-in [fe5c377364] Add new test cases that (current) get an incorrect answer. (check-in: [fe5c377364] user: drh branch: flattener-omit-restriction-29, size: 33865)
19:45
[41203c37d2] part of check-in [db55387b19] New test cases for query flattening when there are ON clauses and outer joins. (check-in: [db55387b19] user: drh branch: flattener-omit-restriction-29, size: 33373)
2022-06-23
22:43
[21dbc65ab2] part of check-in [27f68e4732] New test cases for query flattening with LEFT JOIN. (check-in: [27f68e4732] user: drh branch: flatten-left-join, size: 31402)
15:15
[6dacc1c712] part of check-in [f8fe936ad4] Add back the ability to flatten a LEFT JOIN subquery - previously removed due to ticket [cad1ab4cb7b0fc344]. (check-in: [f8fe936ad4] user: drh branch: flatten-left-join, size: 30986)
2022-06-08
13:13
[5c7f917aa2] part of check-in [12d3c96c39] New test cases refute check-in [3f45007d544e5f78]. (check-in: [12d3c96c39] user: drh branch: right-join-query-flattener, size: 30220)
2022-05-10
18:43
[edeaff6edc] part of check-in [e8479e56c6] New requirement marks. (check-in: [e8479e56c6] user: drh branch: trunk, size: 29158)
2022-04-21
14:08
[e5f165dfd8] part of check-in [e611e8e62c] Ensure correct fg.isNestedFrom values even on crazy parses. (check-in: [e611e8e62c] user: drh branch: right-join, size: 28693)
2022-04-12
18:40
[d9c8cb2769] part of check-in [ab0a0562dd] For the bad join type error message "unknown or unsupported join type" remove the "or unsupported" clause, because we now support all valid join types. (check-in: [ab0a0562dd] user: drh branch: right-join, size: 28467)
2022-04-08
19:20
[a1832675aa] part of check-in [415abd6731] Preliminary code to support RIGHT JOIN. Everything seems to work, except that the code to compute the unmatched rows for the RIGHT JOIN has not yet been added, so the result of a RIGHT JOIN is currently the same as an INNER JOIN. (check-in: [415abd6731] user: drh branch: right-join, size: 28557)
2022-04-07
01:11
[85e9c88bf4] part of check-in [158156a3e3] Improved technique for parsing the ON and USING clauses of a join is faster and uses less memory. (check-in: [158156a3e3] user: drh branch: trunk, size: 28550)
2022-01-16
19:11
[25cf0ac11c] part of check-in [4883776669] Fix test cases so that they all still work even with -DSQLITE_DQS=0. (check-in: [4883776669] user: drh branch: trunk, size: 28578)
2020-09-30
15:36
[25da4f5352] part of check-in [69f9eb7343] In the query flattener when substituting expression Y in place of expression X, if X is marked has having come from an ON or USING clause of a LEFT JOIN, then be sure that all subexpressions of Y, not just the root node of Y, are similarly marked. Otherwise, if Y is an AND operator, it will be split up during WHERE clause analysis and the subexpressions will not get the special treatment needed by LEFT JOIN ON/USING clauses. Fix for ticket [66e4b0e271c47145]. (check-in: [69f9eb7343] user: drh branch: trunk, size: 28529)
2020-07-29
16:18
[1ffb015659] part of check-in [a80ae2c98b] Dozens and dozens of typo fixes in comments. This change adds no value to the end product and is disruptive, so it is questionable whether or not it will ever land on trunk. (check-in: [a80ae2c98b] user: drh branch: typos, size: 28103)
2020-01-22
20:36
[06b2855f16] part of check-in [0eae87ad11] Merge version 3.31.0 (check-in: [0eae87ad11] user: drh branch: apple-osx, size: 28591)
2020-01-21
16:23
[bca044589e] part of check-in [934ee8bdb4] Fix a problem with using views in SQLITE_OMIT_VIRTUAL_TABLE builds. Also some test case fixes required for the same builds. (check-in: [934ee8bdb4] user: dan branch: trunk, size: 28104)
2020-01-15
12:49
[413ffda0d5] part of check-in [dadedf41c7] Merge all recent enhancements and fixes from trunk into the apple-osx branch. (check-in: [dadedf41c7] user: drh branch: apple-osx, size: 28572)
2019-12-28
15:24
[0e8d3f4092] part of check-in [f4bed1d7af] Fix an instance where the planner might choose to use the OR-optimization when it adds no benefit. The same quirk causes an assert() to fail. This is not a bug in released versions - without the assert() the library still gets the right answer, it just does so less efficiently than it should. (check-in: [f4bed1d7af] user: dan branch: trunk, size: 28085)
2019-12-22
20:03
[d7e315cd05] part of check-in [ef604882a2] When constructing the virtual MATCH term of the WHERE clause for a virtual table that is in a LEFT JOIN, be sure to set the correct Expr.iRightJoinTable value. This value does not appear to ever be used, except inside of a single assert(). But it is good to set it correctly, nevertheless. This fixes ticket [7929c1efb2d67e98], which as far as I can tell is completely harmless. (check-in: [ef604882a2] user: drh branch: trunk, size: 27514)
2019-12-18
20:51
[99e1d82fad] part of check-in [862974312e] Continue to back away from the LEFT JOIN optimization of check-in [41c27bc0ff1d3135] by disallowing query flattening if the outer query is DISTINCT. Without this fix, if an index scan is run on the table within the view on the right-hand side of the LEFT JOIN, stale result registers might be accessed yielding incorrect results, and/or an OP_IfNullRow opcode might be invoked on the un-opened table, resulting in a NULL-pointer dereference. This problem was found by the Yongheng and Rui fuzzer. (check-in: [862974312e] user: drh branch: trunk, size: 27184)
2019-11-30
19:29
[f787ee2716] part of check-in [4066a34da7] Do not allow a term in the WHERE clause of the query to qualify a partial index on the right table of a LEFT JOIN. Ticket [7f39060a24b47353] (check-in: [4066a34da7] user: drh branch: trunk, size: 26777)
2019-11-03
00:07
[debdadab63] part of check-in [3be19e1151] The optimization of check-in [9b2879629c34fc0a] is incorrectly reasoned. The WHERE clause of the partial index might not be true if the table of the partial index is the right table of a left join. So disable the optimization in that case. Ticket [623eff57e76d45f6] (check-in: [3be19e1151] user: drh branch: trunk, size: 25966)
2019-10-29
16:18
[832f7c7550] part of check-in [4d424f3047] Remove the legacy_file_format PRAGMA. In its place, provide the SQLITE_DBCONFIG_LEGACY_FILE_FORMAT option to sqlite3_db_config(). Fix for ticket [6484e6ce678fffab] (check-in: [4d424f3047] user: drh branch: trunk, size: 25388)
2019-10-10
17:09
[b515753842] part of check-in [7833feecfe] Prevent SQLite from assuming that if ((? IS NOT NULL) IS NOT NULL) is true, ? may not be NULL. Fix for [c31034044bb72c89]. (check-in: [7833feecfe] user: dan branch: trunk, size: 25389)
2019-10-09
21:14
[7610c1818f] part of check-in [eb7ed90b8a] Avoid assuming that an expression that contains the sub-expression (? IS FALSE) or (? IS TRUE) may only be true if ? is non-null. Fix for [a976c487]. (check-in: [eb7ed90b8a] user: dan branch: trunk, size: 24660)
2019-08-29
15:50
[6ce8296a08] part of check-in [616f5663b3] Fix another case where SQLite assumes that if "~(? AND FALSE)" is true, "?" must be non-null. (check-in: [616f5663b3] user: dan branch: trunk, size: 24172)
2019-08-17
17:07
[f505c78a79] part of check-in [500c9152da] Ensure the functions that appear to be constant are not factored out of expression that originate on the right-hand side of a LEFT JOIN. Ticket [6710d2f7a13a2997] (check-in: [500c9152da] user: drh branch: trunk, size: 24034)
2019-02-05
14:36
[d53a366276] part of check-in [d840e9bb02] The IS NOT NULL operator does not imply that the operand is never NULL. Fix for ticket [5948e09b8c415bc45da5cf] (check-in: [d840e9bb02] user: drh branch: trunk, size: 23485)
2019-01-17
14:34
[b6f771f4db] part of check-in [7cac614d5d] Limit the size of SrcList objects to 200 entries (compile-time configurable using -DSQLITE_MAX_SRCLIST=n). The maximum number of tables in a join has always been 64, so this is not a real constraint on capability. Limiting the size of a SrcList prevents DOS attacks (discovered by OSSFuzz) using crazy nexted CTE joins. (check-in: [7cac614d5d] user: drh branch: trunk, size: 23087)
2018-04-10
15:31
[2ad9d7fe10] part of check-in [1fdaf2c344] Fix a problem causing the LEFT JOIN strength reduction optimization to be incorrectly applied in some cases where the WHERE clause of the query contains a filter expression of the form "lhs.x IS NOT ?". (check-in: [1fdaf2c344] user: dan branch: trunk, size: 22902)
12:10
[6fa8e2bad2] part of check-in [38d319c153] Fix a problem causing the LEFT JOIN strength reduction optimization to be incorrectly applied in some cases where the WHERE clause of the query contains a filter expression of the form NOT(x AND y). Ticket [1e39b966]. (check-in: [38d319c153] user: dan branch: trunk, size: 22774)
2018-03-24
15:47
[730e3e8d51] part of check-in [e88cf3d4df] Yet another fault in the sqlite3ExprImpliesNotNull() routine, causing errors in the LEFT JOIN strength reduction optimization of check-in [dd568c27b1d76563]. (check-in: [e88cf3d4df] user: drh branch: trunk, size: 22636)
13:24
[cffed24d43] part of check-in [cf171abe95] Bug fix in the LEFT JOIN strength reduction optimization of check-in [dd568c27b1d76563]. The sqlite3ExprImpliesNotNull() routine was mistakenly assuming that a CASE expression must always be NULL if contained any reference to a variable that was NULL. (check-in: [cf171abe95] user: drh branch: trunk, size: 22512)
2017-06-20
17:43
[442c462eea] part of check-in [483462682d] Ensure that the query planner knows that any column of a flattened LEFT JOIN can be NULL even if that column is labeled with "NOT NULL". Fix for ticket [892fc34f173e99d8]. (check-in: [483462682d] user: dan branch: trunk, size: 20660)
2017-05-25
00:08
[302f164f4a] part of check-in [77fc23013c] The TK_IF_NULL_ROW expression node must be treated as a variable that references the table Expr.iTable. Proposed fix for ticket [7fde638e94287d2c]. (check-in: [77fc23013c] user: drh branch: trunk, size: 20155)
2017-05-23
15:21
[a26e4c45ed] part of check-in [44b21e35c9] Disable the LEFT JOIN flattening optimization for aggregate queries, as it does not currently work. Further fix for ticket [cad1ab4cb7b0fc344]. (check-in: [44b21e35c9] user: drh branch: trunk, size: 19573)
12:36
[89087a3285] part of check-in [941d8142b7] Ensure that the expression rewriter inside the query flattener decends into the substructure of the TK_IF_NULL_ROW operator. This is a continuation of the fix for ticket [cad1ab4cb7b0fc344]. (check-in: [941d8142b7] user: drh branch: trunk, size: 19180)
01:21
[5cab75bb78] part of check-in [92c178507d] When flattening a query, make sure iTable attribute of TK_IF_NULL_ROW operators (that result from a prior flattening of a LEFT JOIN) are updated correctly. Fix for ticket [cad1ab4cb7b0fc344]. (check-in: [92c178507d] user: drh branch: trunk, size: 19036)
2015-06-08
18:48
[f9d4a28dec] part of check-in [0d9edfab9f] If a query contains "FROM t1 LEFT JOIN t2, t3, t4", ensure that tables t3 and t4 are not scanned before t2. The trunk already does this. (check-in: [0d9edfab9f] user: dan branch: vtab-left-join, size: 18560)
2014-07-18
17:39
[52d4d49f86] part of check-in [824dde7fc4] SQLite has long accepted some unadvertised and non-standard join syntax. Add a test to ensure that future versions continue to accept this non-standard syntax, to avoid breaking legacy applications that use the undefined syntax. (check-in: [824dde7fc4] user: drh branch: trunk, size: 17857)
2014-03-05
19:13
[559b81eb56] part of check-in [e406d4bb78] Avoid running a couple of test cases that use realloc() extensively if SQLITE_MEMDEBUG is defined. (check-in: [e406d4bb78] user: dan branch: trunk, size: 17363)
16:15
[533dc4c1ca] part of check-in [ef1a998625] Add tests to join.test to verify that it is not possible to use more than 64 tables in a join. (check-in: [ef1a998625] user: dan branch: trunk, size: 17002)
2009-10-19
15:52
[8d63cc4d23] part of check-in [6fe6371175] When generating WHERE clause terms internally for NATURAL and USING joins, identify the table by its position in the FROM list, not by its name or alias. Fix for [b73fb0bd64]. (check-in: [6fe6371175] user: dan branch: trunk, size: 15985)
2009-07-01
16:12
[a79084b09d] part of check-in [29b48972b6] Report an error if a USING or ON clause is specified following a from-list element that is not to the right of a join operator. Fix for #3846. (CVS 6832) (check-in: [29b48972b6] user: danielk1977 branch: trunk, size: 14268)
2008-12-05
00:00
[c5696c7e7e] part of check-in [06d206ef7d] Expand table.* properly on a USING or a NATURAL join. Ticket #3522. (CVS 5979) (check-in: [06d206ef7d] user: drh branch: trunk, size: 14246)
2008-08-14
00:19
[e0664af757] part of check-in [8947c72f93] Do not flatten the right term of a LEFT join. Ticket #3300. (CVS 5565) (check-in: [8947c72f93] user: drh branch: trunk, size: 12856)
2008-07-09
14:47
[fc6c4e2132] part of check-in [417aa1580a] Add a test case to join.test. No code changes, no bugs found. (CVS 5384) (check-in: [417aa1580a] user: danielk1977 branch: trunk, size: 12527)
2008-07-08
23:40
[f6aab432bd] part of check-in [cbd3c1585b] Remove obsolete code from select.c, including the "affinity" parameter to the sqlite3Select() module. (CVS 5380) (check-in: [cbd3c1585b] user: drh branch: trunk, size: 12047)
2006-06-20
11:01
[af04431853] part of check-in [c6ea353bd9] Modifications so that compilation and testing with the various OMIT macros defined works. (CVS 3276) (check-in: [c6ea353bd9] user: danielk1977 branch: trunk, size: 11326)
2006-03-02
04:44
[a74f84f8ac] part of check-in [a286e54e26] Allow WHERE clause terms on the left table of a LEFT OUTER JOIN to contain aggregate subqueries. Ticket #1697. (CVS 3117) (check-in: [a286e54e26] user: drh branch: trunk, size: 11273)
2005-06-06
17:11
[db3802739f] part of check-in [6a51bdeeff] Avoid ambiguous column name errors when the column name is in the USING clause of a join. Ticket #743. (CVS 2495) (check-in: [6a51bdeeff] user: drh branch: trunk, size: 10932)
16:59
[8d17a014c0] part of check-in [265fb6079c] Naming the join columns in the result set of a natural join does not result in an "ambiguous column name" error. Ticket #1217. (CVS 2494) (check-in: [265fb6079c] user: drh branch: trunk, size: 10848)
2005-01-21
15:52
[e084712795] part of check-in [17122c7e8e] Final preparations for the 3.1.0 alpha release. (CVS 2255) (check-in: [17122c7e8e] user: drh branch: trunk, size: 10765)
04:25
[95ee10ed1a] part of check-in [d4e19efcef] Modify test scripts to work when SQLITE_OMIT_SUBQUERY (but no other OMIT macros) is defined. (CVS 2252) (check-in: [d4e19efcef] user: danielk1977 branch: trunk, size: 10751)
03:12
[a61c9328ef] part of check-in [bb0254ab14] Modify test scripts to work when SQLITE_OMIT_SUBQUERY (along with other OMIT macros) is defined. (CVS 2251) (check-in: [bb0254ab14] user: danielk1977 branch: trunk, size: 10646)
2005-01-18
17:40
[59e5abff59] part of check-in [c06add57bf] Make sure USING and NATURAL work on joins even if the columns are renamed using an AS phrase. Ticket #523. (CVS 2233) (check-in: [c06add57bf] user: drh branch: trunk, size: 10590)
16:02
[76a6450903] part of check-in [9295050af1] Column names coming back from a SELECT are now just the name of the source column without the "table." prefix. In other words, "PRAGMA short_column_names=ON" is now the default. This makes the names of columns behave more like other SQL engines. The old behavior can be restored by setting "PRAGMA short_column_names=OFF". (CVS 2231) (check-in: [9295050af1] user: drh branch: trunk, size: 9971)
2004-11-22
13:35
[ea8c77b9fb] part of check-in [007be591b7] Ensure tests pass when SQLITE_OMIT_COMPOUND_SELECT is defined. (CVS 2138) (check-in: [007be591b7] user: danielk1977 branch: trunk, size: 10209)
08:43
[4bee854ef7] part of check-in [711e8d7695] Modify test suite to work when SQLITE_OMIT_VIEW is defined. (CVS 2132) (check-in: [711e8d7695] user: danielk1977 branch: trunk, size: 10164)
2003-09-27
13:39
[9ef6aabaac] part of check-in [824430b3ce] Remove support for the Oracle8 outer join syntax. (CVS 1106) (check-in: [824430b3ce] user: drh branch: trunk, size: 10082)
2003-06-16
00:40
[54e770b74f] part of check-in [dd8943e858] 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 branch: trunk, size: 11312)
2003-05-06
20:35
[16c91ec271] part of check-in [6d4b6597e5] Restrain the flattener in the presence of outer joins. Ticket #306. (CVS 958) (check-in: [6d4b6597e5] user: drh branch: trunk, size: 10168)
2003-02-20
01:48
[c97267c192] part of check-in [6a45fe3bd7] When the right table in a LEFT OUTER JOIN contains an INTEGER PRIMARY KEY make sure that key is NULL if there is no row in the right table that matches the current row in the left table. Tickets #246 and #247. (CVS 873) (check-in: [6a45fe3bd7] user: drh branch: trunk, size: 9383)
2002-10-27
19:35
[178b25dc3c] part of check-in [31df3690d0] Minimal support for oracle8 outer join syntax. (CVS 771) (check-in: [31df3690d0] user: drh branch: trunk, size: 8640)
2002-09-30
12:36
[05d2cd1af8] part of check-in [6c0f44bd63] Fix a bug in the left outer join logic. (CVS 758) (check-in: [6c0f44bd63] user: drh branch: trunk, size: 7410)
2002-07-31
19:50
[90a620f2a2] part of check-in [fe329e078f] Fix for ticket #124: Fix a stack VDBE overflow problem on joins on an INTEGER PRIMARY KEY. (CVS 700) (check-in: [fe329e078f] user: drh branch: trunk, size: 6808)
2002-07-01
12:27
[75561ec0e5] part of check-in [1f8a73b1c3] Fix for tickets #90 and #89: Make the AS keyword optional again. (CVS 654) (check-in: [1f8a73b1c3] user: drh branch: trunk, size: 5627)
2002-06-28
12:18
[89bd995450] part of check-in [96515b813e] Additional test cases added. The following bug fixed: A segfault was occurring if a VIEW consisted of a join with a USING clause. (CVS 651) (check-in: [96515b813e] user: drh branch: trunk, size: 5617)
2002-05-25
00:18
[ea6a4097e4] part of check-in [d8d04c14f1] Additional testing of LEFT OUTER JOIN. (CVS 588) (check-in: [d8d04c14f1] user: drh branch: trunk, size: 5218)
2002-05-24
20:31
Added: [905f4b13f8] part of check-in [99bd1f5b9a] Initial implementation of LEFT OUTER JOIN including the expanded SQL92 join syntax. The basic functionality is there but there is still a lot of testing to do. (CVS 587) (check-in: [99bd1f5b9a] user: drh branch: trunk, size: 1873)