Build verified: 1.4.4-1 source
/root/ColumnStore/buildColumnstoreFromGithubSource/server
commit aaf2a53452447b0223866db16e88d52448986ea3
Author: Monty <monty@mariadb.org>
Date: Thu Aug 8 23:04:05 2019 +0300
MENT-401: Include Aria and S3 index length limit increase in ES 10.4
Cherry-picked from:
98ea611940fd492fc5f883625f2afcbbab312795
MDEV-20279 Increase Aria index length limit
/root/ColumnStore/buildColumnstoreFromGithubSource/server/engine
commit 0574127148e4c0a1cd9a341c7b9e8ffc982d0d24
Merge: ca3e2d7 cd7372f
Author: David.Hall <david.hall@mariadb.com>
Date: Mon Mar 23 13:43:27 2020 -0500
Merge pull request #1099 from pleblanc1976/mcol-2022-1.4
Mcol 2022 1.4
Verified that the reported issue no longer an issue. I ran both disk-run and cached-run and got similar results.
MariaDB [tpch10]> select c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice, sum(l.l_quantity) from customer c, orders o, lineitem l where o.o_orderkey in( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 313 ) and c.c_custkey = o.o_custkey and o.o_orderkey = l.l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice limit 100;
-------------------------------------------------------------------------------+
c_name |
c_custkey |
o_orderkey |
o_orderdate |
o_totalprice |
sum(l.l_quantity) |
-------------------------------------------------------------------------------+
Customer#000065491 |
65491 |
7549735 |
1994-06-30 |
424178.05 |
315.00 |
Customer#001172513 |
1172513 |
36667107 |
1997-06-06 |
550142.18 |
322.00 |
Customer#000778949 |
778949 |
40876003 |
1992-05-10 |
481422.24 |
314.00 |
Customer#001247930 |
1247930 |
59138305 |
1993-10-31 |
509372.97 |
321.00 |
.
.
Customer#000480857 |
480857 |
12809282 |
1994-05-21 |
417929.65 |
314.00 |
Customer#000287270 |
287270 |
37531878 |
1997-03-20 |
512044.58 |
315.00 |
Customer#001185599 |
1185599 |
54188961 |
1995-06-17 |
407873.05 |
317.00 |
-------------------------------------------------------------------------------+
99 rows in set (57.456 sec)
MariaDB [tpch10]> select * from (select c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice, sum(l.l_quantity) from customer c, orders o, lineitem l where o.o_orderkey in( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 313 ) and c.c_custkey = o.o_custkey and o.o_orderkey = l.l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice limit 100) dummytable;
-------------------------------------------------------------------------------+
c_name |
c_custkey |
o_orderkey |
o_orderdate |
o_totalprice |
sum(l.l_quantity) |
-------------------------------------------------------------------------------+
Customer#000065491 |
65491 |
7549735 |
1994-06-30 |
424178.05 |
315.00 |
Customer#000347839 |
347839 |
53623108 |
1995-05-07 |
481251.56 |
316.00 |
Customer#001247930 |
1247930 |
59138305 |
1993-10-31 |
509372.97 |
321.00 |
.
.
Customer#000126200 |
126200 |
23861472 |
1997-10-30 |
472802.59 |
314.00 |
Customer#001288183 |
1288183 |
48943904 |
1996-07-22 |
398081.59 |
325.00 |
Customer#000480857 |
480857 |
12809282 |
1994-05-21 |
417929.65 |
314.00 |
-------------------------------------------------------------------------------+
99 rows in set (55.819 sec)
My tests showed that regression has been fixed with
MCOL-3747. Moreover the query contains the same pattern IN+uncorrelated subquery.