Sql queries are not working as expecting on Mysql database through Teiid

[aakash03]

Hi,

#1.I am trying to execute the below query :

//given

select count(*) from (Select T_Address from TargetsqlConnectionName.DQ_2012.dbo.UT_Target_Fail except Select Address from SourcesqlConnectionName.DQ_2012.dbo.UT_Source) as aa 

and  trying to get the below error message as an output:

//expected

Conversion failed when converting the varchar value '641 Grove Path' to data type int.

But getting the below  exception

//Actual

org.teiid.jdbc.TeiidSQLException: ASSERTION FAILED: expected reference to be not null

                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

                at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)

                at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

                at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)

                at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

                at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

                at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

                at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268)

                at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:483)

                at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

#2.Can you please give  equivalent query in teiid for the below MySql query:

SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = upper('TestTable') AND COLUMN_NAME = upper('salary').

Thanks,

Aakash K Sen

[shawkins]

What Teiid version are you on?

> #1.I am trying to execute the below query

Can you provide the server side stacktrace and the query plan?

> #2.Can you please give  equivalent query in teiid for the below MySql query:

SELECT NullType FROM SYS.COLUMNS WHERE upper(TableName) = 'TESTTABLE' and upper(Name) = 'SALARY'

The NullType will be one of:

'No Nulls', 'Nullable', 'Unknown'

Steve

[aakash03]

Thanks Steven for the Reply !!

What Teiid version are you on?

teiid 8.13

Jboss wildfly 9.x

> #1.I am trying to execute the below query

Can you provide the server side stacktrace and the query plan?

StackTrace:

Running through JDBC Connection:

//expected

Conversion failed when converting the varchar value '641 Grove Path' to data type int.

Running through Teiid Connection:

//Actual

org.teiid.jdbc.TeiidSQLException: ASSERTION FAILED: expected reference to be not null

                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

                at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)

                at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

                at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)

                at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

                at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

                at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

                at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268)

                at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:483)

                at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

Query Plan:

select count(*) from (Select T_Address from TargetsqlConnectionName.DQ_2012.dbo.UT_Target_Fail except Select Address from SourcesqlConnectionName.DQ_2012.dbo.UT_Source) as aa

where dataType of T_Address column is Integer in the MySQL database.

Thanks,

Aakash K Sen

[shawkins]

The stacktrace needs to be from the server side log - or it will be further down in the exception chain on the client.  The top level message in this case is not enough to clearly identify what is happening.

The query plan can be obtained in a couple of ways see: Query Plans · Teiid Documentation