Division in MSSQL View materialization

[mtawk]

I'm using Teiid 9.0.0 with MSSQL translator.

I'm materializing a simple view containing division: select Field1/Field2 as DivField From mytable

Teiid query resultset metadata is returning the data type float for DivField, so the view table and stage table are created accordingly in MSSQL with a field of type float.

Materialization is done successfully but when fetching the view i'm getting the error : Expected float, but was double

I find out in the method addColumn in JDBCMetdataProcessor.java, runtimeType is assigned to "double" instead "float". the variables "type" is set to "8" and "typeName" set to "float".

In MSSQL JDBC metadata,the type 8 refers to float not to double.

I have forced the runtimeType value to float for test sake and the view fetched successfully and returned data.

What do you suggest to do in such a case?

[rareddy]

Try with importer property "widenUnsignedTypes", may be it will be double in both cases.

[shawkins]

> In MSSQL JDBC metadata,the type 8 refers to float not to double.

Can you clarify that?  The 8 constant refers to java.sql.Types constants, where DOUBLE=8 and FLOAT=6

From float and real (Transact-SQL) real and float(1-24) would map to a java/Teiid float.  And float(25-53) would map to a java/Teiid double.

Testing against SQL Server I see that float(1) will be reported as a FLOAT type and float(25) will be reported as a DOUBLE as expected.

[mtawk]

"widenUnsignedTypes" is already set to true, but it deals only with small int, tiny int and integer data types.

[mtawk]

When getting Teiid resultset metadata from the view query for the division column, it is returning the data type "7" and data type name "float".

So accordingly, the materialization table and stage table are created in mssql and the division column in the table is given the data type float.

To get the corresponding datatype in mssql, i'm using SQLServer2008Dialect.getTypeName(sqlType, length, precision, scale) and it is returning only "float" without the length.

The problem was solved when i have added manually the length to the float datatype in the view table creation script, in my case it was float(22).