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

NPE on Complex Query with Procedure Call in SELECT

    Details

    • Steps to Reproduce:
      Hide

      1. Add MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml:

            <datasource jndi-name="java:/adventureworks" pool-name="adventureworks" enabled="true" use-java-context="true">
                          <connection-url>jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull</connection-url>
                          <driver>mysql</driver>
                          <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                          </pool>
                          <security>
                              <user-name>root</user-name>
                              <password>xxxxxx</password>
                          </security>
                          <validation>
                              <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      2. Add in test-vdb.xml java:/adventureworks configured in previous step as a data source:

          <model name="adventureworks">
              <property name="importer.useFullSchemaName" value="false"/>
              <property name="importer.tableTypes" value="TABLE,VIEW"/>
              <property name="importer.importKeys" value="false"/>
              <source name="adventureworks" translator-name="mylobs" connection-jndi-name="java:/adventureworks"/>
          </model>
       
          <translator name="mylobs" type="mysql5">
              <property name="CopyLobs" value="true" />
              <property name="SupportsNativeQueries" value="true"/>
          </translator>
      

      3. Add in test-vdb.xml "createDateDimensionsTable" virtual procedure:

          <model visible = "true" type = "VIRTUAL" name = "views">
              <metadata type = "DDL"><![CDATA[
              CREATE PROCEDURE createDateDimensionsTable(
      	     IN startdate date NOT NULL OPTIONS (ANNOTATION 'Start date for table.'),
      	     IN enddate date OPTIONS (ANNOTATION 'End date for table. If NULL, uses current date.')
              )
              RETURNS 
              (
      	     "month_start" date
              ) AS
              BEGIN
                  select CURDATE() as month_start;
              END
              ]]>
              </metadata>
          </model>
      

      4. Run the test query:

      SELECT 
      (exec "SYSADMIN.logMsg"(
          "level" => 'INFO',
          "context" => 'DEBUG.FOO.BAR',
          "msg" => tccd.bank_account_holder_name)) as something
      FROM 
      (
      	SELECT c.city AS "bank_account_holder_name"
      	FROM "adventureworks.address" c
      	JOIN 
      	(
      		select ca.addressid
      		from "adventureworks.customeraddress" ca
      	 cross JOIN 
      	(
      		WITH latest_exchange_rates AS 
      		(
      			SELECT exchange_rate_date AS month_begin
      			FROM (
      				SELECT  CURDATE() as exchange_rate_date
      				)t
      		)
      		SELECT DISTINCT dt.month_start AS month_begin
      		FROM (
      			CALL views.createDateDimensionsTable(
      				"startdate" => TIMESTAMPADD(SQL_TSI_MONTH, 1, (SELECT month_begin FROM latest_exchange_rates)),
      				"enddate" => TIMESTAMPADD(SQL_TSI_YEAR, 15, (SELECT month_begin FROM latest_exchange_rates))
      			) ) AS dt
      	) fx
      	) ci ON ci.addressid = c.addressid
      ) tccd ;;
      
      Show
      1. Add MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml: <datasource jndi-name= "java:/adventureworks" pool-name= "adventureworks" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull </connection-url> <driver> mysql </driver> <new-connection-sql> set SESSION sql_mode = 'ANSI' </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> root </user-name> <password> xxxxxx </password> </security> <validation> <valid-connection-checker class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" /> <exception-sorter class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" /> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 2. Add in test-vdb.xml java:/adventureworks configured in previous step as a data source: <model name= "adventureworks" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "adventureworks" translator-name= "mylobs" connection-jndi-name= "java:/adventureworks" /> </model> <translator name= "mylobs" type= "mysql5" > <property name= "CopyLobs" value= "true" /> <property name= "SupportsNativeQueries" value= "true" /> </translator> 3. Add in test-vdb.xml "createDateDimensionsTable" virtual procedure: <model visible = "true" type = "VIRTUAL" name = "views" > <metadata type = "DDL" > <![CDATA[ CREATE PROCEDURE createDateDimensionsTable( IN startdate date NOT NULL OPTIONS (ANNOTATION 'Start date for table.' ), IN enddate date OPTIONS (ANNOTATION 'End date for table. If NULL, uses current date.' ) ) RETURNS ( "month_start" date ) AS BEGIN select CURDATE() as month_start; END ]]> </metadata> </model> 4. Run the test query: SELECT ( exec "SYSADMIN.logMsg" ( " level " => 'INFO' , "context" => 'DEBUG.FOO.BAR' , "msg" => tccd.bank_account_holder_name)) as something FROM ( SELECT c .city AS "bank_account_holder_name" FROM "adventureworks.address" c JOIN ( select ca.addressid from "adventureworks.customeraddress" ca cross JOIN ( WITH latest_exchange_rates AS ( SELECT exchange_rate_date AS month_begin FROM ( SELECT CURDATE() as exchange_rate_date )t ) SELECT DISTINCT dt.month_start AS month_begin FROM ( CALL views.createDateDimensionsTable( "startdate" => TIMESTAMPADD(SQL_TSI_MONTH, 1, ( SELECT month_begin FROM latest_exchange_rates)), "enddate" => TIMESTAMPADD(SQL_TSI_YEAR, 15, ( SELECT month_begin FROM latest_exchange_rates)) ) ) AS dt ) fx ) ci ON ci.addressid = c .addressid ) tccd ;;

      Description

      Running the following query:

      SELECT 
      (exec "SYSADMIN.logMsg"(
          "level" => 'INFO',
          "context" => 'DEBUG.FOO.BAR',
          "msg" => tccd.bank_account_holder_name)) as something
      FROM 
      (
      	SELECT c.city AS "bank_account_holder_name"
      	FROM "adventureworks.address" c
      	JOIN 
      	(
      		select ca.addressid
      		from "adventureworks.customeraddress" ca
      	 cross JOIN 
      	(
      		WITH latest_exchange_rates AS 
      		(
      			SELECT exchange_rate_date AS month_begin
      			FROM (
      				SELECT  CURDATE() as exchange_rate_date
      				)t
      		)
      		SELECT DISTINCT dt.month_start AS month_begin
      		FROM (
      			CALL views.createDateDimensionsTable(
      				"startdate" => TIMESTAMPADD(SQL_TSI_MONTH, 1, (SELECT month_begin FROM latest_exchange_rates)),
      				"enddate" => TIMESTAMPADD(SQL_TSI_YEAR, 15, (SELECT month_begin FROM latest_exchange_rates))
      			) ) AS dt
      	) fx
      	) ci ON ci.addressid = c.addressid
      ) tccd ;;
      

      will throw out the following stacktrace with NPE:

      Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      2018-09-13 13:35:21,777 ERROR [org.teiid.PROCESSOR] (Worker5_QueryProcessorQueue36) ZvEDctN1yjKY TEIID30019 Unexpected exception for request ZvEDctN1yjKY.15: java.lang.NullPointerExc
      eption
              at org.teiid.query.optimizer.relational.rules.RuleMergeVirtual.doMerge(RuleMergeVirtual.java:218)
              at org.teiid.query.optimizer.relational.rules.RuleMergeVirtual.execute(RuleMergeVirtual.java:80)
              at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:1025)
              at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:228)
              at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:179)
              at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:458)
              at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
              at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:672)
              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:351)
              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
              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)
      

        Gliffy Diagrams

          Attachments

          For Gerrit Dashboard: TEIID-5474
          # Subject Branch Project Status CR V

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: