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)
