[HIVE-28263] Metastore scripts : Update query getting stuck when sub-query of in-clause is returning empty results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Not A Bug
• Fix Version/s: Not Applicable
• Component/s: Hive
• Assignee: Taraka Rama Rao Lethavadla
• Reporter: Taraka Rama Rao Lethavadla
• Created: 16/May/24 09:12
• Updated: 04/Jun/24 12:28
• Resolved: 31/May/24 08:25

Description

As part of fix HIVE-27457

below query is added to upgrade-4.0.0-alpha-2-to-4.0.0-beta-1.mysql.sql

UPDATE SERDES
SET SERDES.SLIB = "org.apache.hadoop.hive.kudu.KuduSerDe"
WHERE SERDE_ID IN (
SELECT SDS.SERDE_ID
FROM TBLS
INNER JOIN SDS ON TBLS.SD_ID = SDS.SD_ID
WHERE TBLS.TBL_ID IN (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_VALUE LIKE '%KuduStorageHandler%')
);

This query is getting hung when sub-query is returning empty results in MySQL
 

 

MariaDB [test]> SELECT TBL_ID FROM table_params WHERE PARAM_VALUE LIKE '%KuduStorageHandler%';
Empty set (0.33 sec)
MariaDB [test]> SELECT sds.SERDE_ID FROM tbls LEFT JOIN sds ON tbls.SD_ID = sds.SD_ID WHERE tbls.TBL_ID IN (SELECT TBL_ID FROM table_params WHERE PARAM_VALUE LIKE '%KuduStorageHandler%');
Empty set (0.44 sec)

And the query kept on running for more than 20 minutes

MariaDB [test]> UPDATE serdes SET serdes.SLIB = "org.apache.hadoop.hive.kudu.KuduSerDe" WHERE SERDE_ID IN ( SELECT sds.SERDE_ID FROM tbls LEFT JOIN sds ON tbls.SD_ID = sds.SD_ID WHERE tbls.TBL_ID IN (SELECT TBL_ID FROM table_params WHERE PARAM_VALUE LIKE '%KuduStorageHandler%'));
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted

The explain extended looks like

MariaDB [test]> explain extended UPDATE serdes SET serdes.SLIB = "org.apache.hadoop.hive.kudu.KuduSerDe" WHERE SERDE_ID IN ( SELECT sds.SERDE_ID FROM tbls LEFT JOIN sds ON tbls.SD_ID = sds.SD_ID WHERE tbls.TBL_ID IN (SELECT TBL_ID FROM table_params WHERE PARAM_VALUE LIKE '%KuduStorageHandler%'));
+------+--------------------+--------------+--------+---------------------------+--------------+---------+-----------------+--------+----------+-------------+
| id   | select_type        | table        | type   | possible_keys             | key          | key_len | ref             | rows   | filtered | Extra       |
+------+--------------------+--------------+--------+---------------------------+--------------+---------+-----------------+--------+----------+-------------+
|    1 | PRIMARY            | serdes       | index  | NULL                      | PRIMARY      | 8       | NULL            | 401267 |   100.00 | Using where |
|    2 | DEPENDENT SUBQUERY | tbls         | index  | PRIMARY,TBLS_N50,TBLS_N49 | TBLS_N50     | 9       | NULL            |  50921 |   100.00 | Using index |
|    2 | DEPENDENT SUBQUERY | <subquery3>  | eq_ref | distinct_key              | distinct_key | 8       | func            |      1 |   100.00 |             |
|    2 | DEPENDENT SUBQUERY | sds          | eq_ref | PRIMARY                   | PRIMARY      | 8       | test.tbls.SD_ID |      1 |   100.00 | Using where |
|    3 | MATERIALIZED       | table_params | ALL    | PRIMARY,TABLE_PARAMS_N49  | NULL         | NULL    | NULL            | 356593 |   100.00 | Using where |
+------+--------------------+--------------+--------+---------------------------+--------------+---------+-----------------+--------+----------+-------------+
5 rows in set (0.00 sec)

Issue Links

• is duplicated by: HIVE-27555 Upgrade issues with Kudu table on backend db