[teiid-issues] [JBoss JIRA] (TEIID-4926) Couchbase - unable to retrieve data if using 'convert' in view definition

[Juraj Duráni (JIRA)]

Juraj Duráni created TEIID-4926:
-----------------------------------

             Summary: Couchbase - unable to retrieve data if using 'convert' in view definition
                 Key: TEIID-4926
                 URL: https://issues.jboss.org/browse/TEIID-4926
             Project: Teiid
          Issue Type: Bug
          Components: Misc. Connectors
    Affects Versions: 9.3
            Reporter: Juraj Duráni
            Assignee: Steven Hawkins
            Priority: Blocker


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
{code:sql}
OK - select * from couchbase.smalla
FAIL - select * from bqt1.smalla
FAIL - select convert(datevalue, date) from couchbase.smalla
{code}

\[4\] VDB definition
{code:sql}
-- 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;
{code}




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