[PR PATCH] sqlite: patch performance regression

[PR PATCH] sqlite: patch performance regression

[classabbyamp]

[-- Attachment #1: Type: text/plain, Size: 614 bytes --]

There is a new pull request by classabbyamp against master on the void-packages repository

https://github.com/classabbyamp/void-packages sqlite-patch
https://github.com/void-linux/void-packages/pull/43043

sqlite: patch performance regression
more info:
- https://sqlite.org/src/info/aa6bd6dff751223e
- https://lore.kernel.org/distributions/87wn35bod7.fsf@gentoo.org/T/

<!-- Uncomment relevant sections and delete options which are not applicable -->

#### Testing the changes
- I tested the changes in this PR: **NO**



A patch file from https://github.com/void-linux/void-packages/pull/43043.patch is attached

[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: github-pr-sqlite-patch-43043.patch --]
[-- Type: text/x-diff, Size: 5148 bytes --]

From 36d70c488e91b594eb71985e81abb743d6f585e6 Mon Sep 17 00:00:00 2001
From: classabbyamp <void@placeviolette.net>
Date: Mon, 27 Mar 2023 02:18:50 -0400
Subject: [PATCH] sqlite: patch performance regression

more info:
https://sqlite.org/src/info/aa6bd6dff751223e
https://lore.kernel.org/distributions/87wn35bod7.fsf@gentoo.org/T/
---
 .../sqlite/patches/3.41-perf-regression.patch | 105 ++++++++++++++++++
 srcpkgs/sqlite/template                       |   2 +-
 2 files changed, 106 insertions(+), 1 deletion(-)
 create mode 100644 srcpkgs/sqlite/patches/3.41-perf-regression.patch

diff --git a/srcpkgs/sqlite/patches/3.41-perf-regression.patch b/srcpkgs/sqlite/patches/3.41-perf-regression.patch
new file mode 100644
index 000000000000..0894714c70ce
--- /dev/null
+++ b/srcpkgs/sqlite/patches/3.41-perf-regression.patch
@@ -0,0 +1,105 @@
+source: https://sqlite.org/src/info/aa6bd6dff751223e
+can remove with 3.42.0
+
+Index: src/select.c
+==================================================================
+--- src/select.c
++++ src/select.c
+@@ -4236,12 +4236,11 @@
+ **        (17f) the subquery must not be the RHS of a LEFT JOIN.
+ **        (17g) either the subquery is the first element of the outer
+ **              query or there are no RIGHT or FULL JOINs in any arm
+ **              of the subquery.  (This is a duplicate of condition (27b).)
+ **        (17h) The corresponding result set expressions in all arms of the
+-**              compound must have the same affinity. (See restriction (9)
+-**              on the push-down optimization.)
++**              compound must have the same affinity.
+ **
+ **        The parent and sub-query may contain WHERE clauses. Subject to
+ **        rules (11), (13) and (14), they may also contain ORDER BY,
+ **        LIMIT and OFFSET clauses.  The subquery cannot use any compound
+ **        operator other than UNION ALL because all the other compound
+@@ -5105,14 +5104,10 @@
+ **
+ **   (8) If the subquery is a compound that uses UNION, INTERSECT,
+ **       or EXCEPT, then all of the result set columns for all arms of
+ **       the compound must use the BINARY collating sequence.
+ **
+-**   (9) If the subquery is a compound, then all arms of the compound must
+-**       have the same affinity.  (This is the same as restriction (17h)
+-**       for query flattening.)
+-**       
+ **
+ ** Return 0 if no changes are made and non-zero if one or more WHERE clause
+ ** terms are duplicated into the subquery.
+ */
+ static int pushDownWhereTerms(
+@@ -5139,13 +5134,10 @@
+       }
+ #ifndef SQLITE_OMIT_WINDOWFUNC
+       if( pSel->pWin ) return 0;    /* restriction (6b) */
+ #endif
+     }
+-    if( compoundHasDifferentAffinities(pSubq) ){
+-      return 0;  /* restriction (9) */
+-    }
+     if( notUnionAll ){
+       /* If any of the compound arms are connected using UNION, INTERSECT,
+       ** or EXCEPT, then we must ensure that none of the columns use a
+       ** non-BINARY collating sequence. */
+       for(pSel=pSubq; pSel; pSel=pSel->pPrior){
+
+Index: test/pushdown.test
+==================================================================
+--- test/pushdown.test
++++ test/pushdown.test
+@@ -120,7 +120,47 @@
+     SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1
+   ) WHERE a=2 AND b=0 AND cd=0;
+ } {
+   2 0     0
+ }
+-  
++
++# 2023-02-22 https://sqlite.org/forum/forumpost/bcc4375032
++# Performance regression caused by check-in [1ad41840c5e0fa70] from 2022-11-25.
++# That check-in added a new restriction on push-down.  The new restriction is
++# no longer necessary after check-in [27655c9353620aa5] from 2022-12-14.
++#
++do_execsql_test 3.5 {
++  DROP TABLE IF EXISTS t1;
++  CREATE TABLE t1(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
++  INSERT INTO t1(a,b,c) VALUES
++    (1,100,'abc'),
++    (2,200,'def'),
++    (3,300,'abc');
++  DROP TABLE IF EXISTS t2;
++  CREATE TABLE t2(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
++  INSERT INTO t2(a,b,c) VALUES
++    (1,110,'efg'),
++    (2,200,'hij'),
++    (3,330,'klm');
++  CREATE VIEW v3 AS
++    SELECT a, b, c FROM t1
++    UNION ALL
++    SELECT a, b, 'xyz' FROM t2;
++  SELECT * FROM v3 WHERE a=2 AND b=200;
++} {2 200 def 2 200 xyz}
++do_eqp_test 3.6 {
++  SELECT * FROM v3 WHERE a=2 AND b=200;
++} {
++  QUERY PLAN
++  |--CO-ROUTINE v3
++  |  `--COMPOUND QUERY
++  |     |--LEFT-MOST SUBQUERY
++  |     |  `--SEARCH t1 USING PRIMARY KEY (a=? AND b=?)
++  |     `--UNION ALL
++  |        `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?)
++  `--SCAN v3
++}
++#                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
++# We want both arms of the compound subquery to use the
++# primary key.
++
+ finish_test
+
diff --git a/srcpkgs/sqlite/template b/srcpkgs/sqlite/template
index d63c68fc0f63..14d03054c9e3 100644
--- a/srcpkgs/sqlite/template
+++ b/srcpkgs/sqlite/template
@@ -1,7 +1,7 @@
 # Template file for 'sqlite'
 pkgname=sqlite
 version=3.41.2
-revision=1
+revision=2
 _amalgamationver=$(printf "%d%02d%02d00\n" ${version//./ })
 build_style=gnu-configure
 configure_args="--enable-threadsafe --enable-dynamic-extensions --enable-fts5"

Re: [PR PATCH] [Updated] sqlite: patch performance regression

[classabbyamp]

[-- Attachment #1: Type: text/plain, Size: 619 bytes --]

There is an updated pull request by classabbyamp against master on the void-packages repository

https://github.com/classabbyamp/void-packages sqlite-patch
https://github.com/void-linux/void-packages/pull/43043

sqlite: patch performance regression
more info:
- https://sqlite.org/src/info/aa6bd6dff751223e
- https://lore.kernel.org/distributions/87wn35bod7.fsf@gentoo.org/T/

<!-- Uncomment relevant sections and delete options which are not applicable -->

#### Testing the changes
- I tested the changes in this PR: **NO**



A patch file from https://github.com/void-linux/void-packages/pull/43043.patch is attached

[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: github-pr-sqlite-patch-43043.patch --]
[-- Type: text/x-diff, Size: 1921 bytes --]

From 60ca9a71d2d1507a7a98830707d788d012d10647 Mon Sep 17 00:00:00 2001
From: classabbyamp <void@placeviolette.net>
Date: Mon, 27 Mar 2023 02:18:50 -0400
Subject: [PATCH] sqlite: patch performance regression

more info:
https://sqlite.org/src/info/aa6bd6dff751223e
https://lore.kernel.org/distributions/87wn35bod7.fsf@gentoo.org/T/
---
 .../sqlite/patches/3.41-perf-regression.patch | 19 +++++++++++++++++++
 srcpkgs/sqlite/template                       |  2 +-
 2 files changed, 20 insertions(+), 1 deletion(-)
 create mode 100644 srcpkgs/sqlite/patches/3.41-perf-regression.patch

diff --git a/srcpkgs/sqlite/patches/3.41-perf-regression.patch b/srcpkgs/sqlite/patches/3.41-perf-regression.patch
new file mode 100644
index 000000000000..d3e3e20a4c92
--- /dev/null
+++ b/srcpkgs/sqlite/patches/3.41-perf-regression.patch
@@ -0,0 +1,19 @@
+Patch-Source: https://sqlite.org/src/info/aa6bd6dff751223e
+https://sqlite.org/forum/forumpost/3a180ba0d4
+
+Can be removed in 3.42.x
+
+diff --git a/sqlite3.c b/sqlite3.c
+index 947a154..bf9ce16 100644
+--- a/sqlite3.c
++++ b/sqlite3.c
+@@ -143420,9 +143420,6 @@ static int pushDownWhereTerms(
+       if( pSel->pWin ) return 0;    /* restriction (6b) */
+ #endif
+     }
+-    if( compoundHasDifferentAffinities(pSubq) ){
+-      return 0;  /* restriction (9) */
+-    }
+     if( notUnionAll ){
+       /* If any of the compound arms are connected using UNION, INTERSECT,
+       ** or EXCEPT, then we must ensure that none of the columns use a
diff --git a/srcpkgs/sqlite/template b/srcpkgs/sqlite/template
index d63c68fc0f63..14d03054c9e3 100644
--- a/srcpkgs/sqlite/template
+++ b/srcpkgs/sqlite/template
@@ -1,7 +1,7 @@
 # Template file for 'sqlite'
 pkgname=sqlite
 version=3.41.2
-revision=1
+revision=2
 _amalgamationver=$(printf "%d%02d%02d00\n" ${version//./ })
 build_style=gnu-configure
 configure_args="--enable-threadsafe --enable-dynamic-extensions --enable-fts5"

Re: sqlite: patch performance regression

[mhmdanas]

[-- Attachment #1: Type: text/plain, Size: 181 bytes --]

New comment by mhmdanas on void-packages repository

https://github.com/void-linux/void-packages/pull/43043#issuecomment-1487646476

Comment:
Tested this, didn't notice any issues.

Re: [PR PATCH] [Merged]: sqlite: patch performance regression

[classabbyamp]

[-- Attachment #1: Type: text/plain, Size: 448 bytes --]

There's a merged pull request on the void-packages repository

sqlite: patch performance regression
https://github.com/void-linux/void-packages/pull/43043

Description:
more info:
- https://sqlite.org/src/info/aa6bd6dff751223e
- https://lore.kernel.org/distributions/87wn35bod7.fsf@gentoo.org/T/

<!-- Uncomment relevant sections and delete options which are not applicable -->

#### Testing the changes
- I tested the changes in this PR: **NO**