HIVE-28193: Skip CBO fallback when EXCEPT/INTERSECT/QUALIFY are used

[okumin]

What changes were proposed in this pull request?

Skip hive.cbo.fallback.strategy=CONSERVATIVE|ALWAYS when EXCEPT, INTERSECT, or QUALIFY are used.

https://issues.apache.org/jira/browse/HIVE-28193

Why are the changes needed?

Users see a confusing error message on hitting this problem. They are asked to enable CBO even though they enable CBO.

Does this PR introduce any user-facing change?

Practically, no.

Is the change a dependency upgrade?

No.

How was this patch tested?

I added negative tests to check error messages.

[okumin]

just 1 thing, maybe a new check should be moved to HiveCalciteUtil

Thanks. I looked at the file and I am sure that it is the right place

[okumin]

I think @zabetak 's proposal is further better, and the last revision follows it. I tested some cases by hand and verified the approach would likely work in all known cases. I didn't add them to qtest because it sounds overkilling to test all the cases.

0: jdbc:hive2://hive-hiveserver2:10000/defaul> select cast(0 as bigint) = '1' except select cast(1 as bigint) = '1';
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

0: jdbc:hive2://hive-hiveserver2:10000/defaul> select count(*) from (select cast(0 as bigint) = '1' except select cast(1 as bigint) = '1') x;
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

0: jdbc:hive2://hive-hiveserver2:10000/defaul> with x as (select cast(0 as bigint) = '1' except select cast(1 as bigint) = '1') select count(*) from x;
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

0: jdbc:hive2://hive-hiveserver2:10000/defaul> set hive.optimize.cte.materialize.threshold=1;
No rows affected (0.005 seconds)
0: jdbc:hive2://hive-hiveserver2:10000/defaul> set hive.optimize.cte.materialize.full.aggregate.only=false;
No rows affected (0.006 seconds)
0: jdbc:hive2://hive-hiveserver2:10000/defaul> with x as (select cast(0 as bigint) = '1' except select cast(1 as bigint) = '1') select count(*) from x;
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

0: jdbc:hive2://hive-hiveserver2:10000/defaul> select cast(key as bigint) = '1' from src qualify row_number() over (partition by key order by value) = 1;
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

0: jdbc:hive2://hive-hiveserver2:10000/defaul> select count(*) from (select cast(key as bigint) = '1' from src qualify row_number() over (partition by key order by value) = 1) x;
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

0: jdbc:hive2://hive-hiveserver2:10000/defaul> with x as (select cast(key as bigint) = '1' from src qualify row_number() over (partition by key order by value) = 1) select count(*) from x;
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

0: jdbc:hive2://hive-hiveserver2:10000/defaul> set hive.optimize.cte.materialize.threshold=1;
No rows affected (0.006 seconds)
0: jdbc:hive2://hive-hiveserver2:10000/defaul> set hive.optimize.cte.materialize.full.aggregate.only=false;
No rows affected (0.006 seconds)
0: jdbc:hive2://hive-hiveserver2:10000/defaul> with x as (select cast(key as bigint) = '1' from src qualify row_number() over (partition by key order by value) = 1) select count(*) from x;
Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Wrong arguments ''1'': Unsafe compares between different types are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.type.safety to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

[sonarqubecloud[bot]]

Quality Gate passed

Issues
6 New issues
0 Accepted issues

Measures
0 Security Hotspots
0.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarCloud

[okumin]

Thanks a lot!