From 9175b18226948b05c4a6ed9b8a1c3225d3547544 Mon Sep 17 00:00:00 2001 From: drh <> Date: Fri, 14 Jun 2024 23:13:54 +0000 Subject: [PATCH] Do not omit the ORDER BY clause from a recursive CTE just because the query that contains the CTE also contains an ORDER BY clause. Plus other changes imported from the recursive-cte-orderby-fix branch. FossilOrigin-Name: 13242289c5d412b706f50fc7e1553032ea3a52d41a3e34e155432adaf0551481 --- manifest | 21 ++++++++++++--------- manifest.uuid | 2 +- src/select.c | 5 ++++- src/treeview.c | 12 +++++++++--- src/where.c | 2 +- 5 files changed, 27 insertions(+), 15 deletions(-) diff --git a/manifest b/manifest index 55eed392ac..e1de139027 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Remove\ssome\sdead\sJS\scode.\sImprove\sthe\sexception-to-C-result-code\sconversion\sto\saccount\sfor\sthe\scase\sof\sa\sfile\sdisappearing\swhile\sthe\sOPFS\sVFS\sis\swaiting\sto\sacquire\sa\slock\son\sit. -D 2024-06-12T22:15:25.519 +C Do\snot\somit\sthe\sORDER\sBY\sclause\sfrom\sa\srecursive\sCTE\sjust\sbecause\sthe\s\nquery\sthat\scontains\sthe\sCTE\salso\scontains\san\sORDER\sBY\sclause.\s\sPlus\nother\schanges\simported\sfrom\sthe\srecursive-cte-orderby-fix\sbranch. +D 2024-06-14T23:13:54.369 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -755,7 +755,7 @@ F src/printf.c 8b250972305e14b365561be5117ed0fd364e4fd58968776df1ce64c6280b90f9 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c F src/resolve.c 7e8d23ce7cdbfedf351a47e759f2722e8182ca10fd7580be43f4ce1f1a228145 F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97 -F src/select.c ea0b75fce45e1f2c22f50ed2b6e2ddd7f66640948d0fc79a397917b4236a74af +F src/select.c d26ac0a13b4154cbd71a9a57ca024350cd73f0f6fcf0ca0cbb4537e2d5b3e257 F src/shell.c.in ad27d1d990e9e5fb7ae8fc38a717e91f55233714f59723e5618baf4a2a3d2b65 F src/sqlite.h.in 6c884a87bbf8828562b49272025a1e66e3801a196a58b0bdec87edcd2c9c8fc1 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 @@ -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 4b0e9131c33a08905bb56a424cd07d6146497f3277301962b4d3d997c2fd7d5f +F src/treeview.c e4f0c5ca871371d26ca6868554bd5b06b7bd9554023bbec834e26c2b45814b0c 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 94c2ae6c3ef6173492c9b400635fca8f04316cf6bea8face1d425753aba31b85 +F src/where.c 70e12796956949658d6adb85f22d0760f0c05829f2ce882e89752eb28147baeb F src/whereInt.h 002adc3aa2cc10733b9b27958fdbe893987cd989fab25a9853941c1f9b9b0a65 F src/wherecode.c c9cac0b0b8e809c5e7e79d7796918907fb685ad99be2aaa9737f9787aa47349c F src/whereexpr.c 7d0d34b42b9edfd8e8ca66beb3a6ef63fe211c001af54caf2ccbcd989b783290 @@ -2195,8 +2195,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 f253cab3359270045a3ae9f5e9eadc1cfc8844181db127165bfdf49d5f99efb1 -R 63f255ea2e5c09516862fb8ffb235ee5 -U stephan -Z 1022f5b3521399d57e698263f497769f +P 5bd9fd5f61e7cd1ed3b44c5cabc759c154c98eb40ad10d29e61f142aaa062ddf +Q +5e700f2cc22bafa51928084454b374858eee3e2b00b58454cb97948d1691d276 +Q +ca063802332eba9cb97d5e52c5889403bba7b57e421695929a49c1d381bdcb44 +Q +e87966cf63af78a8cc0188d9476a2003a0be1ca1be2b305af97add0428ace2c7 +R e25beacc80993e6730d8a9b60a6a3784 +U drh +Z 4bd7377f01b30167b6d338c1a711954b # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index f49af7d0df..e01bbca1a4 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -5bd9fd5f61e7cd1ed3b44c5cabc759c154c98eb40ad10d29e61f142aaa062ddf \ No newline at end of file +13242289c5d412b706f50fc7e1553032ea3a52d41a3e34e155432adaf0551481 \ No newline at end of file diff --git a/src/select.c b/src/select.c index 9a0f2e40f5..3bc5858f62 100644 --- a/src/select.c +++ b/src/select.c @@ -7535,13 +7535,16 @@ int sqlite3Select( ** (a) The outer query has a different ORDER BY clause ** (b) The subquery is part of a join ** See forum post 062d576715d277c8 + ** (6) The subquery is not a recursive CTE. ORDER BY has a different + ** meaning for recursive CTEs and this optimization does not + ** apply. ** ** Also retain the ORDER BY if the OmitOrderBy optimization is disabled. */ if( pSub->pOrderBy!=0 && (p->pOrderBy!=0 || pTabList->nSrc>1) /* Condition (5) */ && pSub->pLimit==0 /* Condition (1) */ - && (pSub->selFlags & SF_OrderByReqd)==0 /* Condition (2) */ + && (pSub->selFlags & (SF_OrderByReqd|SF_Recursive))==0 /* (2) and (6) */ && (p->selFlags & SF_OrderByReqd)==0 /* Condition (3) and (4) */ && OptimizationEnabled(db, SQLITE_OmitOrderBy) ){ diff --git a/src/treeview.c b/src/treeview.c index 4dcc130cde..054265338e 100644 --- a/src/treeview.c +++ b/src/treeview.c @@ -901,9 +901,10 @@ void sqlite3TreeViewBareExprList( sqlite3TreeViewLine(pView, "%s", zLabel); for(i=0; inExpr; i++){ int j = pList->a[i].u.x.iOrderByCol; + u8 sortFlags = pList->a[i].fg.sortFlags; char *zName = pList->a[i].zEName; int moreToFollow = inExpr - 1; - if( j || zName ){ + if( j || zName || sortFlags ){ sqlite3TreeViewPush(&pView, moreToFollow); moreToFollow = 0; sqlite3TreeViewLine(pView, 0); @@ -924,13 +925,18 @@ void sqlite3TreeViewBareExprList( } } if( j ){ - fprintf(stdout, "iOrderByCol=%d", j); + fprintf(stdout, "iOrderByCol=%d ", j); + } + if( sortFlags & KEYINFO_ORDER_DESC ){ + fprintf(stdout, "DESC "); + }else if( sortFlags & KEYINFO_ORDER_BIGNULL ){ + fprintf(stdout, "NULLS-LAST"); } fprintf(stdout, "\n"); fflush(stdout); } sqlite3TreeViewExpr(pView, pList->a[i].pExpr, moreToFollow); - if( j || zName ){ + if( j || zName || sortFlags ){ sqlite3TreeViewPop(&pView); } } diff --git a/src/where.c b/src/where.c index 4b9ee2c43b..ba4631c6c2 100644 --- a/src/where.c +++ b/src/where.c @@ -4980,7 +4980,7 @@ static i8 wherePathSatisfiesOrderBy( assert( pIndex->aiColumn[nColumn-1]==XN_ROWID || !HasRowid(pIndex->pTable)); /* All relevant terms of the index must also be non-NULL in order - ** for isOrderDistinct to be true. So the isOrderDistint value + ** for isOrderDistinct to be true. So the isOrderDistinct value ** computed here might be a false positive. Corrections will be ** made at tag-20210426-1 below */ isOrderDistinct = IsUniqueIndex(pIndex) -- 2.47.2