Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6044

MSSQL: upper results in NPE

    XMLWordPrintable

Details

    • Hide

      1. In MSSQL create the rain table using the following script:

      CREATE TABLE [dbo].[rain](
      	[col0] [nvarchar](4000) NULL
      ) ON [PRIMARY]
      

      2. Add MSSQL database configuration in standalone-teiid.xml:

                      <datasource jndi-name="java:/mssql-test-dwh" pool-name="mssql-test-dwh" enabled="true" use-java-context="true">
                          <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
                          <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
                          <driver>com.microsoft.sqlserver</driver>
                          <new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                          </pool>
                          <security>
                              <user-name>xxxxx</user-name>
                              <password>xxxxx</password>
                          </security>
                          <validation>
                              <check-valid-connection-sql>select 1</check-valid-connection-sql>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      3. Add in test-vdb.xml java:/mssql-test-dwh configured in the previous step as a data source:

          <model name="ms_dwh">
              <property name="importer.useFullSchemaName" value="false"/>
              <property name="importer.tableTypes" value="TABLE,VIEW"/>
              <property name="importer.importKeys" value="false"/>
              <property name="importer.schemaPattern" value="dbo"/>
              <source name="dwh_ms" translator-name="mySqlserver" connection-jndi-name="java:/mssql-test-dwh"/>
          </model>
      
          <translator name="mySqlserver" type="sqlserver">
              <property name="SupportsNativeQueries" value="true"/>
              <property name="copyLobs" value="true"/>
          </translator>
      

      4. Add in test-vdb.xml the views.rain virtual view:

      <model visible = "true" type = "VIRTUAL" name = "views">
              <metadata type = "DDL"><![CDATA[
      create view rain
      as
      select col0 as warning, 
      		count(col0) as count from ms_dwh.rain group by col0
      union all  
      select col0 as warning, 
      		count(col0) as count from ms_dwh.rain group by col0
              ]]>
              </metadata>
          </model>
      

      5. Run the following query:

      --run select statement
      SELECT 	"warning"
      		,"count" 
      		,upper(warning)
      FROM views.rain ;;
      
      Show
      1. In MSSQL create the rain table using the following script: CREATE TABLE [dbo].[rain]( [col0] [nvarchar](4000) NULL ) ON [ PRIMARY ] 2. Add MSSQL database configuration in standalone-teiid.xml: <datasource jndi-name= "java:/mssql-test-dwh" pool-name= "mssql-test-dwh" enabled= "true" use-java-context= "true" > <connection-url> jdbc:sqlserver://localhost:1433;databaseName=test_dwh </connection-url> <driver-class> com.microsoft.sqlserver.jdbc.SQLServerDriver </driver-class> <driver> com.microsoft.sqlserver </driver> <new-connection-sql> SET ANSI_WARNINGS OFF </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> xxxxx </user-name> <password> xxxxx </password> </security> <validation> <check-valid-connection-sql> select 1 </check-valid-connection-sql> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 3. Add in test-vdb.xml java:/mssql-test-dwh configured in the previous step as a data source: <model name= "ms_dwh" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <property name= "importer.schemaPattern" value= "dbo" /> <source name= "dwh_ms" translator-name= "mySqlserver" connection-jndi-name= "java:/mssql-test-dwh" /> </model> <translator name= "mySqlserver" type= "sqlserver" > <property name= "SupportsNativeQueries" value= "true" /> <property name= "copyLobs" value= "true" /> </translator> 4. Add in test-vdb.xml the views.rain virtual view: <model visible = "true" type = "VIRTUAL" name = "views" > <metadata type = "DDL" > <![CDATA[ create view rain as select col0 as warning, count(col0) as count from ms_dwh.rain group by col0 union all select col0 as warning, count(col0) as count from ms_dwh.rain group by col0 ]]> </metadata> </model> 5. Run the following query: --run select statement SELECT "warning" , " count " , upper (warning) FROM views.rain ;;

    Description

      Running a query with UPPER on a MSSQL will result to a NPE:

      --run select statement
      SELECT 	"warning"
      		,"count" 
      		,upper(warning)
      FROM views.rain ;;
      
      2020-10-23 15:40:54,105 ERROR [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue9) uDna4ntt7KV7 Connector worker process failed for atomic-request=uDna4ntt7KV7.2.2.0: java.lang.NullPointerException                                                                                                                                                                               at org.teiid.translator.jdbc.sqlserver.SQLServerExecutionFactory$UpperLowerFunctionModifier.translate(SQLServerExecutionFactory.java:116)                                             at org.teiid.translator.jdbc.JDBCExecutionFactory.translate(JDBCExecutionFactory.java:548)                                                                                            at org.teiid.translator.jdbc.sybase.BaseSybaseExecutionFactory.translate(BaseSybaseExecutionFactory.java:96)                                                                          at org.teiid.translator.jdbc.sqlserver.SQLServerExecutionFactory.translate(SQLServerExecutionFactory.java:322)                                                                        at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:108)                                                                                               at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:863)                                                                                                       at org.teiid.translator.jdbc.SQLConversionVisitor.visit(SQLConversionVisitor.java:313)                                                                                                at org.teiid.language.DerivedColumn.acceptVisitor(DerivedColumn.java:43)                                                                                                              at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:47)                                                                                      at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:90)                                                                                                       at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:127)                                                                                               at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:105)                                                                                                      at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:796)                                                                                                       at org.teiid.language.Select.acceptVisitor(Select.java:106)                                                                                                                           at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:47)                                                                                      at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:90)                                                                                                       at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:127)                                                                                               at org.teiid.translator.jdbc.TranslatedCommand.translateCommand(TranslatedCommand.java:69)                                                                                            at org.teiid.translator.jdbc.JDBCBaseExecution.translateCommand(JDBCBaseExecution.java:122)                                                                                           at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:110)                                                                                                  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402)                                                                                               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:498)                                                                                                                                   at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)                                                                                                at com.sun.proxy.$Proxy46.execute(Unknown Source)                                                                                                                                     at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)                                                                                        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)                                                                                            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)                                                                                            at java.util.concurrent.FutureTask.run(FutureTask.java:266)                                                                                                                           at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59)                                                                                                                  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)                                                                                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)                                                                               at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)                                                                                             at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)                                                                                                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)                                                                                                    at java.lang.Thread.run(Thread.java:745)
      

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            dalex005 Dmitrii Pogorelov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: