From 781b7ac3f6c61eb756740549ab10a14322210ad7 Mon Sep 17 00:00:00 2001 From: dan Date: Fri, 22 Mar 2019 13:56:49 +0000 Subject: [PATCH] Fix a problem with a window function in a correlated sub-query where at least one reference to the outer SELECT appears in a FROM clause sub-select. FossilOrigin-Name: 9aca86f98388ff66b2bccb3fbfc486a3931398d2a8da139f905826ae2cdbb205 --- manifest | 14 ++++---- manifest.uuid | 2 +- src/resolve.c | 2 +- test/window1.test | 85 ++++++++++++++++++++++++++++++++--------------- 4 files changed, 68 insertions(+), 35 deletions(-) diff --git a/manifest b/manifest index 2731ced2e3..ce66d38d4a 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Remove\sassert()\sstatements\sbased\son\sthe\scounter-factual\sproposition\sthat\s0\sis\snot\sa\svalid\scursor\snumber. -D 2019-03-21T13:51:09.569 +C Fix\sa\sproblem\swith\sa\swindow\sfunction\sin\sa\scorrelated\ssub-query\swhere\sat\sleast\sone\sreference\sto\sthe\souter\sSELECT\sappears\sin\sa\sFROM\sclause\ssub-select. +D 2019-03-22T13:56:49.356 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -514,7 +514,7 @@ F src/pragma.h a776bb9c915207e9d1117b5754743ddf1bf6a39cc092a4a44e74e6cb5fab1177 F src/prepare.c 78027c6231fbb19ca186a5f5f0c0a1375d9c2cec0655273f9bd90d9ff74a34b3 F src/printf.c 93a3d539019264683a444bc043c875e9a6cca43fe935ae7bf6cfff0af3bba118 F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384 -F src/resolve.c 09419ad5c432190b69be7c0c326e03abb548a97c2c50675b81b459e1b382d1d2 +F src/resolve.c f20cb5b953bd927e8c6b6e01662cce066a632b5d3a21d2938b93de0825aef5d5 F src/rowset.c d977b011993aaea002cab3e0bb2ce50cf346000dff94e944d547b989f4b1fe93 F src/select.c 9263f5c30dd44c7ac2eb29f40a7ec64322a96885b71c00de6bc30b756c2e1c49 F src/shell.c.in 01c0cc01391d00d247fdf640052d38c267fc16d975bc4f3154a02277c232dbeb @@ -1677,7 +1677,7 @@ F test/win32heap.test 10fd891266bd00af68671e702317726375e5407561d859be1aa04696f2 F test/win32lock.test fbf107c91d8f5512be5a5b87c4c42ab9fdd54972 F test/win32longpath.test 169c75a3b2e43481f4a62122510210c67b08f26d F test/win32nolock.test ac4f08811a562e45a5755e661f45ca85892bdbbc -F test/window1.test c4ff89d6b2c6071967bee1df9907d2abedf980a55c8f7975e942e9627f34be90 +F test/window1.test 7484bf9a2ef2d845a50c9aff866efb8cd9d244b5916a9e97fb384e05f33d242b F test/window2.tcl 9bfa842d8a62b0d36dc8c1b5972206393c43847433c6d75940b87fec93ce3143 F test/window2.test 8e6d2a1b9f54dfebee1cde961c8590cd87b4db45c50f44947a211e1b63c2a05e F test/window3.tcl acea6e86a4324a210fd608d06741010ca83ded9fde438341cb978c49928faf03 @@ -1813,7 +1813,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P ec7e224f50271a69a28074270b01328ec0ee38751fcb93b2c598d8be2b77a95d -R ea4b126233082b141671d798b323e6ed +P c7b336181aac6785a515f275c0f50ad4bf2dee20abde959b56d968a7fdce3e5b +R 27c6db300027eaa6c67b9af679eb88d7 U dan -Z 9fc537eb224e6166ecbe31c6a413fd61 +Z 618684b43d76a076130756009b0f94fe diff --git a/manifest.uuid b/manifest.uuid index 6ed0434b6f..5b76473da8 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -c7b336181aac6785a515f275c0f50ad4bf2dee20abde959b56d968a7fdce3e5b \ No newline at end of file +9aca86f98388ff66b2bccb3fbfc486a3931398d2a8da139f905826ae2cdbb205 \ No newline at end of file diff --git a/src/resolve.c b/src/resolve.c index fd2cf539a9..2644e069fa 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -1424,7 +1424,7 @@ static int resolveSelectStep(Walker *pWalker, Select *p){ */ for(i=0; ipSrc->nSrc; i++){ struct SrcList_item *pItem = &p->pSrc->a[i]; - if( pItem->pSelect ){ + if( pItem->pSelect && (pItem->pSelect->selFlags & SF_Resolved)==0 ){ NameContext *pNC; /* Used to iterate name contexts */ int nRef = 0; /* Refcount for pOuterNC and outer contexts */ const char *zSavedContext = pParse->zAuthContext; diff --git a/test/window1.test b/test/window1.test index f80560883f..5eea9de8a2 100644 --- a/test/window1.test +++ b/test/window1.test @@ -720,32 +720,6 @@ do_execsql_test 17.3 { ORDER BY 10+sum(a) OVER (ORDER BY a) DESC; } {16 13 11} -#------------------------------------------------------------------------- -# -reset_db -do_execsql_test 18.0 { - CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ); - CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY ); - CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY ); - - INSERT INTO t1 VALUES(1), (3), (5); - INSERT INTO t2 VALUES (3), (5); - INSERT INTO t3 VALUES(10), (11), (12); -} - -do_execsql_test 18.1 { - SELECT t1.* FROM t1, t2 WHERE - t1_id=t2_id AND t1_id IN ( - SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3 - ) -} - -do_execsql_test 18.2 { - SELECT t1.* FROM t1, t2 WHERE - t1_id=t2_id AND t1_id IN ( - SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3 - ) -} {3} #------------------------------------------------------------------------- # Test error cases from chaining window definitions. @@ -1035,6 +1009,65 @@ do_execsql_test 24.2 { SELECT lead(44) OVER () } {{}} +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 25.0 { + CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ); + CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY ); + CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY ); + + INSERT INTO t1 VALUES(1), (3), (5); + INSERT INTO t2 VALUES (3), (5); + INSERT INTO t3 VALUES(10), (11), (12); +} + +do_execsql_test 25.1 { + SELECT t1.* FROM t1, t2 WHERE + t1_id=t2_id AND t1_id IN ( + SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3 + ) +} + +do_execsql_test 25.2 { + SELECT t1.* FROM t1, t2 WHERE + t1_id=t2_id AND t1_id IN ( + SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3 + ) +} {3} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 26.0 { + CREATE TABLE t1(x); + CREATE TABLE t2(c); +} + +do_execsql_test 26.1 { + SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2 +} {} + +do_execsql_test 26.2 { + INSERT INTO t1 VALUES(1), (2), (3), (4); + INSERT INTO t2 VALUES(2), (6), (8), (4); + SELECT c, c IN ( + SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) + ) FROM t2 +} {2 1 6 0 8 0 4 1} + +do_execsql_test 26.3 { + DELETE FROM t1; + DELETE FROM t2; + + INSERT INTO t2 VALUES(1), (2), (3), (4); + INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4); + + SELECT c, c IN ( + SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c ) + ) FROM t2 +} {1 1 2 0 3 1 4 0} + + finish_test -- 2.39.5