From 7f70085071280f1853e194c5d7db1d1fb59bdb82 Mon Sep 17 00:00:00 2001 From: dan Date: Fri, 31 Oct 2025 16:07:31 +0000 Subject: [PATCH] Detect some cases of ambiguous column references in USING clauses that can come up with nested joins. FossilOrigin-Name: 38c993c8b7137d6d5623d387292639634297c17da11befec9029f12a16a472f8 --- manifest | 16 ++++++------- manifest.uuid | 2 +- src/resolve.c | 7 ++++-- test/joinH.test | 63 +++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 77 insertions(+), 11 deletions(-) diff --git a/manifest b/manifest index 3aff05c94a..075bc64ee8 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Change\sthe\sname\sof\sa\sfile-scope\sconstant\sarray\sin\scarray.c\sso\sthat\sit\sdoes\nnot\sconflict\swith\sother\svariables\snames\sin\sother\sfiles\swhen\scarray.c\sis\namalgamated.\s\sThis\sis\sdown\sto\sresolve\sa\sharmless\scompiler\swarning. -D 2025-10-31T15:18:29.031 +C Detect\ssome\scases\sof\sambiguous\scolumn\sreferences\sin\sUSING\sclauses\sthat\scan\scome\sup\swith\snested\sjoins. +D 2025-10-31T16:07:31.438 F .fossil-settings/binary-glob 61195414528fb3ea9693577e1980230d78a1f8b0a54c78cf1b9b24d0a409ed6a x F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea @@ -733,7 +733,7 @@ F src/pragma.c ecec75795c1821520266e4f93fa8840cce48979af532db06f085e36a7813860f F src/prepare.c 2af0b5c1ec787c8eebd21baa9d79caf4a4dc3a18e76ce2edbf2027d706bca37a F src/printf.c 7297c2aeed4d90d80c5ba82920d9e57b7bfad04b3466be1d7e042db382fe296e F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c -F src/resolve.c f8d1d011aba0964ff1bdccd049d4d2c2fec217efd90d202a4bb775e926b2c25d +F src/resolve.c 5616fbcf3b833c7c705b24371828215ad0925d0c0073216c4f153348d5753f0a F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97 F src/select.c ba9cd07ffa3277883c1986085f6ddc4320f4d35d5f212ab58df79a7ecc1a576a F src/shell.c.in e06c0e6b20cc8618e080496939155fc0a4728c9d9246923bfce9e52ad322575f @@ -1332,7 +1332,7 @@ F test/joinC.test 1f1a602c2127f55f136e2cbd3bf2d26546614bf8cffe5902ec1ac9c07f87f2 F test/joinD.test 2ce62e7353a0702ca5e70008faf319c1d4686aa19fba34275c6d1da0e960be28 F test/joinE.test d5d182f3812771e2c0d97c9dcf5dbe4c41c8e21c82560e59358731c4a3981d6b F test/joinF.test 53dd66158806823ea680dd7543b5406af151b5aafa5cd06a7f3231cd94938127 -F test/joinH.test fd76024ff104baec16417db5cafc0894ad4e0863e70803e63c1bba0322706339 +F test/joinH.test 1d2fc3190be68525fd9ce749b9468c40ba2930181e52fb5ee6f836051b38effb F test/joinI.test fc7d24a2b1e444979b83bd92c30ebb975cebb5b9eae4442ce94969bd8d083053 F test/journal1.test bc61a4228db11bffca118bd358ba4b868524bf080f3532749de6c539656e20fa F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4 @@ -2171,8 +2171,8 @@ F tool/version-info.c 33d0390ef484b3b1cb685d59362be891ea162123cea181cb8e6d2cf6dd F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee87c1b31a7 F tool/warnings.sh d924598cf2f55a4ecbc2aeb055c10bd5f48114793e7ba25f9585435da29e7e98 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P c1aa52361b8ede611f64b96f2394174389ce531b1d86b9cfec91d3b03c94f6ea -R 9284ddf37ecd249fbdc59088c02b46df -U drh -Z a8053b3b924d680e2231b0d1d7c51169 +P 5afd815ada94d55c9c56efe339235d23ffbf1fb64511a5e460927a2c8deaaaf0 +R 9fce4c06730bd171aafe337c923d19bb +U dan +Z 084dcba51f165497628862393726ae33 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index f629d9f34c..56c4f64e58 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -5afd815ada94d55c9c56efe339235d23ffbf1fb64511a5e460927a2c8deaaaf0 +38c993c8b7137d6d5623d387292639634297c17da11befec9029f12a16a472f8 diff --git a/src/resolve.c b/src/resolve.c index 00d8239401..16c193ca23 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -373,10 +373,13 @@ static int lookupName( if( cnt>0 ){ if( pItem->fg.isUsing==0 || sqlite3IdListIndex(pItem->u3.pUsing, zCol)<0 + || pMatch==pItem ){ /* Two or more tables have the same column name which is - ** not joined by USING. This is an error. Signal as much - ** by clearing pFJMatch and letting cnt go above 1. */ + ** not joined by USING. Or, a single table has two columns + ** that match a USING term (if pMatch==pItem). These are both + ** "ambiguous column name" errors. Signal as much by clearing + ** pFJMatch and letting cnt go above 1. */ sqlite3ExprListDelete(db, pFJMatch); pFJMatch = 0; }else diff --git a/test/joinH.test b/test/joinH.test index 3392009680..56fa9c7ec3 100644 --- a/test/joinH.test +++ b/test/joinH.test @@ -411,4 +411,67 @@ do_execsql_test 15.2 { SELECT lower(x), quote(y) FROM x1 LEFT JOIN x2 USING (x) JOIN x3 USING (x) FULL JOIN t4; } {abc NULL} +#------------------------------------------------------------------------- +reset_db +do_execsql_test 16.0 { + CREATE TABLE t0_a (c0 INT); + CREATE TABLE t0_b (c0 INT); + + CREATE TABLE t2 (c0 INT); + INSERT INTO t2 VALUES (1); +} + +do_execsql_test 16.1 { + SELECT * FROM t2 LEFT JOIN t0_b +} {1 {}} + +do_execsql_test 16.2.1 { + SELECT * FROM (t0_a RIGHT JOIN ( t2 LEFT JOIN t0_b)) +} {{} 1 {}} +do_execsql_test 16.2.2 { + SELECT * FROM (t0_a RIGHT JOIN (SELECT * FROM t2 LEFT JOIN t0_b)) +} {{} 1 {}} + + +do_catchsql_test 16.3.1 { + SELECT * FROM (t0_a RIGHT JOIN ( t2 LEFT JOIN t0_b) USING (c0)); +} {1 {ambiguous column name: c0}} + +do_execsql_test 16.3.2 { + SELECT * FROM (t0_a RIGHT JOIN (SELECT * FROM t2 LEFT JOIN t0_b) USING (c0)); +} {1 {}} + +do_execsql_test 16.4.0 { + CREATE TABLE x0(a TEXT); + CREATE TABLE x1(a TEXT); + CREATE TABLE x2(a TEXT); + + INSERT INTO x1 VALUES('blue'); + INSERT INTO x2 VALUES('red'); +} + +do_catchsql_test 16.4.1 { + SELECT * FROM x0 RIGHT JOIN ( x1, x2) USING (a) +} {1 {ambiguous column name: a}} + +do_execsql_test 16.4.2 { + SELECT * FROM x0 RIGHT JOIN (SELECT * FROM x1, x2) USING (a) +} {blue red} + +reset_db +do_execsql_test 16.5.0 { + CREATE TABLE t0 (c0 INT); + CREATE TABLE t1 (c0 INT); + CREATE TABLE t2 (c0 INT); + + INSERT INTO t1(c0) VALUES (NULL); + INSERT INTO t2 VALUES (1); + + CREATE VIEW v0(c0) AS SELECT 1 AS col_0 FROM t0; +} + +do_catchsql_test 16.5.2 { + SELECT * FROM (t0 NATURAL RIGHT JOIN (t0 FULL JOIN (v0 NATURAL FULL JOIN t2) ON TRUE)) NATURAL FULL JOIN t1; +} {1 {ambiguous column name: c0}} + finish_test -- 2.47.3