Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3223

An issue with time in WHERE clause - SQLServer

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 8.7.1
    • Fix Version/s: 8.7.1, 8.10
    • Component/s: None
    • Labels:
      None
    • Steps to Reproduce:
      Hide

      Query:
      SELECT BQT1.SmallA.IntKey, BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue < '05:00:00' ORDER BY IntKey
      Query return all rows except rows with TimeValue == null (same in case '<=' and '<>')

      SELECT BQT1.SmallA.IntKey, BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue > '05:00:00' ORDER BY IntKey
      Query return no row (same in case '>=' and '=')

      Dynamic VDB:
      <vdb name="test_teiid" version="1">

      <model name="BQT1">
      <source name="local" translator-name="sqlserver" connection-jndi-name="java:/SQL2012_PushDS"/>
      <metadata type="DDL"><![CDATA[
      CREATE FOREIGN TABLE smalla (
      INTNUM integer,
      BIGDECIMALVALUE bigdecimal,
      BIGINTEGERVALUE biginteger,
      BOOLEANVALUE boolean,
      BYTENUM byte,
      CHARVALUE char,
      DATEVALUE date,
      DOUBLENUM double,
      FLOATNUM float,
      INTKEY integer,
      LONGNUM long,
      OBJECTVALUE object,
      SHORTVALUE short,
      STRINGKEY string,
      STRINGNUM string,
      TIMESTAMPVALUE timestamp,
      TIMEVALUE time
      ) OPTIONS(UPDATABLE 'TRUE');
      ]]> </metadata>
      </model>
      </vdb>

      data source:
      <datasource jndi-name="java:/SQL2012_PushDS" pool-name="mssqlDS_2012" enabled="true">
      <connection-url>

      {url}

      </connection-url>
      <driver>mssql</driver>
      <security>
      <user-name>

      {username}

      </user-name>
      <password>

      {password}

      </password>
      </security>
      </datasource>

      driver: sqljdbc4.jar

      Show
      Query: SELECT BQT1.SmallA.IntKey, BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue < '05:00:00' ORDER BY IntKey Query return all rows except rows with TimeValue == null (same in case '<=' and '<>') SELECT BQT1.SmallA.IntKey, BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue > '05:00:00' ORDER BY IntKey Query return no row (same in case '>=' and '=') Dynamic VDB: <vdb name="test_teiid" version="1"> <model name="BQT1"> <source name="local" translator-name="sqlserver" connection-jndi-name="java:/SQL2012_PushDS"/> <metadata type="DDL"><![CDATA[ CREATE FOREIGN TABLE smalla ( INTNUM integer, BIGDECIMALVALUE bigdecimal, BIGINTEGERVALUE biginteger, BOOLEANVALUE boolean, BYTENUM byte, CHARVALUE char, DATEVALUE date, DOUBLENUM double, FLOATNUM float, INTKEY integer, LONGNUM long, OBJECTVALUE object, SHORTVALUE short, STRINGKEY string, STRINGNUM string, TIMESTAMPVALUE timestamp, TIMEVALUE time ) OPTIONS(UPDATABLE 'TRUE'); ]]> </metadata> </model> </vdb> data source: <datasource jndi-name="java:/SQL2012_PushDS" pool-name="mssqlDS_2012" enabled="true"> <connection-url> {url} </connection-url> <driver>mssql</driver> <security> <user-name> {username} </user-name> <password> {password} </password> </security> </datasource> driver: sqljdbc4.jar

      Description

      Teiid is not able to manage WHERE clause with time value.
      translator: sqlserver
      base DB: SQL Server 2012/2008/2005

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jdurani Juraj DurĂ¡ni
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: