stonedb
stonedb copied to clipboard
stoneatom
fix(tianmu): Fix errors in the Q4 (exists subquery) and Q16 query result (#330, #331)
Q4 uses the EXISTS subquery and previously optimized the EXISTS scenario using a parallel Hash Join, but an exception was found in the result set. Roll back the EXISTS process to a NEstd loop, and the query result is restored. This time in advance to ensure the correctness. On the basis of ensuring correctness, the optimization of performance is considered.
Q4
Mysql/Innodb query result
mysql> select
-> o_orderpriority,
-> count(*) as order_count
-> from
-> orders
-> where
-> o_orderdate >= date '1993-07-01'
-> and o_orderdate < date '1993-07-01' + interval '3' month
-> and exists (
-> select
-> *
-> from
-> lineitem
-> where
-> l_orderkey = o_orderkey
-> and l_commitdate < l_receiptdate
-> )
-> group by
-> o_orderpriority
-> order by
-> o_orderpriority
-> limit 100;
+-----------------+-------------+
| o_orderpriority | order_count |
+-----------------+-------------+
| 1-URGENT | 57865 |
| 2-HIGH | 57520 |
| 3-MEDIUM | 57514 |
| 4-NOT SPECIFIED | 57900 |
| 5-LOW | 57981 |
+-----------------+-------------+
5 rows in set (32.88 sec)
Query results before optimization
Stonedb Current query results:
mysql> select
-> o_orderpriority,
-> count(*) as order_count
-> from
-> orders
-> where
-> o_orderdate >= date '1993-07-01'
-> and o_orderdate < date '1993-07-01' + interval '3' month
-> and exists (
-> select
-> *
-> from
-> lineitem
-> where
-> l_orderkey = o_orderkey
-> and l_commitdate < l_receiptdate
-> )
-> group by
-> o_orderpriority
-> order by
-> o_orderpriority
-> limit 100;
+-----------------+-------------+
| o_orderpriority | order_count |
+-----------------+-------------+
| 1-URGENT | 63225 |
| 2-HIGH | 62671 |
| 3-MEDIUM | 62736 |
| 4-NOT SPECIFIED | 63166 |
| 5-LOW | 63167 |
+-----------------+-------------+
5 rows in set (2 min 58.34 sec)
Q16
mysql/innodb query result
mysql> select
-> p_brand,
-> p_type,
-> p_size,
-> count(distinct ps_suppkey) as supplier_cnt
-> from
-> partsupp,
-> part
-> where
-> p_partkey = ps_partkey
-> and p_brand <> 'Brand#45'
-> and p_type not like 'MEDIUM POLISHED%'
-> and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
-> and ps_suppkey not in (
-> select
-> s_suppkey
-> from
-> supplier
-> where
-> s_comment like '%Customer%Complaints%'
-> )
-> group by
-> p_brand,
-> p_type,
-> p_size
-> order by
-> supplier_cnt desc,
-> p_brand,
-> p_type,
-> p_size
-> limit 10;
+----------+--------------------------+--------+--------------+
| p_brand | p_type | p_size | supplier_cnt |
+----------+--------------------------+--------+--------------+
| Brand#44 | STANDARD PLATED TIN | 9 | 120 |
| Brand#12 | STANDARD POLISHED COPPER | 14 | 100 |
| Brand#11 | LARGE BRUSHED STEEL | 36 | 96 |
| Brand#23 | PROMO BURNISHED STEEL | 14 | 96 |
| Brand#34 | MEDIUM BRUSHED STEEL | 23 | 96 |
| Brand#53 | PROMO BURNISHED BRASS | 36 | 96 |
| Brand#54 | STANDARD BRUSHED COPPER | 19 | 96 |
| Brand#32 | LARGE POLISHED COPPER | 14 | 95 |
| Brand#43 | LARGE PLATED COPPER | 19 | 95 |
| Brand#11 | SMALL BRUSHED STEEL | 9 | 92 |
+----------+--------------------------+--------+--------------+
10 rows in set (26.00 sec)
stonedb query result
mysql> select
-> p_brand,
-> p_type,
-> p_size,
-> count(distinct ps_suppkey) as supplier_cnt
-> from
-> partsupp,
-> part
-> where
-> p_partkey = ps_partkey
-> and p_brand <> 'Brand#45'
-> and p_type not like 'MEDIUM POLISHED%'
-> and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
-> and ps_suppkey not in (
-> select
-> s_suppkey
-> from
-> supplier
-> where
-> s_comment like '%Customer%Complaints%'
-> )
-> group by
-> p_brand,
-> p_type,
-> p_size
-> order by
-> supplier_cnt desc,
-> p_brand,
-> p_type,
-> p_size
-> limit 10;
+----------+--------------------------+--------+--------------+
| p_brand | p_type | p_size | supplier_cnt |
+----------+--------------------------+--------+--------------+
| Brand#44 | STANDARD PLATED TIN | 9 | 120 |
| Brand#12 | STANDARD POLISHED COPPER | 14 | 100 |
| Brand#11 | LARGE BRUSHED STEEL | 36 | 96 |
| Brand#23 | PROMO BURNISHED STEEL | 14 | 96 |
| Brand#34 | MEDIUM BRUSHED STEEL | 23 | 96 |
| Brand#53 | PROMO BURNISHED BRASS | 36 | 96 |
| Brand#54 | STANDARD BRUSHED COPPER | 19 | 96 |
| Brand#32 | LARGE POLISHED COPPER | 14 | 95 |
| Brand#43 | LARGE PLATED COPPER | 19 | 95 |
| Brand#11 | SMALL BRUSHED STEEL | 9 | 92 |
+----------+--------------------------+--------+--------------+
10 rows in set (54.52 sec)
Summary about this PR
Issue Number: close #330 #331
Tests Check List
- [ ] Unit test
- [ ] Integration test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No code
Changelog
- [ ] New Feature
- [x] Bug Fix
- [x] Improvement
- [ ] Performance Improvement
- [ ] Build/Testing/CI/CD
- [ ] Documentation
- [ ] Not for changelog (changelog entry is not required)
Documentation
- [ ] Affects user behaviors
- [ ] Contains syntax changes
- [ ] Contains variable changes
- [ ] Contains experimental features
Thanks for the contribution! I have applied any labels matching special text in your PR Changelog.
Please review the labels and make any necessary changes.
![mergify[bot] avatar](https://avatars.githubusercontent.com/in/10562?v=4 "Published by a pub.dev verified publisher"){kind=small}
Can this modification be verified by MTR with manually generated data? This also ensures that the correctness of the next modification can be verified.
