[teiid-issues] [JBoss JIRA] (TEIID-4520) Support for Exasol Connector

[Rafal Korytkowski (JIRA)]

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

Rafal Korytkowski edited comment on TEIID-4520 at 5/14/18 10:19 AM:
--------------------------------------------------------------------

I've identified the issue to be related to NULL sorting. Resolving this issue will most likely fix hundred of failing tests as they seem to be related. 

By default Exasol returns NULLs as last in ascending order as opposed to other DBs like MySQL, PostgreSQL, etc. Teiid expects the revert. It's why the verification step fails in the join query from above. -I was looking for a way to change the sort behavior in Exasol, but it seems it's not possible. (see my comment below)-

e.g. SELECT BigIntegerValue FROM BQT1.SmallA ORDER BY BigIntegerValue has the following processing plan (not yet sure why convert kicks in, any hints?):
LimitNode(0) output=[convert(BigIntegerValue, biginteger)] limit 100
  SortNode(1) output=[convert(BigIntegerValue, biginteger)] [SORT] [convert(BigIntegerValue, biginteger)]
    ProjectNode(2) output=[convert(BigIntegerValue, biginteger)] [convert(BigIntegerValue, biginteger)]
      AccessNode(3) output=[BigIntegerValue] SELECT g_0.BigIntegerValue FROM Source.smalla AS g_0

whereas SELECT BigDecimalValue FROM BQT1.SmallA ORDER BY BigDecimalValue:
AccessNode(0) output=[BigDecimalValue] SELECT g_0.BigDecimalValue AS c_0 FROM Source.smalla AS g_0 ORDER BY c_0 LIMIT 100

In the first case my understanding is that SORT is applied on the teiid level, thus I see NULLs first, whereas in the second query it's passed directly to Exasol and NULLs come last. Any suggestions on what would be the best fix?  Where there any cases like that in the past?


was (Author: rkorytkowski):
I've identified the issue to be related to NULL sorting. Resolving this issue will most likely fix hundred of failing tests as they seem to be related. 

By default Exasol returns NULLs as last in ascending order as opposed to other DBs like MySQL, PostgreSQL, etc. Teiid expects the revert. It's why the verification step fails in the join query from above. -I was looking for a way to change the sort behavior in Exasol, but it seems it's not possible.-

e.g. SELECT BigIntegerValue FROM BQT1.SmallA ORDER BY BigIntegerValue has the following processing plan (not yet sure why convert kicks in, any hints?):
LimitNode(0) output=[convert(BigIntegerValue, biginteger)] limit 100
  SortNode(1) output=[convert(BigIntegerValue, biginteger)] [SORT] [convert(BigIntegerValue, biginteger)]
    ProjectNode(2) output=[convert(BigIntegerValue, biginteger)] [convert(BigIntegerValue, biginteger)]
      AccessNode(3) output=[BigIntegerValue] SELECT g_0.BigIntegerValue FROM Source.smalla AS g_0

whereas SELECT BigDecimalValue FROM BQT1.SmallA ORDER BY BigDecimalValue:
AccessNode(0) output=[BigDecimalValue] SELECT g_0.BigDecimalValue AS c_0 FROM Source.smalla AS g_0 ORDER BY c_0 LIMIT 100

In the first case my understanding is that SORT is applied on the teiid level, thus I see NULLs first, whereas in the second query it's passed directly to Exasol and NULLs come last. Any suggestions on what would be the best fix?  Where there any cases like that in the past?

> Support for Exasol Connector
> ----------------------------
>
>                 Key: TEIID-4520
>                 URL: https://issues.jboss.org/browse/TEIID-4520
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Misc. Connectors
>    Affects Versions: 9.x
>            Reporter: Van Halbert
>            Assignee: Andrej Šmigala
>             Fix For: 10.x, Open To Community
>
>
> Requesting support for Exasol Connector



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)