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

An issue with time in WHERE clause - SQLServer

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 8.7.1, 8.10
    • 8.7.1
    • None
    • None
    • 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

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            jdurani Juraj DurĂ¡ni (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: