[teiid-issues] [JBoss JIRA] (TEIID-5474) NPE on Complex Query with Procedure Call in SELECT
dalex dalex (JIRA)
issues at jboss.org
Thu Sep 13 07:43:00 EDT 2018
[ https://issues.jboss.org/browse/TEIID-5474?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
dalex dalex updated TEIID-5474:
-------------------------------
Steps to Reproduce:
1. Add MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml:
{code: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>
{code}
2. Add in test-vdb.xml java:/adventureworks configured in previous step as a data source:
{code:xml}
<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>
{code}
3. Add in test-vdb.xml "createDateDimensionsTable" virtual procedure:
{code:xml}
<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>
{code}
4. Run the test query:
{code:sql}
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 ;;
{code}
was:
1. Add MySQL (in my case I use adventureworks test database) database configurations in standalone-teiid.xml:
{code: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>
{code}
2. Add in test-vdb.xml java:/adventureworks configured in previous step as data source:
{code:xml}
<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>
{code}
3. Add "createDateDimensionsTable" virtual procedure:
{code:xml}
<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>
{code}
4. Run the test query:
{code:sql}
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 ;;
{code}
> NPE on Complex Query with Procedure Call in SELECT
> --------------------------------------------------
>
> Key: TEIID-5474
> URL: https://issues.jboss.org/browse/TEIID-5474
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Running the following query:
> {code:sql}
> 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 ;;
> {code}
> will throw out the following stacktrace with NPE:
> {code:noformat}
> 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)
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list