From 4b94ff802362960f0cfd0861877376fa0090a875 Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 22 Jul 2024 00:46:10 +0000 Subject: [PATCH] Allow LIMIT and OFFSET to contain variables from prior terms of the FROM clause in a LATERAL subquery. [forum:/forumpost/dfe2cd37ca3a9a80|Forum post dfe2cd37ca3a9a80] FossilOrigin-Name: 4f0515138acb5f04b5b2f8b9efa47e1b09f35060fb260dad44617dd88ae1bab8 --- manifest | 16 ++++++++-------- manifest.uuid | 2 +- src/resolve.c | 5 ++--- src/select.c | 1 + test/joinL.test | 37 +++++++++++++++++++++++++++++++++---- 5 files changed, 45 insertions(+), 16 deletions(-) diff --git a/manifest b/manifest index 74c80cc82f..1d97b91b54 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Merge\sdebugging\soutput\sfixes\sfrom\strunk\sinto\sthe\slateral-join\sbranch. -D 2024-07-21T23:36:32.351 +C Allow\sLIMIT\sand\sOFFSET\sto\scontain\svariables\sfrom\sprior\sterms\sof\sthe\nFROM\sclause\sin\sa\sLATERAL\ssubquery.\n[forum:/forumpost/dfe2cd37ca3a9a80|Forum\spost\sdfe2cd37ca3a9a80] +D 2024-07-22T00:46:10.960 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -753,9 +753,9 @@ F src/pragma.h e690a356c18e98414d2e870ea791c1be1545a714ba623719deb63f7f226d8bb7 F src/prepare.c d99931f45416652895e502328ca49fe782cfc4e1ebdcda13b3736d991ebf42ce F src/printf.c 8b250972305e14b365561be5117ed0fd364e4fd58968776df1ce64c6280b90f9 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c -F src/resolve.c 24bc6206fea31e1100ffb29bc4eed6908341787c75245c1224575f4393dce383 +F src/resolve.c bb2629301456e739d0bb245497af3d370cb740004107ee926a7a12c19443c652 F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97 -F src/select.c 1fdd9e2d9b68b6cf3fb1565c2ef34a48b4c81f154889b1c45ba7050dc035d27d +F src/select.c 1f5f1663fe84ee1963a55aef8871f1e7740a8030db517dcbfa16e27c6187fe74 F src/shell.c.in b7d435c137eb323981adff814f172dbaabb9ba504fef17cb11d4681c1633ee13 F src/sqlite.h.in 6c884a87bbf8828562b49272025a1e66e3801a196a58b0bdec87edcd2c9c8fc1 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 @@ -1347,7 +1347,7 @@ F test/joinD.test 2ce62e7353a0702ca5e70008faf319c1d4686aa19fba34275c6d1da0e960be F test/joinE.test d5d182f3812771e2c0d97c9dcf5dbe4c41c8e21c82560e59358731c4a3981d6b F test/joinF.test 53dd66158806823ea680dd7543b5406af151b5aafa5cd06a7f3231cd94938127 F test/joinH.test 55f69e64da74d4eca2235237f3acb657aef181e22e45daa228e35bba865e0255 -F test/joinL.test 87217fd82a283ba08597cfb3ba5a9a3eaea8a0971d357d463d3ada5105d1c3ae +F test/joinL.test f4c7e70dc2cc293b70d4fbbeab19724366e62d6ebc15eb7005bff6e269d2297e F test/journal1.test c7b768041b7f494471531e17abc2f4f5ebf9e5096984f43ed17c4eb80ba34497 F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4 F test/journal3.test 7c3cf23ffc77db06601c1fcfc9743de8441cb77db9d1aa931863d94f5ffa140e @@ -2196,8 +2196,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 6d863172dc736169bd5c303fab5d2fc47324261f29cdabb692781cd0fd609c87 b6aed8bcb06edc7f0221fd707d5adc227856fe62dbcaae5ffe1fb4faa5c542e1 -R f945ef86fef54dc43bf41701ceeec911 +P 9182e999524bf55bf6a336c02ff5fb129a89ee7ed6fd859d4050ba1dbffcf4df +R 1b514e88847c4dd672bf624993e269d1 U drh -Z 325044715fc2b274aef2f5fbc81890a3 +Z 2df173ea627adbc98fc3632c926e23f0 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index b782c8e550..9ac87b6e3b 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -9182e999524bf55bf6a336c02ff5fb129a89ee7ed6fd859d4050ba1dbffcf4df +4f0515138acb5f04b5b2f8b9efa47e1b09f35060fb260dad44617dd88ae1bab8 diff --git a/src/resolve.c b/src/resolve.c index 7a34612506..b8f9845322 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -1864,12 +1864,11 @@ static int resolveSelectStep(Walker *pWalker, Select *p){ assert( (p->selFlags & SF_Resolved)==0 ); p->selFlags |= SF_Resolved; - /* Resolve the expressions in the LIMIT and OFFSET clauses. These - ** are not allowed to refer to any names, so pass an empty NameContext. - */ + /* Resolve the expressions in the LIMIT and OFFSET clauses. */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pWinSelect = p; + sNC.pNext = pOuterNC; if( sqlite3ResolveExprNames(&sNC, p->pLimit) ){ return WRC_Abort; } diff --git a/src/select.c b/src/select.c index 372b775757..f0c31008ed 100644 --- a/src/select.c +++ b/src/select.c @@ -3977,6 +3977,7 @@ static void substSelect( substExprList(pSubst, p->pEList); substExprList(pSubst, p->pGroupBy); substExprList(pSubst, p->pOrderBy); + p->pLimit = substExpr(pSubst, p->pLimit); p->pHaving = substExpr(pSubst, p->pHaving); p->pWhere = substExpr(pSubst, p->pWhere); pSrc = p->pSrc; diff --git a/test/joinL.test b/test/joinL.test index 0e0b2080fb..e69dfbfefc 100644 --- a/test/joinL.test +++ b/test/joinL.test @@ -170,6 +170,39 @@ do_execsql_test 3.0 { do_execsql_test 4.0 { SELECT * FROM (VALUES (1), (2)) JOIN LATERAL (select COUNT(*), column1); } {1 1 1 2 1 2} +do_execsql_test 4.1 { + CREATE TABLE t3(x INTEGER PRIMARY KEY); + WITH RECURSIVE c(n) AS (VALUES(0) UNION ALL SELECT n+1 FROM c WHERE n<20) + INSERT INTO t3(x) SELECT n FROM c; + CREATE TABLE t4(y,z); + INSERT INTO t4 VALUES(1,0),(3,2),(5,7); + SELECT * FROM t4, LATERAL(SELECT x FROM t3 ORDER BY x LIMIT y OFFSET z); +} { + 1 0 0 + 3 2 2 + 3 2 3 + 3 2 4 + 5 7 7 + 5 7 8 + 5 7 9 + 5 7 10 + 5 7 11 +} +do_execsql_test 4.2 { + SELECT * FROM (VALUES(1,0),(3,2),(5,7)), + LATERAL(SELECT x FROM t3 ORDER BY x LIMIT column1 OFFSET column2); +} { + 1 0 0 + 3 2 2 + 3 2 3 + 3 2 4 + 5 7 7 + 5 7 8 + 5 7 9 + 5 7 10 + 5 7 11 +} + # https://sqlite.org/forum/forumpost/fc29fa4f14 # @@ -199,8 +232,4 @@ do_execsql_test 5.2 { ORDER BY a, c; } {3 1 2} - - - - finish_test -- 2.47.3