blafond, what is supposed to be the resolution here? Because when I import the following DDL:

CREATE FOREIGN TABLE "t1" (
    c1 integer not null options (NAMEINSOURCE '"c1"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE 't1');

I get the same result in 8.3.4 as in 8.3.3, that is the c1 column is created with datatype "integer : xs:decimal" (this is also true for 8.6)

Issue resolved in Jan '14. but needs to be applied to 8.3.x branch

Done

The intent is to be consistent. In this case I have a SQL Server DB with a BQT table. If I import a column type int via JDBC importer, I end up with a design-time type of int. If I import via TEiid connection/DDL, the column datatype is defined as integer. Currently we set the design-time type to integer. So the 2 relational columns end up with different datatypes.

I'm assuming for the moment that our JDBC importer is setting the property design-time type.

The runtime type is determined at index time via a SqlAspect.getRuntimeTypeName(EObject type) which eventually walks the datatype hiearchy to get the base type. (I'll verify that tomorrow)

> Q: Should the Teiid Import option look at native type (i.e. int) and use that instead? or map integer >> int

I'm not sure what you mean. We need to be consistent here with there the type names come from. From a teiid/sql perspective int/integer are just synonyms. So I'm not sure by what you mean by map integer to int, double to float, etc. You have to say what domain each of the terms apply to. From there we can say things like teiid double = sql server float, xsd integer = teiid biginteger, and so on and so forth.

Generally the DDL importer when importing Teiid DDL should always use the Teiid type as the runtime type. The native type is not necessary in making the determination.

So let's walk through a couple scenarios...

1) column named type_int of type int

• JDBC import
  • converts to type int
• Teiid DDL contains type = integer and native type = int
  • converts to type integer

Q: Should the Teiid Import option look at native type (i.e. int) and use that instead? or map integer >> int

2) column named type_float

• JDBC import
  • converts to type float
• Teiid DDL contains type = double and native type = float
  • converts to type double

Q: Should the Teiid Import option look at native type (i.e. float) and use that instead? or map double > float

3) column named type_character_10

• JDBC import
  • converts to type char(10)
• Teiid DDL contains type = string(10) and native type = char
  • converts to type string(10

Q: Should the Teiid Import option look at native type (i.e. char) and use that instead? Based on string(10) there no way to tell that this is a char(10) without looking at the native type

In the image:

• ALL_TYPES_JDBC is imported via JDBC importer
• ALL_TYPES_TEIID is imported via Teiid Connection Importer which operates on the above DDL during that import.

We do not currently map the DDL column datatypes in the Teiid Connection importer. Hence, "integer" results in the built-in-type "integer".

So what should the mappings be? And is our JDBC importer mapping types correctl?

So here ALL_TYPES_JDBC is imported from SQLServer
and ALL_TYPES_TEIID is import of DDL of previous? And the DDL is shown above?

I believe the error is in the DDL parsing or built-in-type mapping there is wrong mapping.

TEIID DDL from BQT2 (SqlServer) ALL_TYPES table:

CREATE FOREIGN TABLE "bqt2.BQT2.ALL_TYPES" (
type_int integer OPTIONS (NAMEINSOURCE '"type_int"', NATIVE_TYPE 'int'),
type_integer integer OPTIONS (NAMEINSOURCE '"type_integer"', NATIVE_TYPE 'int'),
type_smallint short OPTIONS (NAMEINSOURCE '"type_smallint"', NATIVE_TYPE 'smallint'),
type_tinyint short OPTIONS (NAMEINSOURCE '"type_tinyint"', NATIVE_TYPE 'tinyint'),
type_decimal bigdecimal(18,2147483647) OPTIONS (NAMEINSOURCE '"type_decimal"', NATIVE_TYPE 'decimal'),
type_decimal_5 bigdecimal(5,2147483647) OPTIONS (NAMEINSOURCE '"type_decimal_5"', NATIVE_TYPE 'decimal'),
type_decimal_5_5 bigdecimal(5,2147483647) OPTIONS (NAMEINSOURCE '"type_decimal_5_5"', NATIVE_TYPE 'decimal'),
type_double_precision double OPTIONS (NAMEINSOURCE '"type_double_precision"', RADIX 2, NATIVE_TYPE 'float'),
type_float double OPTIONS (NAMEINSOURCE '"type_float"', RADIX 2, NATIVE_TYPE 'float'),
type_float_10 float OPTIONS (NAMEINSOURCE '"type_float_10"', RADIX 2, NATIVE_TYPE 'real'),
type_numeric bigdecimal(18,2147483647) OPTIONS (NAMEINSOURCE '"type_numeric"', NATIVE_TYPE 'numeric'),
type_numeric_5 bigdecimal(5,2147483647) OPTIONS (NAMEINSOURCE '"type_numeric_5"', NATIVE_TYPE 'numeric'),
type_numeric_5_5 bigdecimal(5,2147483647) OPTIONS (NAMEINSOURCE '"type_numeric_5_5"', NATIVE_TYPE 'numeric'),
type_real float OPTIONS (NAMEINSOURCE '"type_real"', RADIX 2, NATIVE_TYPE 'real'),
type_bit boolean OPTIONS (NAMEINSOURCE '"type_bit"', RADIX 0, NATIVE_TYPE 'bit'),
type_character string(1) OPTIONS (NAMEINSOURCE '"type_character"', NATIVE_TYPE 'char'),
type_character_10 string(10) OPTIONS (NAMEINSOURCE '"type_character_10"', NATIVE_TYPE 'char'),
type_char string(1) OPTIONS (NAMEINSOURCE '"type_char"', NATIVE_TYPE 'char'),
type_char_10 string(10) OPTIONS (NAMEINSOURCE '"type_char_10"', NATIVE_TYPE 'char'),
type_nchar string(1) OPTIONS (NAMEINSOURCE '"type_nchar"', CHAR_OCTET_LENGTH 2, NATIVE_TYPE 'nchar'),
type_nchar_10 string(10) OPTIONS (NAMEINSOURCE '"type_nchar_10"', CHAR_OCTET_LENGTH 20, NATIVE_TYPE 'nchar'),
type_varchar string(1) OPTIONS (NAMEINSOURCE '"type_varchar"', NATIVE_TYPE 'varchar'),
type_varchar_10 string(10) OPTIONS (NAMEINSOURCE '"type_varchar_10"', NATIVE_TYPE 'varchar'),
type_long_nvarchar string(1) OPTIONS (NAMEINSOURCE '"type_long_nvarchar"', CHAR_OCTET_LENGTH 2, NATIVE_TYPE 'nvarchar'),
type_long_nvarchar_10 string(10) OPTIONS (NAMEINSOURCE '"type_long_nvarchar_10"', CHAR_OCTET_LENGTH 20, NATIVE_TYPE 'nvarchar'),
type_text clob OPTIONS (NAMEINSOURCE '"type_text"', NATIVE_TYPE 'text'),
type_money bigdecimal(19,2147483647) OPTIONS (NAMEINSOURCE '"type_money"', NATIVE_TYPE 'money'),
type_smallmoney bigdecimal(10,2147483647) OPTIONS (NAMEINSOURCE '"type_smallmoney"', NATIVE_TYPE 'smallmoney'),
type_datetime timestamp OPTIONS (NAMEINSOURCE '"type_datetime"', NATIVE_TYPE 'datetime'),
type_binary varbinary(1) OPTIONS (NAMEINSOURCE '"type_binary"', NATIVE_TYPE 'binary'),
type_binary_2 varbinary(2) OPTIONS (NAMEINSOURCE '"type_binary_2"', NATIVE_TYPE 'binary'),
type_image blob OPTIONS (NAMEINSOURCE '"type_image"', NATIVE_TYPE 'image'),
type_varbinary varbinary(1) OPTIONS (NAMEINSOURCE '"type_varbinary"', NATIVE_TYPE 'varbinary')
) OPTIONS (NAMEINSOURCE '"bqt2"."BQT2"."ALL_TYPES"', UPDATABLE TRUE, CARDINALITY 0);

After more digging, it appears the DDL importer isn't mapping datatypes based on string value key, so "integer" will map to "integer", etc..

The JDBC importer has a data type mapping class that offers more than just "find the built-in data type". Investigating more...

It does seem odd/confusing. These built-in datatypes were defined a millenia ago. If we need to clean them up, seriously, feel free to make some recommendations.

1) As far as the Teiid DDL importer, we can catch this scenario rather easily and change all "integer" types to "xs:long".

2) We're setting the datatype to a numeric but not setting precision (default is 0) which results in a warning for each "numeric" column. What should the default be set to?