[teiid-issues] [JBoss JIRA] (TEIID-4694) PrestoDB translator - NULL values not supported in SemiJoin

[Kylin Soong (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4694?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13349075#comment-13349075 ] 

Kylin Soong edited comment on TEIID-4694 at 1/17/17 12:35 AM:
--------------------------------------------------------------

I also think we don't need do more work, leave a document to notify the users of the risk that some version of prestodb not support null as valid values in subquery.

Also from document [1]
{code}
The IN predicate determines if any values produced by the subquery are equal to the provided expression. The result of IN follows the standard rules for nulls. The subquery must produce exactly one column:
SELECT name
FROM nation
WHERE regionkey IN (SELECT regionkey FROM region)
{code}

Note that the 0.163 documents don't refer any about subquery return can not be null.

[1] https://prestodb.io/docs/current/sql/select.html



was (Author: kylin):
I also think we don't need do more work, leave a document to notify the users of the risk that some version of prestodb not support null as valid values in subquery.

Also from document [1]
{note}
The IN predicate determines if any values produced by the subquery are equal to the provided expression. The result of IN follows the standard rules for nulls. The subquery must produce exactly one column:
SELECT name
FROM nation
WHERE regionkey IN (SELECT regionkey FROM region)
{note}

Note that the 0.163 documents don't refer any about subquery return can not be null.

[1] https://prestodb.io/docs/current/sql/select.html


> PrestoDB translator - NULL values not supported in SemiJoin
> -----------------------------------------------------------
>
>                 Key: TEIID-4694
>                 URL: https://issues.jboss.org/browse/TEIID-4694
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.12.8.6_3
>            Reporter: Juraj Duráni
>            Assignee: Kylin Soong
>             Fix For: 9.2.1
>
>
> PrestoDB does not allows NULL values in SemiJoin operator. It means, that query like \[1\] causes an exception \[2\]. However, this is disallowed in version 0.162 but worked in 0.161. According to GitHub issues \[3\], this will be fixed in one of the future versions of PrestoDB. Do we want to handle it somehow?
> Development and release frequency of PrestoDB seems to be pretty fast (new release every 2 or 3 week).
> {code:sql|title=\[1\] Query}
> SELECT IntKey, ShortValue FROM BQT1.SmallA WHERE BQT1.SmallA.ShortValue IN (SELECT ShortValue FROM BQT1.SmallB)
> {code}
> {code:plain|title=\[2\] Exception}
> org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 Source: Unexpected exception while translating results: Query failed (#20170110_072908_00117_gftb8): NULL values are not allowed on the probe side of SemiJoin operator. See the query plan for details.
> 	at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
> ...
> Caused by: java.lang.RuntimeException: Remote com.facebook.presto.jdbc.internal.client.FailureInfo$FailureException: NULL values are not allowed on the probe side of SemiJoin operator. See the query plan for details.
> 	at com.facebook.presto.operator.HashSemiJoinOperator.addInput(HashSemiJoinOperator.java:179)
> 	at com.facebook.presto.operator.Driver.processInternal(Driver.java:384)
> 	at com.facebook.presto.operator.Driver.processFor(Driver.java:301)
> 	at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:622)
> 	at com.facebook.presto.execution.TaskExecutor$PrioritizedSplitRunner.process(TaskExecutor.java:534)
> 	at com.facebook.presto.execution.TaskExecutor$Runner.run(TaskExecutor.java:670)
> 	... 3 more
> {code}
> \[3\] https://github.com/prestodb/presto/issues/6991



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)