Taraka Rama Rao Lethavadla created HIVE-28263: -------------------------------------------------
Summary: Metastore scripts : Update query getting stuck when sub-query of in-clause is returning empty results Key: HIVE-28263 URL: https://issues.apache.org/jira/browse/HIVE-28263 Project: Hive Issue Type: Bug Components: Hive Reporter: Taraka Rama Rao Lethavadla 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|https://github.com/apache/hive/blob/0e84fe2000c026afd0a49f4e7c7dd5f54fe7b1ec/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.0.0-alpha-2-to-4.0.0-beta-1.mysql.sql#L43] {noformat} 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%') );{noformat} This query is getting hung when sub-query is returning empty results in MySQL {noformat} 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) {noformat} And the query kept on running for more than 20 minutes {noformat} 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{noformat} The explain extended looks like {noformat} 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){noformat} -- This message was sent by Atlassian Jira (v8.20.10#820010)