3 Replies Latest reply on Apr 3, 2017 3:50 PM by shawkins

    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

        • 1. Re: Sql queries are not working as expecting on Mysql database through Teiid
          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

          • 2. Re: Sql queries are not working as expecting on Mysql database through Teiid
            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

            • 3. Re: Sql queries are not working as expecting on Mysql database through Teiid
              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