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

Couchbase - unable to retrieve data if using 'convert' in view definition

    Details

      Description

      I have a simple document structure in Couchbase (no nested documents, all values are strings, integers, and booleans).

      I have a source model with proper types (strings, integers, booleans) and view model defined with my target types (float, double, date,...) all with appropriate conversion.

      There are basically two issues:

      • When I try to retrieve all data from the view, Teiid ends with exception [1]. This is wired.
      • When I try to use explicit convert on some columns from source table (not view) it ends with similar exception [2].

      Additional information:
      I represent 'Null' values by excluding key from the document (nevertheless, same error occurs even if I add explicit "key":null to the definition of the document).

      [1]
      TEIID10076 Invalid conversion from type class java.lang.Integer with value '-24' to type class java.sql.Date

      [2] - case for data value
      TEIID10076 Invalid conversion from type class java.lang.Object with value '{}' to type class java.sql.Date

      [3] - Queries

      OK - select * from couchbase.smalla
      FAIL - select * from bqt1.smalla
      FAIL - select convert(datevalue, date) from couchbase.smalla
      

      [4] VDB definition

      -- source model
      SET NAMESPACE 'http://www.teiid.org/translator/couchbase/2017' AS teiid_couchbase;
      CREATE FOREIGN TABLE SmallA (
        documentID string,
        FloatNum integer OPTIONS (NAMEINSOURCE '`smalla`.`FloatNum`'),
        IntKey integer PRIMARY KEY OPTIONS (NAMEINSOURCE '`smalla`.`IntKey`'),
        BigIntegerValue integer OPTIONS (NAMEINSOURCE '`smalla`.`BigIntegerValue`'),
        StringKey string OPTIONS (NAMEINSOURCE '`smalla`.`StringKey`'),
        CharValue string OPTIONS (NAMEINSOURCE '`smalla`.`CharValue`'),
        LongNum integer OPTIONS (NAMEINSOURCE '`smalla`.`LongNum`'),
        type string OPTIONS (NAMEINSOURCE '`smalla`.`type`'),
        DoubleNum integer OPTIONS (NAMEINSOURCE '`smalla`.`DoubleNum`'),
        ObjectValue string OPTIONS (NAMEINSOURCE '`smalla`.`ObjectValue`'),
        ShortValue integer OPTIONS (NAMEINSOURCE '`smalla`.`ShortValue`'),
        BigDecimalValue integer OPTIONS (NAMEINSOURCE '`smalla`.`BigDecimalValue`'),
        DateValue string OPTIONS (NAMEINSOURCE '`smalla`.`DateValue`'),
        BooleanValue boolean OPTIONS (NAMEINSOURCE '`smalla`.`BooleanValue`'),
        TimestampValue string OPTIONS (NAMEINSOURCE '`smalla`.`TimestampValue`'),
        ByteNum integer OPTIONS (NAMEINSOURCE '`smalla`.`ByteNum`'),
        StringNum string OPTIONS (NAMEINSOURCE '`smalla`.`StringNum`'),
        TimeValue string OPTIONS (NAMEINSOURCE '`smalla`.`TimeValue`'),
        IntNum integer OPTIONS (NAMEINSOURCE '`smalla`.`IntNum`')
      ) OPTIONS (NAMEINSOURCE '`smalla`', UPDATABLE FALSE, "teiid_couchbase:ISARRAYTABLE" 'false', "teiid_couchbase:NAMEDTYPEPAIR" '`type`:''SmallA''');
      
      -- view model
      CREATE VIEW SmallA (
        IntKey integer PRIMARY KEY,
        StringKey string,
        IntNum integer,
        StringNum string,
        FloatNum float,
        LongNum long,
        DoubleNum double,
        ByteNum byte,
        DateValue date,
        TimeValue time,
        TimestampValue timestamp,
        BooleanValue boolean,
        CharValue char(1),
        ShortValue short,
        BigIntegerValue biginteger,
        BigDecimalValue bigdecimal,
        ObjectValue object)
      AS
        SELECT
          IntKey, StringKey, IntNum, StringNum, convert(FloatNum, float) AS FloatNum, convert(LongNum, long) AS LongNum,
          convert(DoubleNum, double) AS DoubleNum, convert(ByteNum, byte) AS ByteNum, convert(DateValue, date) AS DateValue,
          convert(TimeValue, time) AS TimeValue, convert(TimestampValue, timestamp) AS TimstampValue, BooleanValue,
          convert(CharValue, char) AS CharValue, convert(ShortValue, short) AS ShortValue, convert(BigIntegerValue, biginteger) AS BigIntegerValue,
          convert(BigDecimalValue, bigdecimal) AS BigDecimalValue, convert(ObjectValue, object) AS ObjectValue
        FROM
          Couchbase.SmallA;
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  kylin Kylin Soong
                  Reporter:
                  jdurani Juraj DurĂ¡ni
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: