[TEIID-5004] INNER JOINs of two CTEs fail with ASSERTION FAILED error message

Type: Bug
Resolution: Done
Priority: Blocker
Fix Version/s: 10.0, 9.2.5, 9.3.2
Affects Version/s: 9.3, 9.3.1
Component/s: Query Engine
Labels:
None

Steps to Reproduce:
Hide

1. In MySQL create the test_a table by the following script:

CREATE TABLE `test`.`test_a` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (1,1); INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (1,2); INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (2,1); INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (2,2); INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (3,2); INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (3,10);

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

<datasource jndi-name="java:/test" pool-name="test" enabled="true" use-java-context="true"> <connection-url>jdbc:mysql://localhost:3306/test?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>

3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource:

<model name="test"> <property name="importer.useFullSchemaName" value="false"/> <property name="importer.tableTypes" value="TABLE,VIEW"/> <property name="importer.importIndexes" value="false"/> <property name="importer.importKeys" value="false"/> <source name="test" translator-name="mylobs" connection-jndi-name="java:/test"/> </model>

using "myLobs" as following:

<translator name="mylobs" type="mysql5"> <property name="CopyLobs" value="true" /> <property name="SupportsNativeQueries" value="true"/> </translator>

4. run the following query several times in a row:

with CTE1 as ( select a1 from ( -->> with CTE11 as (select a1 from ( WITH alias as (SELECT 1 as a), alias1 as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a), alias2 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias), alias3 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.a from alias join alias2 AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias) SELECT AAA.alias1 as a1 FROM alias join alias1 as AAA on alias.a=AAA.a or alias.a!=AAA.a join alias2 on AAA.a=alias2.a or AAA.a!=alias.a left join alias3 on alias3.a=alias2.a or alias3.a!=alias2.a ) tv2) --<< select a1 from CTE11 ) as SUBQ1), CTE2 as ( select a1 from ( -->> with CTE21 as (select a1 from ( WITH alias as (SELECT 1 as a), alias1 as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a), alias2 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias), alias3 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.a from alias join alias2 AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias) SELECT AAA.alias1 as a1 FROM alias join alias1 as AAA on alias.a=AAA.a or alias.a!=AAA.a join alias2 on AAA.a=alias2.a or AAA.a!=alias.a left join alias3 on alias3.a=alias2.a or alias3.a!=alias2.a ) tv2) --<< select a1 from CTE21 ) as SUBQ2) select * from CTE1 as T1 join CTE2 as T2 on T1.a1=T2.a1
Show
1. In MySQL create the test_a table by the following script: CREATE TABLE `test`.`test_a` ( ` a ` int (11) DEFAULT NULL , `b` int (11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (1,1); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (1,2); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (2,1); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (2,2); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (3,2); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (3,10); 2. Add MySQL database configuration in standalone-teiid.xml: <datasource jndi-name= "java:/test" pool-name= "test" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/test?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> 3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource: <model name= "test" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importIndexes" value= "false" /> <property name= "importer.importKeys" value= "false" /> <source name= "test" translator-name= "mylobs" connection-jndi-name= "java:/test" /> </model> using "myLobs" as following: <translator name= "mylobs" type= "mysql5" > <property name= "CopyLobs" value= "true" /> <property name= "SupportsNativeQueries" value= "true" /> </translator> 4. run the following query several times in a row: with CTE1 as ( select a1 from ( -->> with CTE11 as ( select a1 from ( WITH alias as ( SELECT 1 as a ), alias1 as ( select alias . a as alias , AAA. a as alias1, AAA.b as a from alias join test.test_a AAA on alias . a !=AAA. a or alias . a !=AAA. a ), alias2 as ( with alias as ( select alias . a as alias , AAA. a as alias1, AAA.b as a from alias join test.test_a AAA on alias . a !=AAA. a or alias . a !=AAA. a ) select * from alias ), alias3 as ( with alias as ( select alias . a as alias , AAA. a as alias1, AAA. a from alias join alias2 AAA on alias . a !=AAA. a or alias . a !=AAA. a ) select * from alias ) SELECT AAA.alias1 as a1 FROM alias join alias1 as AAA on alias . a =AAA. a or alias . a !=AAA. a join alias2 on AAA. a =alias2. a or AAA. a != alias . a left join alias3 on alias3. a =alias2. a or alias3. a !=alias2. a ) tv2) --<< select a1 from CTE11 ) as SUBQ1), CTE2 as ( select a1 from ( -->> with CTE21 as ( select a1 from ( WITH alias as ( SELECT 1 as a ), alias1 as ( select alias . a as alias , AAA. a as alias1, AAA.b as a from alias join test.test_a AAA on alias . a !=AAA. a or alias . a !=AAA. a ), alias2 as ( with alias as ( select alias . a as alias , AAA. a as alias1, AAA.b as a from alias join test.test_a AAA on alias . a !=AAA. a or alias . a !=AAA. a ) select * from alias ), alias3 as ( with alias as ( select alias . a as alias , AAA. a as alias1, AAA. a from alias join alias2 AAA on alias . a !=AAA. a or alias . a !=AAA. a ) select * from alias ) SELECT AAA.alias1 as a1 FROM alias join alias1 as AAA on alias . a =AAA. a or alias . a !=AAA. a join alias2 on AAA. a =alias2. a or AAA. a != alias . a left join alias3 on alias3. a =alias2. a or alias3. a !=alias2. a ) tv2) --<< select a1 from CTE21 ) as SUBQ2) select * from CTE1 as T1 join CTE2 as T2 on T1.a1=T2.a1

when running the following query several times in a row:

with
CTE1 as (
	select a1 from ( 
-->>
		with CTE11 as (select a1 from (
								WITH 
									alias as (SELECT 1 as a),
									alias1 as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a),
									alias2 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias),
									alias3 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.a from alias join alias2 AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias)
								SELECT AAA.alias1 as a1 FROM alias join alias1 as AAA on alias.a=AAA.a or alias.a!=AAA.a join alias2 on AAA.a=alias2.a or AAA.a!=alias.a left join alias3 on alias3.a=alias2.a or alias3.a!=alias2.a
								) tv2) 
--<<
			select a1 from CTE11
	) as  SUBQ1),
CTE2 as (
	select a1 from ( 
-->>
		with CTE21 as (select a1 from (
								WITH 
									alias as (SELECT 1 as a),
									alias1 as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a),
									alias2 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.b as a from alias join test.test_a AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias),
									alias3 as (with alias as (select alias.a as alias, AAA.a as alias1, AAA.a from alias join alias2 AAA on alias.a!=AAA.a or alias.a!=AAA.a) select * from alias)
								SELECT AAA.alias1 as a1 FROM alias join alias1 as AAA on alias.a=AAA.a or alias.a!=AAA.a join alias2 on AAA.a=alias2.a or AAA.a!=alias.a left join alias3 on alias3.a=alias2.a or alias3.a!=alias2.a
								) tv2) 
--<<
			select a1 from CTE21
	) as  SUBQ2)  
select * from CTE1 as T1 join CTE2 as T2 on T1.a1=T2.a1

teiid returns the following stacktrace (from teiid 9.3.1):

2017-07-21 16:52:32,440 ERROR [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue73) oiBQAQPtTMe+ TEIID30019 Unexpected exception for request oiBQAQPtTMe+.15: java.lang.AssertionError
: ASSERTION FAILED: expected reference to be not null
        at org.teiid.core.util.Assertion.failed(Assertion.java:73)
        at org.teiid.core.util.Assertion.isNotNull(Assertion.java:100)
        at org.teiid.core.util.Assertion.isNotNull(Assertion.java:92)
        at org.teiid.common.buffer.TupleBuffer.getBatch(TupleBuffer.java:287)
        at org.teiid.dqp.internal.process.TupleSourceCache$CopyOnReadTupleSource.nextTuple(TupleSourceCache.java:103)
        at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:394)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:281)
        at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)
        at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)
        at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)
        at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92)
        at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:165)
        at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:235)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:281)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
        at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:211)
        at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:158)
        at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:227)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:281)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
        at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:211)
        at org.teiid.query.processor.relational.JoinNode.prefetch(JoinNode.java:255)
        at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:247)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:281)
        at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:102)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:281)
        at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:281)
        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:477)
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:349)
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
        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)

Dmitrii Pogorelov added a comment - 2017/07/24 8:34 AM

rhn-engineering-shawkins thx a lot for the fix.

Dmitrii Pogorelov added a comment - 2017/07/24 8:34 AM rhn-engineering-shawkins thx a lot for the fix.

Steven Hawkins added a comment - 2017/07/22 12:33 PM

Ensured that the command context is cloned for each execution of a subplan to not cause the parent tuplebuffer cache to be closed.

Steven Hawkins added a comment - 2017/07/22 12:33 PM Ensured that the command context is cloned for each execution of a subplan to not cause the parent tuplebuffer cache to be closed.

Steven Hawkins added a comment - 2017/07/21 6:19 PM

In the plan there are access nodes that end up with the same query. The sharing logic is allowing the cached buffer to be fully closed before all usage is complete.

Steven Hawkins added a comment - 2017/07/21 6:19 PM In the plan there are access nodes that end up with the same query. The sharing logic is allowing the cached buffer to be fully closed before all usage is complete.

Details

Description

Attachments

Easy Agile Planning Poker

Activity

Collapse comment: Dmitrii Pogorelov added a comment - 2017/07/24 8:34 AM

Expand comment: Dmitrii Pogorelov added a comment - 2017/07/24 8:34 AM

Collapse comment: Steven Hawkins added a comment - 2017/07/22 12:33 PM

Expand comment: Steven Hawkins added a comment - 2017/07/22 12:33 PM

Collapse comment: Steven Hawkins added a comment - 2017/07/21 6:19 PM

Expand comment: Steven Hawkins added a comment - 2017/07/21 6:19 PM

People

Dates