wecharyu commented on PR #4831:
URL: https://github.com/apache/hive/pull/4831#issuecomment-1887547831
Test failure seems unrelated, rebase to mater to get green CI.
And add some query case of PARTITIONS in MySQL:
```sql
-- Case 1: with TBL_ID and PART_ID (hit PRIMARY index)
mysql> explain select * from PARTITIONS where TBL_ID=1 and PART_ID=1 and
PART_NAME like 'p=1%';
+----+-------------+------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys
| key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | PARTITIONS | NULL | const |
PRIMARY,UNIQUEPARTITION | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+
-- Case 2: with TBL_ID and partial partition values (hit UNIQUEPARTITION
index)
mysql> explain select * from PARTITIONS where TBL_ID=1 and PART_NAME like
'p=1%';
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key
| key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | PARTITIONS | NULL | range | UNIQUEPARTITION |
UNIQUEPARTITION | 779 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
-- Case 3: with only TBL_ID (hit the prefix index of UNIQUEPARTITION)
mysql> explain select * from PARTITIONS where TBL_ID=1;
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key
| key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | PARTITIONS | NULL | ref | UNIQUEPARTITION |
UNIQUEPARTITION | 9 | const | 10 | 100.00 | NULL |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
-- Case 4: with only PART_NAME (no index hit, it's acceptable because in the
real calls we always provide the TBL_ID for partition query)
mysql> explain select * from PARTITIONS where PART_NAME like 'p=1%';
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | PARTITIONS | NULL | ALL | NULL | NULL |
NULL | NULL | 10 | 11.11 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscr...@hive.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscr...@hive.apache.org
For additional commands, e-mail: gitbox-h...@hive.apache.org
