[teiid-issues] [JBoss JIRA] (TEIID-3816) Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
Steven Hawkins (JIRA)
issues at jboss.org
Thu Jan 28 09:18:00 EST 2016
[ https://issues.jboss.org/browse/TEIID-3816?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13155615#comment-13155615 ]
Steven Hawkins commented on TEIID-3816:
---------------------------------------
There are several related bugs with Informix here dealing with a boolean subquery that returns a single row. Informix will ignore the NOT with an IN predicate, and has the results for <> ALL and = ALL flipped.
Since this is such a specific scenario and there isn't a simple compensation that we can do at the translator level, I would vote for making a KI out of this.
> Informix translator - <> ALL is translated as NOT IN - this statement seem to not work in Infromix
> --------------------------------------------------------------------------------------------------
>
> Key: TEIID-3816
> URL: https://issues.jboss.org/browse/TEIID-3816
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 8.12.x
>
>
> Query:
> {code:sql}
> SELECT INTKEY, BOOLEANVALUE FROM BQT1.SMALLA WHERE BOOLEANVALUE <> ALL (SELECT BOOLEANVALUE FROM BQT1.SMALLA WHERE INTKEY = 40)
> {code}
> Source-specific command:
> {code:sql}
> SELECT g_0.IntKey, g_0.BooleanValue FROM Source.SmallA AS g_0 WHERE g_0.BooleanValue NOT IN (SELECT g_1.BooleanValue FROM Source.SmallA AS g_1 WHERE g_1.IntKey = 40)
> {code}
> *Note: boolean value for row where Intkey = 40 is false.*
> Expected result:
> |intkey|booleanvalue|
> |1|true|
> |...|...|
> Actual result:
> |intkey|booleanvalue|
> |0|false|
> |...|...|
> This seems to be an Informix issue as neither '<> ALL' nor 'NOT IN' works.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
More information about the teiid-issues
mailing list