From fb98dac04f7d4ae4d2c9c460803cb7bb81ff565c Mon Sep 17 00:00:00 2001 From: drh <> Date: Wed, 25 May 2022 02:32:11 +0000 Subject: [PATCH] An attempt to get UPDATE FROM working when the FROM clause contains a RIGHT or FULL JOIN. FossilOrigin-Name: a124e4f96f883d8682ba7a253d33a9565ed0fc3580525225b95733bd3782a806 --- manifest | 18 ++++++++++------ manifest.uuid | 2 +- src/parse.y | 13 ++++++++++- src/select.c | 38 ++++++++++++++++++++++++-------- test/upfrom4.test | 55 +++++++++++++++++++++++++++++++++++++++++++++++ 5 files changed, 108 insertions(+), 18 deletions(-) create mode 100644 test/upfrom4.test diff --git a/manifest b/manifest index a6f75bdb85..b84a6ae9d5 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C When\san\sON\sclause\son\san\sINNER\sJOIN\sreferences\sa\stable\sto\sthe\sright\sof\nof\sthe\sjoin,\sjust\sconvert\sthe\sON\sclause\sto\san\sordinary\sWHERE\sclause\sterm,\nin\sorder\sto\sbe\scompatible\swith\solder\sversions\sof\sSQLite.\s\sSee\n[forum:/forumpost/687b0bf563a1d4f1|forum\sthread\s687b0bf563a1d4f1]\sfor\sdetails. -D 2022-05-24T16:05:41.137 +C An\sattempt\sto\sget\sUPDATE\sFROM\sworking\swhen\sthe\sFROM\sclause\scontains\sa\nRIGHT\sor\sFULL\sJOIN. +D 2022-05-25T02:32:11.876 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -557,7 +557,7 @@ F src/os_win.c a8ea80037e81127ca01959daa87387cc135f325c88dc745376c4f760de852a10 F src/os_win.h 7b073010f1451abe501be30d12f6bc599824944a F src/pager.c 42120492784fc9bcd9082b5c9b5e329b7318c357f9f3574a1bbfcf7418910356 F src/pager.h f82e9844166e1585f5786837ddc7709966138ced17f568c16af7ccf946c2baa3 -F src/parse.y ad9a59d0078267e5aea4cf62f244c0ce6fcec9a91edd365e929c75d3a1716563 +F src/parse.y b58ad9fadf31e26e45214a4d8be4351ec2910bbd914caa2e172d616057f67ddc F src/pcache.c 084e638432c610f95aea72b8509f0845d2791293f39d1b82f0c0a7e089c3bb6b F src/pcache.h 4f87acd914cef5016fae3030343540d75f5b85a1877eed1a2a19b9f284248586 F src/pcache1.c 54881292a9a5db202b2c0ac541c5e3ef9a5e8c4f1c1383adb2601d5499a60e65 @@ -568,7 +568,7 @@ F src/printf.c 6166a30417b05c5b2f82e1f183f75faa2926ad60531c0b688a57dbc951441a20 F src/random.c 097dc8b31b8fba5a9aca1697aeb9fd82078ec91be734c16bffda620ced7ab83c F src/resolve.c a4eb3c617027fd049b07432f3b942ea7151fa793a332a11a7d0f58c9539e104f F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92 -F src/select.c 4f13d01141caae4f982125fecf50703c746ab621c57ca0aa747fab2b28e88c1e +F src/select.c a0dca0b4a328826d4713195a2fe305852cd8b1ed876311667b00c64b93a7fe23 F src/shell.c.in 55d71bf8c7a8f2a66bc5f99cd76f226790b291599b83415533dad84a553ed806 F src/sqlite.h.in d15c307939039086adca159dd340a94b79b69827e74c6d661f343eeeaefba896 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 @@ -1707,6 +1707,7 @@ F test/upfrom1.tcl 8859d9d437f03b44174c4524a7a734a391fd4526fcff65be08285dafc9dc9 F test/upfrom1.test 8cb06689e99cd707d884faa16da0e8eb26ff658bb01c47ddf72fadade666e6e1 F test/upfrom2.test 66f3ebf721b3cebd922faee5c386bf244f816d416b57c000753ff51af62328a1 F test/upfrom3.test 6130f24ebf97f5ea865e5d2a14a2d543fe5428a62e87cc60f62d875e45c1f5f0 +F test/upfrom4.test 71d25e18c7d04e2930dcbd78e6aafb6931c80cb08f77039309b6095a12f1e62e F test/upfromfault.test 3a10075a0043f0c4fad6614b2c371f88a8ba5a4acab68b907438413865d6a8d6 F test/upsert1.test b0ae2f58680c5205b4bc1cdeed3c3d444057c506f6c44494fa3eac60731d68a2 F test/upsert2.test 720e94d09f7362a282bc69b3c6b83d51daeaaf0440eb4920a08b86518b8c7496 @@ -1969,8 +1970,11 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 2f9a42fb141d386f6edd03a37da3b0cef63dcc9fbfd076076b5330a8aa7d45a8 -R b23285aa9cad2fbb603a6d0b592197f4 +P 2b6ebba26d936ae7b9acf7d4bd15e82cbfabda22e1044b3dd838c7b07095100e +R 014358eba344e3263997b7e1fc402f9a +T *branch * update-from-right-join +T *sym-update-from-right-join * +T -sym-trunk * U drh -Z a3136607e1e2d962d7405d0d9fcd3d10 +Z f458636c8150cdb5371ae8ca6d7f2721 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 1cb0daf032..4b2b715906 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -2b6ebba26d936ae7b9acf7d4bd15e82cbfabda22e1044b3dd838c7b07095100e \ No newline at end of file +a124e4f96f883d8682ba7a253d33a9565ed0fc3580525225b95733bd3782a806 \ No newline at end of file diff --git a/src/parse.y b/src/parse.y index fb3c3f38cd..44a14e2585 100644 --- a/src/parse.y +++ b/src/parse.y @@ -940,7 +940,18 @@ cmd ::= with UPDATE orconf(R) xfullname(X) indexed_opt(I) SET setlist(Y) from(F) where_opt_ret(W). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); - X = sqlite3SrcListAppendList(pParse, X, F); + if( F ){ + SrcList *pFromClause = F; + if( pFromClause->nSrc>1 ){ + Select *pSubquery; + Token as; + pSubquery = sqlite3SelectNew(pParse,0,pFromClause,0,0,0,0,SF_NestedFrom,0); + as.n = 0; + as.z = 0; + pFromClause = sqlite3SrcListAppendFromTerm(pParse,0,0,0,&as,pSubquery,0); + } + X = sqlite3SrcListAppendList(pParse, X, pFromClause); + } sqlite3Update(pParse,X,Y,W,R,0,0,0); } %endif diff --git a/src/select.c b/src/select.c index 96b5035f90..57f1b45957 100644 --- a/src/select.c +++ b/src/select.c @@ -6518,6 +6518,29 @@ static int countOfViewOptimization(Parse *pParse, Select *p){ } #endif /* SQLITE_COUNTOFVIEW_OPTIMIZATION */ +/* +** If any term of pSrc, or any SF_NestedFrom sub-query, is not the same +** as pSrcItem but has the same alias as p0, then return true. +** Otherwise return false. +*/ +static int sameSrcAlias(SrcItem *p0, SrcList *pSrc){ + int i; + for(i=0; inSrc; i++){ + SrcItem *p1 = &pSrc->a[i]; + if( p1==p0 ) continue; + if( p0->pTab==p1->pTab && 0==sqlite3_stricmp(p0->zAlias, p1->zAlias) ){ + return 1; + } + if( p1->pSelect + && (p1->pSelect->selFlags & SF_NestedFrom)!=0 + && sameSrcAlias(p0, p1->pSelect->pSrc) + ){ + return 1; + } + } + return 0; +} + /* ** Generate code for the SELECT statement given in the p argument. ** @@ -6622,15 +6645,12 @@ int sqlite3Select( ** disallow it altogether. */ if( p->selFlags & SF_UFSrcCheck ){ SrcItem *p0 = &p->pSrc->a[0]; - for(i=1; ipSrc->nSrc; i++){ - SrcItem *p1 = &p->pSrc->a[i]; - if( p0->pTab==p1->pTab && 0==sqlite3_stricmp(p0->zAlias, p1->zAlias) ){ - sqlite3ErrorMsg(pParse, - "target object/alias may not appear in FROM clause: %s", - p0->zAlias ? p0->zAlias : p0->pTab->zName - ); - goto select_end; - } + if( sameSrcAlias(p0, p->pSrc) ){ + sqlite3ErrorMsg(pParse, + "target object/alias may not appear in FROM clause: %s", + p0->zAlias ? p0->zAlias : p0->pTab->zName + ); + goto select_end; } /* Clear the SF_UFSrcCheck flag. The check has already been performed, diff --git a/test/upfrom4.test b/test/upfrom4.test new file mode 100644 index 0000000000..d0dcc953c9 --- /dev/null +++ b/test/upfrom4.test @@ -0,0 +1,55 @@ +# 2022-05-24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom4 + +do_execsql_test 100 { + DROP TABLE IF EXISTS t5; + DROP TABLE IF EXISTS m1; + DROP TABLE IF EXISTS m2; + CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); + CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); + + INSERT INTO t5 VALUES(1, 'one', 'ONE'); + INSERT INTO t5 VALUES(2, 'two', 'TWO'); + INSERT INTO t5 VALUES(3, 'three', 'THREE'); + INSERT INTO t5 VALUES(4, 'four', 'FOUR'); + + INSERT INTO m1 VALUES(1, 'i'); + INSERT INTO m1 VALUES(2, 'ii'); + INSERT INTO m1 VALUES(3, 'iii'); + + INSERT INTO m2 VALUES(1, 'I'); + INSERT INTO m2 VALUES(3, 'II'); + INSERT INTO m2 VALUES(4, 'III'); + SELECT * FROM t5; +} {1 one ONE 2 two TWO 3 three THREE 4 four FOUR} + +do_execsql_test 110 { + BEGIN; + UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; + SELECT * FROM t5 ORDER BY a; + ROLLBACK; +} {1 i I 2 ii {} 3 iii II 4 four FOUR} + +do_execsql_test 120 { + BEGIN; + UPDATE t5 SET b=y, c=v FROM m2 RIGHT JOIN m1 ON (x=u) WHERE x=a; + SELECT * FROM t5 ORDER BY a; + ROLLBACK; +} {1 i I 2 ii {} 3 iii II 4 four FOUR} + + +finish_test -- 2.39.5