[teiid-issues] [JBoss JIRA] (TEIID-4700) Google translator inconsistent behavior for DELETE and SELECT for null values

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-4700:
---------------------------------------

Unfortunately it looks like google spreadsheet null handling is a mess.

In select it appears that null is treated as a "low value", so it passes the less than tests, but not the greater.  We'll workaround that using IS NOT NULL.

In the delete/update case, things are worse - and the IS NOT NULL predicate is not supported, but we can use <> "" - for non-string types.  For string types things are more complicated since to google an empty string also matches the null string (similar to oracle).  We'll document that as part of this resolution.

> Google translator inconsistent behavior for DELETE and SELECT for null values
> -----------------------------------------------------------------------------
>
>                 Key: TEIID-4700
>                 URL: https://issues.jboss.org/browse/TEIID-4700
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 9.2
>         Environment: 9.2.0.Alpha2
>            Reporter: Lucie Fabrikova
>            Assignee: Steven Hawkins
>
> In teiid  I observed following inconsistent behavior:
> Q1: select intnum,  intkey from smalla where intnum > -10 AND intnum < 20 
> Q2: delete from smalla where intnum > -10 AND intnum < 20 
> Q1 returns rows without null values for intnum, but Q2 deletes also rows with null values for intnum.
> I observed that if I remove the lower boundary (ie. Q3: select intnum,  intkey from smalla where intnum < 20 and similarly for delete), both selected and deleted rows contain null values, so the result is consistent.
> The server.log is empty, so I didn't attach it...



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