From ad674beccf7399d400a92f6e8f1268fda0c13466 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sat, 20 Jul 2024 01:19:18 +0000 Subject: [PATCH] Experimental implementation of LATERAL JOIN. FossilOrigin-Name: 3c045a96bc65713e5544af82b8547cdcdf93fa20f9b7b0851d77f8c090cc650b --- manifest | 30 +++++++------ manifest.uuid | 2 +- src/parse.y | 10 ++++- src/resolve.c | 29 +++++++++--- src/select.c | 7 +-- src/sqliteInt.h | 3 +- src/treeview.c | 3 ++ src/where.c | 11 ++--- test/joinL.test | 104 +++++++++++++++++++++++++++++++++++++++++++ tool/mkkeywordhash.c | 1 + 10 files changed, 170 insertions(+), 30 deletions(-) create mode 100644 test/joinL.test diff --git a/manifest b/manifest index 88ad236403..0fa6ca7065 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Minor\stypo\sfixes\sin\stest-script-interpreter.md. -D 2024-07-18T19:17:29.793 +C Experimental\simplementation\sof\sLATERAL\sJOIN. +D 2024-07-20T01:19:18.644 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -744,7 +744,7 @@ F src/os_win.c 6ff43bac175bd9ed79e7c0f96840b139f2f51d01689a638fd05128becf94908a F src/os_win.h 7b073010f1451abe501be30d12f6bc599824944a F src/pager.c b08600ebf0db90b6d1e9b8b6577c6fa3877cbe1a100bd0b2899e4c6e9adad4b3 F src/pager.h 4b1140d691860de0be1347474c51fee07d5420bd7f802d38cbab8ea4ab9f538a -F src/parse.y 2bd540b3b1e79017eb41fca2396633a75e7dd430c05383c61fe52c6f4e97c6d8 +F src/parse.y 4be218b2b28316145aae99695ccec0bd08d1b9f1088304a72e5e526bb9bbb083 F src/pcache.c 588cc3c5ccaaadde689ed35ce5c5c891a1f7b1f4d1f56f6cf0143b74d8ee6484 F src/pcache.h 1497ce1b823cf00094bb0cf3bac37b345937e6f910890c626b16512316d3abf5 F src/pcache1.c 49516ad7718a3626f28f710fa7448ef1fce3c07fd169acbb4817341950264319 @@ -753,14 +753,14 @@ F src/pragma.h e690a356c18e98414d2e870ea791c1be1545a714ba623719deb63f7f226d8bb7 F src/prepare.c d99931f45416652895e502328ca49fe782cfc4e1ebdcda13b3736d991ebf42ce F src/printf.c 8b250972305e14b365561be5117ed0fd364e4fd58968776df1ce64c6280b90f9 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c -F src/resolve.c 7e8d23ce7cdbfedf351a47e759f2722e8182ca10fd7580be43f4ce1f1a228145 +F src/resolve.c 8a82616721e6009aad4502d8c20ac003a08c5b83fb141784ccca75ce6467ec98 F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97 -F src/select.c 6a95a2bffa6c09584dea99db5a7ae10c813305c09c92920ffc54f6eae2ba399e +F src/select.c da8c817bc099e9e6d4169b03d1935bf8d17c062f7635cc90abfe29a764c873ae F src/shell.c.in b7d435c137eb323981adff814f172dbaabb9ba504fef17cb11d4681c1633ee13 F src/sqlite.h.in 6c884a87bbf8828562b49272025a1e66e3801a196a58b0bdec87edcd2c9c8fc1 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 3f046c04ea3595d6bfda99b781926b17e672fd6d27da2ba6d8d8fc39981dcb54 -F src/sqliteInt.h b77218c425891c7c90506c77fd2eb13bae03628d065b44fffeb37401cd955ac1 +F src/sqliteInt.h c5f0c47ecbd9ea1c3809f2f8f5c1082d4f4d590211a53e51761b21fd7522d129 F src/sqliteLimit.h 6878ab64bdeb8c24a1d762d45635e34b96da21132179023338c93f820eee6728 F src/status.c cb11f8589a6912af2da3bb1ec509a94dd8ef27df4d4c1a97e0bcf2309ece972b F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1 @@ -818,7 +818,7 @@ F src/test_window.c cdae419fdcea5bad6dcd9368c685abdad6deb59e9fc8b84b153de513d394 F src/test_wsd.c 41cadfd9d97fe8e3e4e44f61a4a8ccd6f7ca8fe9 F src/threads.c 4ae07fa022a3dc7c5beb373cf744a85d3c5c6c3c F src/tokenize.c 3f703cacdab728d7741e5a6ac242006d74fe1c2754d4f03ed889d7253259bd68 -F src/treeview.c e4f0c5ca871371d26ca6868554bd5b06b7bd9554023bbec834e26c2b45814b0c +F src/treeview.c 324f285fde4de481be4595b3d1c3e1dc8de08e160f4dd4f79750678d2faca794 F src/trigger.c 0858f75818ed1580332db274f1032bcc5effe567cb132df5c5be8b1d800ca97f F src/update.c 732404a04d1737ef14bb6ec6b84f74edf28b3c102a92ae46b4855438a710efe7 F src/upsert.c 2e60567a0e9e8520c18671b30712a88dc73534474304af94f32bb5f3ef65ac65 @@ -840,7 +840,7 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9 F src/wal.c 887fc4ca3f020ebb2e376f222069570834ac63bf50111ef0cbf3ae417048ed89 F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452 F src/walker.c 7c7ea0115345851c3da4e04e2e239a29983b61fb5b038b94eede6aba462640e2 -F src/where.c 70e12796956949658d6adb85f22d0760f0c05829f2ce882e89752eb28147baeb +F src/where.c e411ef973e520428a3cbbf8a348c7b37bc8f7f639ebdabd64f835861364be3ee F src/whereInt.h 002adc3aa2cc10733b9b27958fdbe893987cd989fab25a9853941c1f9b9b0a65 F src/wherecode.c c9cac0b0b8e809c5e7e79d7796918907fb685ad99be2aaa9737f9787aa47349c F src/whereexpr.c 7d0d34b42b9edfd8e8ca66beb3a6ef63fe211c001af54caf2ccbcd989b783290 @@ -1347,6 +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 42f319f9fc60a3dddb248a71f4d8ace80741772c8a8606f4ce878e96311822ee F test/journal1.test c7b768041b7f494471531e17abc2f4f5ebf9e5096984f43ed17c4eb80ba34497 F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4 F test/journal3.test 7c3cf23ffc77db06601c1fcfc9743de8441cb77db9d1aa931863d94f5ffa140e @@ -2116,7 +2117,7 @@ F tool/merge-test.tcl de76b62f2de2a92d4c1ca4f976bce0aea6899e0229e250479b229b2a19 F tool/mkautoconfamal.sh cbdcf993fa83dccbef7fb77b39cdeb31ef9f77d9d88c9e343b58d35ca3898a6a F tool/mkccode.tcl 86463e68ce9c15d3041610fedd285ce32a5cf7a58fc88b3202b8b76837650dbe x F tool/mkctimec.tcl 060e9785e9503bf51f8b1b11b542bdeef90fd0ceb0738154f6762acec0c61e5f x -F tool/mkkeywordhash.c b9faa0ae7e14e4dbbcd951cddd786bf46b8a65bb07b129ba8c0cfade723aaffd +F tool/mkkeywordhash.c 10f6a1eb691b17d4a87b0a9bf809420c5cfb806273d3e5101add1544b223f9d4 F tool/mkmsvcmin.tcl 8897d515ef7f94772322db95a3b6fce6c614d84fe0bdd06ba5a1c786351d5a1d F tool/mkopcodec.tcl 33d20791e191df43209b77d37f0ff0904620b28465cca6990cf8d60da61a07ef F tool/mkopcodeh.tcl 2b4e6967a670ef21bf53a164964c35c6163277d002a4c6f56fa231d68c88d023 @@ -2195,8 +2196,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 0c1cc4071edcd3e465779bbb17ea3ee6ddaa7c93c4fde55e5f3e07aa4b45783b -R aae1e75c8b917e7c82d70094db31bce4 -U stephan -Z 4b6c3f65da8d1d26380cf3c9e0c046bc +P bf54b26092ded2e6acc779acfb960364f05c665c7626c38ffae61caae5636184 +R fd0426e8bf5d734a1d48d710a5bc9a8a +T *branch * lateral-join +T *sym-lateral-join * +T -sym-trunk * +U drh +Z 0351f62d422552d2d5b55232d6520d93 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 0d74c4156a..55f341b9bf 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -bf54b26092ded2e6acc779acfb960364f05c665c7626c38ffae61caae5636184 +3c045a96bc65713e5544af82b8547cdcdf93fa20f9b7b0851d77f8c090cc650b diff --git a/src/parse.y b/src/parse.y index de8282e81f..963b52d423 100644 --- a/src/parse.y +++ b/src/parse.y @@ -253,7 +253,7 @@ columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,A,Y);} ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW CONFLICT DATABASE DEFERRED DESC DETACH DO EACH END EXCLUSIVE EXPLAIN FAIL FOR - IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN + IGNORE IMMEDIATE INITIALLY INSTEAD LATERAL LIKE_KW MATCH NO PLAN QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW ROWS ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT NULLS FIRST LAST @@ -732,6 +732,14 @@ seltablist(A) ::= stl_prefix(A) nm(Y) dbnm(D) LP exprlist(E) RP as(Z) on_using(N seltablist(A) ::= stl_prefix(A) LP select(S) RP as(Z) on_using(N). { A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,S,&N); } + seltablist(A) ::= stl_prefix(A) LATERAL LP select(S) RP as(Z) on_using(N). { + SrcList *pSrc; + pSrc = A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,S,&N); + if( pSrc && pSrc->nSrc>1 ){ + pSrc->a[pSrc->nSrc-1].fg.isLateral = 1; + pSrc->a[pSrc->nSrc-2].fg.jointype |= JT_LATERAL; + } + } seltablist(A) ::= stl_prefix(A) LP seltablist(F) RP as(Z) on_using(N). { if( A==0 && Z.n==0 && N.pOn==0 && N.pUsing==0 ){ A = F; diff --git a/src/resolve.c b/src/resolve.c index d5c1515a74..afc3ebb90a 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -1859,6 +1859,7 @@ static int resolveSelectStep(Walker *pWalker, Select *p){ nCompound = 0; pLeftmost = p; while( p ){ + int nSrc; assert( (p->selFlags & SF_Expanded)!=0 ); assert( (p->selFlags & SF_Resolved)==0 ); p->selFlags |= SF_Resolved; @@ -1890,15 +1891,32 @@ static int resolveSelectStep(Walker *pWalker, Select *p){ /* Recursively resolve names in all subqueries in the FROM clause */ if( pOuterNC ) pOuterNC->nNestedSelect++; - for(i=0; ipSrc->nSrc; i++){ + nSrc = p->pSrc->nSrc; + for(i=0; ipSrc->a[i]; assert( pItem->zName!=0 || pItem->pSelect!=0 );/* Test of tag-20240424-1*/ if( pItem->pSelect && (pItem->pSelect->selFlags & SF_Resolved)==0 ){ int nRef = pOuterNC ? pOuterNC->nRef : 0; const char *zSavedContext = pParse->zAuthContext; - if( pItem->zName ) pParse->zAuthContext = pItem->zName; - sqlite3ResolveSelectNames(pParse, pItem->pSelect, pOuterNC); + if( pItem->fg.isLateral && i>0 ){ + int nRef2 = sNC.nRef; + p->pSrc->nSrc = i; + sNC.pSrcList = p->pSrc; + sNC.pNext = pOuterNC; + sqlite3ResolveSelectNames(pParse, pItem->pSelect, &sNC); + p->pSrc->nSrc = nSrc; + if( sNC.nRef>nRef2 ){ + int kk; + pItem->fg.isCorrelated = 1; + for(kk=0; kkpSrc->a[kk].fg.jointype |= JT_LATERAL; + } + sNC.pSrcList = 0; + sNC.pNext = 0; + }else{ + sqlite3ResolveSelectNames(pParse, pItem->pSelect, pOuterNC); + } + pParse->zAuthContext = zSavedContext; if( pParse->nErr ) return WRC_Abort; assert( db->mallocFailed==0 ); @@ -1909,9 +1927,8 @@ static int resolveSelectStep(Walker *pWalker, Select *p){ ** but the innermost outer context object, as lookupName() increments ** the refcount on all contexts between the current one and the ** context containing the column when it resolves a name. */ - if( pOuterNC ){ - assert( pItem->fg.isCorrelated==0 && pOuterNC->nRef>=nRef ); - pItem->fg.isCorrelated = (pOuterNC->nRef>nRef); + if( pOuterNC && pOuterNC->nRef>nRef ){ + pItem->fg.isCorrelated = 1; } } } diff --git a/src/select.c b/src/select.c index 4b0b554295..4c3d807b0c 100644 --- a/src/select.c +++ b/src/select.c @@ -261,6 +261,7 @@ static Select *findRightmost(Select *p){ */ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){ int jointype = 0; + int bErr = 0; Token *apAll[3]; Token *p; /* 0123456789 123456789 123456789 123 */ @@ -293,13 +294,13 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){ } testcase( j==0 || j==1 || j==2 || j==3 || j==4 || j==5 || j==6 ); if( j>=ArraySize(aKeyword) ){ - jointype |= JT_ERROR; + bErr = 1; break; } } if( (jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) || - (jointype & JT_ERROR)!=0 || + bErr>0 || (jointype & (JT_OUTER|JT_LEFT|JT_RIGHT))==JT_OUTER ){ const char *zSp1 = " "; @@ -7273,7 +7274,7 @@ static int fromClauseTermCanBeCoroutine( if( pCteUse->eM10d==M10d_Yes ) return 0; /* (2a) */ if( pCteUse->nUse>=2 && pCteUse->eM10d!=M10d_No ) return 0; /* (2b) */ } - if( pTabList->a[0].fg.jointype & JT_LTORJ ) return 0; /* (3) */ + if( pTabList->a[0].fg.jointype & (JT_LTORJ|JT_LATERAL) ) return 0; /* (3) */ if( OptimizationDisabled(pParse->db, SQLITE_Coroutines) ) return 0; /* (4) */ if( isSelfJoinView(pTabList, pItem, i+1, pTabList->nSrc)!=0 ){ return 0; /* (5) */ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 6a92befe0f..cf6299b762 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3305,6 +3305,7 @@ struct SrcItem { unsigned isIndexedBy :1; /* True if there is an INDEXED BY clause */ unsigned isTabFunc :1; /* True if table-valued-function syntax */ unsigned isCorrelated :1; /* True if sub-query is correlated */ + unsigned isLateral :1; /* True if sub-query is LATERAL */ unsigned isMaterialized:1; /* This is a materialized view */ unsigned viaCoroutine :1; /* Implemented as a co-routine */ unsigned isRecursive :1; /* True for recursive reference in WITH */ @@ -3367,7 +3368,7 @@ struct SrcList { #define JT_OUTER 0x20 /* The "OUTER" keyword is present */ #define JT_LTORJ 0x40 /* One of the LEFT operands of a RIGHT JOIN ** Mnemonic: Left Table Of Right Join */ -#define JT_ERROR 0x80 /* unknown or unsupported join type */ +#define JT_LATERAL 0x80 /* A LATERAL join */ /* ** Flags appropriate for the wctrlFlags parameter of sqlite3WhereBegin() diff --git a/src/treeview.c b/src/treeview.c index 054265338e..9e0e5271c2 100644 --- a/src/treeview.c +++ b/src/treeview.c @@ -211,6 +211,9 @@ void sqlite3TreeViewSrcList(TreeView *pView, const SrcList *pSrc){ if( pItem->fg.jointype & JT_LTORJ ){ sqlite3_str_appendf(&x, " LTORJ"); } + if( pItem->fg.isLateral ){ + sqlite3_str_appendf(&x, " LATERAL"); + } if( pItem->fg.fromDDL ){ sqlite3_str_appendf(&x, " DDL"); } diff --git a/src/where.c b/src/where.c index ba4631c6c2..cdbec7b015 100644 --- a/src/where.c +++ b/src/where.c @@ -4772,12 +4772,13 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){ pBuilder->iPlanLimit += SQLITE_QUERY_PLANNER_LIMIT_INCR; pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor); if( bFirstPastRJ - || (pItem->fg.jointype & (JT_OUTER|JT_CROSS|JT_LTORJ))!=0 + || (pItem->fg.jointype & (JT_OUTER|JT_CROSS|JT_LTORJ|JT_LATERAL))!=0 ){ /* Add prerequisites to prevent reordering of FROM clause terms - ** across CROSS joins and outer joins. The bFirstPastRJ boolean - ** prevents the right operand of a RIGHT JOIN from being swapped with - ** other elements even further to the right. + ** across CROSS joins, outer joins, and lateral joins. + ** The bFirstPastRJ boolean prevents the right operand of a + ** RIGHT JOIN from being swapped with other elements even further + ** to the right. ** ** The JT_LTORJ case and the hasRightJoin flag work together to ** prevent FROM-clause terms from moving from the right side of @@ -4794,7 +4795,7 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){ if( IsVirtual(pItem->pTab) ){ SrcItem *p; for(p=&pItem[1]; pfg.jointype & (JT_OUTER|JT_CROSS)) ){ + if( mUnusable || (p->fg.jointype & (JT_OUTER|JT_CROSS|JT_LATERAL)) ){ mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor); } } diff --git a/test/joinL.test b/test/joinL.test new file mode 100644 index 0000000000..5d3366f989 --- /dev/null +++ b/test/joinL.test @@ -0,0 +1,104 @@ +# 2024-07-20 +# +# 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. +# +#*********************************************************************** +# +# Tests for LATERAL JOIN +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix joinL + +db null NULL +do_execsql_test 1.0 { + CREATE TABLE product(product_id INT, price REAL, product TEXT); + INSERT INTO product VALUES + (1, 6.76, 'apple'), + (2, 5.28, 'banana'), + (3, 28.28, 'cherry'), + (4, 13.54, 'dates'), + (5, 30.58, 'elderberry'), + (6, 26.57, 'figs'), + (7, 64.85, 'grapes'), + (8, 21.55, 'huckleberry'), + (9, 29.00, 'jakefruit'), + (10, 17.31, 'kiwi'); + CREATE TABLE wishlist(wishlist_id int, username text, desired_price real); + INSERT INTO wishlist VALUES + (1, 'alice', 45.0), + (2, 'bob', 30.0), + (3, 'cindy', 15.0), + (4, 'david', 4.0); + SELECT * + FROM wishlist AS w LEFT JOIN LATERAL ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x + ORDER BY wishlist_id, price DESC; +} { + 1 alice 45.0 5 30.58 elderberry + 1 alice 45.0 9 29.0 jakefruit + 1 alice 45.0 3 28.28 cherry + 2 bob 30.0 9 29.0 jakefruit + 2 bob 30.0 3 28.28 cherry + 2 bob 30.0 6 26.57 figs + 3 cindy 15.0 4 13.54 dates + 3 cindy 15.0 1 6.76 apple + 3 cindy 15.0 2 5.28 banana + 4 david 4.0 NULL NULL NULL +} +do_execsql_test 1.1 { + SELECT * + FROM wishlist AS w JOIN LATERAL ( + SELECT * FROM product AS p + WHERE p.price < w.desired_price + ORDER BY p.price DESC LIMIT 3 + ) AS x + ORDER BY wishlist_id, price DESC; +} { + 1 alice 45.0 5 30.58 elderberry + 1 alice 45.0 9 29.0 jakefruit + 1 alice 45.0 3 28.28 cherry + 2 bob 30.0 9 29.0 jakefruit + 2 bob 30.0 3 28.28 cherry + 2 bob 30.0 6 26.57 figs + 3 cindy 15.0 4 13.54 dates + 3 cindy 15.0 1 6.76 apple + 3 cindy 15.0 2 5.28 banana +} + +do_execsql_test 2.0 { + CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INT, created_at TEXT); + INSERT INTO orders VALUES + (1,1,'2024-07-20T01:35:03'), + (2,2,'2024-07-20T01:35:07'), + (3,3,'2024-07-20T01:35:10'), + (4,1,'2024-07-20T01:58:10'), + (5,3,'2024-07-20T01:58:17'), + (6,3,'2024-07-20T01:58:25'); + + SELECT user_id, first_order_time, next_order_time, id FROM + (SELECT user_id, min(created_at) AS first_order_time + FROM orders GROUP BY user_id) AS o1 + LEFT JOIN LATERAL + (SELECT id, created_at AS next_order_time + FROM orders + WHERE user_id = o1.user_id AND created_at > o1.first_order_time + ORDER BY created_at ASC LIMIT 1) AS o2 + ON true; +} { + 1 2024-07-20T01:35:03 2024-07-20T01:58:10 4 + 2 2024-07-20T01:35:07 NULL NULL + 3 2024-07-20T01:35:10 2024-07-20T01:58:17 5 +} + + +finish_test diff --git a/tool/mkkeywordhash.c b/tool/mkkeywordhash.c index 5386a36c40..c6f96e691b 100644 --- a/tool/mkkeywordhash.c +++ b/tool/mkkeywordhash.c @@ -251,6 +251,7 @@ static Keyword aKeywordTable[] = { { "JOIN", "TK_JOIN", ALWAYS, 5 }, { "KEY", "TK_KEY", ALWAYS, 1 }, { "LAST", "TK_LAST", ALWAYS, 4 }, + { "LATERAL", "TK_LATERAL", ALWAYS, 7 }, { "LEFT", "TK_JOIN_KW", ALWAYS, 5 }, { "LIKE", "TK_LIKE_KW", ALWAYS, 5 }, { "LIMIT", "TK_LIMIT", ALWAYS, 3 }, -- 2.47.3