[JBoss JIRA] (TEIID-3378) Malformed query with SalesForce when Inner join used
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3378?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3378:
----------------------------------
Fix Version/s: 6.2-8.7.2
> Malformed query with SalesForce when Inner join used
> ----------------------------------------------------
>
> Key: TEIID-3378
> URL: https://issues.jboss.org/browse/TEIID-3378
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Affects Versions: 8.9
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Labels: alpha1
> Fix For: 6.2-8.7.2, 8.11
>
>
> There is "AND" clause missing after implicit "!= NULL" call
> {code}
> USER COMMAND:
> SELECT A.Name, O.Id FROM importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O WHERE (A.Id = O.AccountId) AND (A.OracleAccountNumber__c = '540840')
> ----------------------------------------------------------------------------
> OPTIMIZE:
> SELECT A.Name, O.Id FROM importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O WHERE (A.Id = O.AccountId) AND (A.OracleAccountNumber__c = '540840')
> ----------------------------------------------------------------------------
> GENERATE CANONICAL:
> SELECT A.Name, O.Id FROM importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O WHERE (A.Id = O.AccountId) AND (A.OracleAccountNumber__c = '540840')
> CANONICAL PLAN:
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id]})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840'})
> Select(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={SELECT_CRITERIA=A.Id = O.AccountId})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[]})
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING PlaceAccess
> AFTER:
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id]})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840'})
> Select(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={SELECT_CRITERIA=A.Id = O.AccountId})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[]})
> Access(groups=[importVDBSrcModel.Account AS A], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000})
> Source(groups=[importVDBSrcModel.Account AS A])
> Access(groups=[importVDBSrcModel.Opportunity AS O], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000})
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING PushSelectCriteria
> AFTER:
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id]})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId]})
> Access(groups=[importVDBSrcModel.Account AS A], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840'})
> Source(groups=[importVDBSrcModel.Account AS A])
> Access(groups=[importVDBSrcModel.Opportunity AS O])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING PushNonJoinCriteria
> AFTER:
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId]})
> Access(groups=[importVDBSrcModel.Account AS A])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Access(groups=[importVDBSrcModel.Opportunity AS O])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING CleanCriteria
> AFTER:
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id], OUTPUT_COLS=null})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId], OUTPUT_COLS=null})
> Access(groups=[importVDBSrcModel.Account AS A], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, OUTPUT_COLS=null})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840', OUTPUT_COLS=null})
> Source(groups=[importVDBSrcModel.Account AS A], props={OUTPUT_COLS=null})
> Access(groups=[importVDBSrcModel.Opportunity AS O], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, OUTPUT_COLS=null})
> Source(groups=[importVDBSrcModel.Opportunity AS O], props={OUTPUT_COLS=null})
> ============================================================================
> EXECUTING RaiseAccess
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null})
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id], OUTPUT_COLS=null})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId], OUTPUT_COLS=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840', OUTPUT_COLS=null})
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O], props={OUTPUT_COLS=null})
> ============================================================================
> EXECUTING CopyCriteria
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId], OUTPUT_COLS=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, IS_COPIED=true})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840', OUTPUT_COLS=null, IS_COPIED=true})
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING CleanCriteria
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, OUTPUT_COLS=null, CONFORMED_SOURCES=null})
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id], OUTPUT_COLS=null})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId], OUTPUT_COLS=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, IS_COPIED=true})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840', OUTPUT_COLS=null, IS_COPIED=true})
> Source(groups=[importVDBSrcModel.Account AS A], props={OUTPUT_COLS=null})
> Source(groups=[importVDBSrcModel.Opportunity AS O], props={OUTPUT_COLS=null})
> ============================================================================
> EXECUTING PlanJoins
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING PushSelectCriteria
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840', OUTPUT_COLS=null, IS_COPIED=true})
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING RaiseAccess
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING ChooseJoinStrategy
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING ChooseDependent
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING AssignOutputElements
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, OUTPUT_COLS=[A.Name, O.Id], CONFORMED_SOURCES=null})
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id], OUTPUT_COLS=[A.Name, O.Id]})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId], OUTPUT_COLS=[A.Name, O.Id], MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, IS_COPIED=true})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840', OUTPUT_COLS=[A.Id, A.Name], IS_COPIED=true})
> Source(groups=[importVDBSrcModel.Account AS A], props={OUTPUT_COLS=[A.OracleAccountNumber__c, A.Id, A.Name]})
> Source(groups=[importVDBSrcModel.Opportunity AS O], props={OUTPUT_COLS=[O.AccountId, O.Id]})
> ============================================================================
> EXECUTING CalculateCost
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, OUTPUT_COLS=[A.Name, O.Id], CONFORMED_SOURCES=null, EST_CARDINALITY=-1.0})
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={PROJECT_COLS=[A.Name, O.Id], OUTPUT_COLS=[A.Name, O.Id], EST_CARDINALITY=-1.0})
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[A.Id = O.AccountId], OUTPUT_COLS=[A.Name, O.Id], MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, IS_COPIED=true, EST_CARDINALITY=-1.0})
> Select(groups=[importVDBSrcModel.Account AS A], props={SELECT_CRITERIA=A.OracleAccountNumber__c = '540840', OUTPUT_COLS=[A.Id, A.Name], IS_COPIED=true, EST_CARDINALITY=-1.0})
> Source(groups=[importVDBSrcModel.Account AS A], props={OUTPUT_COLS=[A.OracleAccountNumber__c, A.Id, A.Name], EST_COL_STATS={A.OracleAccountNumber__c=[-1.0, -1.0], A.Id=[-1.0, -1.0], A.Name=[-1.0, -1.0]}, EST_CARDINALITY=-1.0})
> Source(groups=[importVDBSrcModel.Opportunity AS O], props={OUTPUT_COLS=[O.AccountId, O.Id], EST_COL_STATS={O.AccountId=[-1.0, -1.0], O.Id=[-1.0, -1.0]}, EST_CARDINALITY=-1.0})
> ============================================================================
> EXECUTING ImplementJoinStrategy
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING MergeCriteria
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING PlanSorts
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Project(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Join(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O])
> Select(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Account AS A])
> Source(groups=[importVDBSrcModel.Opportunity AS O])
> ============================================================================
> EXECUTING CollapseSource
> AFTER:
> Access(groups=[importVDBSrcModel.Account AS A, importVDBSrcModel.Opportunity AS O], props={SOURCE_HINT=null, MODEL_ID=Schema name=importVDBSrcModel, nameInSource=null, uuid=tid:171d0fcff042-1eabba54-00000000, OUTPUT_COLS=[A.Name, O.Id], CONFORMED_SOURCES=null, EST_CARDINALITY=-1.0, ATOMIC_REQUEST=SELECT A.Name, O.Id FROM importVDBSrcModel.Account AS A INNER JOIN importVDBSrcModel.Opportunity AS O ON A.Id = O.AccountId WHERE A.OracleAccountNumber__c = '540840'})
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
> PROCESS PLAN =
> AccessNode(0) output=[A.Name, O.Id] SELECT importVDBSrcModel.Account.Name, importVDBSrcModel.Opportunity.Id FROM importVDBSrcModel.Account INNER JOIN importVDBSrcModel.Opportunity ON importVDBSrcModel.Account.Id = importVDBSrcModel.Opportunity.AccountId WHERE importVDBSrcModel.Account.OracleAccountNumber__c = '540840'
> ============================================================================
> ----------------------------------------------------------------------------
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[A.Name, O.Id] SELECT importVDBSrcModel.Account.Name, importVDBSrcModel.Opportunity.Id FROM importVDBSrcModel.Account INNER JOIN importVDBSrcModel.Opportunity ON importVDBSrcModel.Account.Id = importVDBSrcModel.Opportunity.AccountId WHERE importVDBSrcModel.Account.OracleAccountNumber__c = '540840'
> ============================================================================
> 11:14:08,728 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue4) Connector worker process failed for atomic-request=KrZqiapoESB1.0.0.1: org.teiid.translator.TranslatorException: com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY:
> WHERE Opportunity.AccountId != NULLAccount.OracleAccountNumber__c
> ^
> ERROR at Row:1:Column:84
> Bind variables only allowed in Apex code
> at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:162)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_65]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.redhat-3.jar:8.7.1.redhat-3]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_65]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_65]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_65]
> Caused by: javax.resource.ResourceException: com.sforce.soap.partner.MalformedQueryFault: MALFORMED_QUERY:
> WHERE Opportunity.AccountId != NULLAccount.OracleAccountNumber__c
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 9 months
[JBoss JIRA] (TEIID-3296) Some criteria are missing when indexes are used on the source tables
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3296?page=com.atlassian.jira.plugin... ]
Steven Hawkins reopened TEIID-3296:
-----------------------------------
> Some criteria are missing when indexes are used on the source tables
> --------------------------------------------------------------------
>
> Key: TEIID-3296
> URL: https://issues.jboss.org/browse/TEIID-3296
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.2
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.10
>
>
> The following two tables are defined in two different datasources (PostgreSQL and MySQL):
> {code:sql}
> CREATE TABLE "pg"."tx1"
> (
> a varchar(2147483647),
> b varchar(2147483647),
> c integer,
> d integer,
> e integer
> );
> CREATE INDEX tx1_b ON "pg"."tx1"(b);
> CREATE INDEX tx1_a ON "pg"."tx1"(a);
> CREATE INDEX tx1_e ON "pg"."tx1"(e);
> CREATE INDEX tx1_d ON "pg"."tx1"(d);
> CREATE INDEX tx1_c ON "pg"."tx1"(c);
>
> CREATE TABLE "my"."tx2"
> (
> a integer,
> b integer,
> c integer,
> d integer,
> e integer
> );
> insert into tx1 VALUES(1,1,1,1,1) ;
> insert into tx1 VALUES(1,2,2,2,2) ;
> insert into tx1 VALUES(1,2,3,3,3) ;
> insert into tx1 VALUES(1,2,3,4,4) ;
> insert into tx1 VALUES(1,2,3,4,5) ;
> insert into tx2 VALUES(1,2,3,4,5) ;
> {code}
> Running the following query:
> {code:sql}
> select tx1.* from pg.tx1 as tx1 join my.tx2 as tx2 on tx1.a=tx2.a and tx1.b=tx2.b and tx1.c=tx2.c and tx1.d=tx2.d and tx1.e!=tx2.e;
> {code}
> a wrong result is returned:
> ||a||b||c||d||e||
> |1|2|2|2|2|
> |1|2|3|3|3|
> |1|2|3|4|4|
> If indexes are removed from the source table, the expected result is correctly returned:
> ||a||b||c||d||e||
> |1|2|3|4|4|
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 9 months
[JBoss JIRA] (TEIID-3296) Some criteria are missing when indexes are used on the source tables
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3296?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3296:
----------------------------------
Fix Version/s: 6.2-8.7.2
> Some criteria are missing when indexes are used on the source tables
> --------------------------------------------------------------------
>
> Key: TEIID-3296
> URL: https://issues.jboss.org/browse/TEIID-3296
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.2
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 8.10, 6.2-8.7.2
>
>
> The following two tables are defined in two different datasources (PostgreSQL and MySQL):
> {code:sql}
> CREATE TABLE "pg"."tx1"
> (
> a varchar(2147483647),
> b varchar(2147483647),
> c integer,
> d integer,
> e integer
> );
> CREATE INDEX tx1_b ON "pg"."tx1"(b);
> CREATE INDEX tx1_a ON "pg"."tx1"(a);
> CREATE INDEX tx1_e ON "pg"."tx1"(e);
> CREATE INDEX tx1_d ON "pg"."tx1"(d);
> CREATE INDEX tx1_c ON "pg"."tx1"(c);
>
> CREATE TABLE "my"."tx2"
> (
> a integer,
> b integer,
> c integer,
> d integer,
> e integer
> );
> insert into tx1 VALUES(1,1,1,1,1) ;
> insert into tx1 VALUES(1,2,2,2,2) ;
> insert into tx1 VALUES(1,2,3,3,3) ;
> insert into tx1 VALUES(1,2,3,4,4) ;
> insert into tx1 VALUES(1,2,3,4,5) ;
> insert into tx2 VALUES(1,2,3,4,5) ;
> {code}
> Running the following query:
> {code:sql}
> select tx1.* from pg.tx1 as tx1 join my.tx2 as tx2 on tx1.a=tx2.a and tx1.b=tx2.b and tx1.c=tx2.c and tx1.d=tx2.d and tx1.e!=tx2.e;
> {code}
> a wrong result is returned:
> ||a||b||c||d||e||
> |1|2|2|2|2|
> |1|2|3|3|3|
> |1|2|3|4|4|
> If indexes are removed from the source table, the expected result is correctly returned:
> ||a||b||c||d||e||
> |1|2|3|4|4|
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 9 months
[JBoss JIRA] (TEIID-3296) Some criteria are missing when indexes are used on the source tables
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3296?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3296.
---------------------------------
Resolution: Done
> Some criteria are missing when indexes are used on the source tables
> --------------------------------------------------------------------
>
> Key: TEIID-3296
> URL: https://issues.jboss.org/browse/TEIID-3296
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.2
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 6.2-8.7.2, 8.10
>
>
> The following two tables are defined in two different datasources (PostgreSQL and MySQL):
> {code:sql}
> CREATE TABLE "pg"."tx1"
> (
> a varchar(2147483647),
> b varchar(2147483647),
> c integer,
> d integer,
> e integer
> );
> CREATE INDEX tx1_b ON "pg"."tx1"(b);
> CREATE INDEX tx1_a ON "pg"."tx1"(a);
> CREATE INDEX tx1_e ON "pg"."tx1"(e);
> CREATE INDEX tx1_d ON "pg"."tx1"(d);
> CREATE INDEX tx1_c ON "pg"."tx1"(c);
>
> CREATE TABLE "my"."tx2"
> (
> a integer,
> b integer,
> c integer,
> d integer,
> e integer
> );
> insert into tx1 VALUES(1,1,1,1,1) ;
> insert into tx1 VALUES(1,2,2,2,2) ;
> insert into tx1 VALUES(1,2,3,3,3) ;
> insert into tx1 VALUES(1,2,3,4,4) ;
> insert into tx1 VALUES(1,2,3,4,5) ;
> insert into tx2 VALUES(1,2,3,4,5) ;
> {code}
> Running the following query:
> {code:sql}
> select tx1.* from pg.tx1 as tx1 join my.tx2 as tx2 on tx1.a=tx2.a and tx1.b=tx2.b and tx1.c=tx2.c and tx1.d=tx2.d and tx1.e!=tx2.e;
> {code}
> a wrong result is returned:
> ||a||b||c||d||e||
> |1|2|2|2|2|
> |1|2|3|3|3|
> |1|2|3|4|4|
> If indexes are removed from the source table, the expected result is correctly returned:
> ||a||b||c||d||e||
> |1|2|3|4|4|
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 9 months
[JBoss JIRA] (TEIID-3404) Teiid DatabaseAppender JPA perisist Command log consume high CPU
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3404?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3404:
---------------------------------------
> From the code [1], DatabaseAppender does a JNDI lookup on every call, then EJB/JPA persist the log entity, probably we "wouldn't do like that".
Presumably the logging configuration is such that this is an asynch appender and that only command/audit log entries are routed to it. Any other configuration would be highly problematic.
> One question I first thought is why we need persist log to database?
This is only for the Teiid audit/command log events, not all logs. I believe this was a customer/product management feature request.
> Do we need consider transaction per log or per request?
There will be somewhere around 2 + 2*(source queries) command logs per user request. There will typically only be one audit log entry.
> I also find the default production install use the DEBUG level log, does this reasonable?
The command and audit log events are only logged at the debug and trace level. So anything higher than debug would see no events.
> can we use H2 in production?
H2 is not required. The rationale I believe for using JPA was that the logger would be database agnostic.
> I thought to enhance this issue is use JDBC/batch the insertions to replace EJB/JPA, do you think this is a feasible way ?
Queuing/batching can be implemented either way. Since the asynch appender approach already is maintaining an event queue it would make more sense to batch instead. As for using straight JDBC vs JPA, I'd let Ramesh weigh in there.
> Teiid DatabaseAppender JPA perisist Command log consume high CPU
> ----------------------------------------------------------------
>
> Key: TEIID-3404
> URL: https://issues.jboss.org/browse/TEIID-3404
> Project: Teiid
> Issue Type: Bug
> Components: Tools
> Affects Versions: 8.7.1
> Environment: * DV 6.1
> * dynamicvdb-datafederation quickstart
> Reporter: Kylin Soong
> Assignee: Kylin Soong
>
> I have 4 - 5 concurrent client query to Portfolio VDB, this cause log DatabaseAppender JPA perisist Command log thread consume high CPU. In my test, one log threads always swallow high CPU, if the concurrent client query more than 10, it swallow 100% cpu.
> [1] have detailed reproduce steps, including thread dumps.
> [1] https://github.com/kylinsoong/teiid-samples/tree/master/command-logging-h...
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 9 months
[JBoss JIRA] (TEIID-3404) Teiid DatabaseAppender JPA perisist Command log consume high CPU
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-3404?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-3404:
------------------------------------
>From the code [1], DatabaseAppender does a JNDI lookup on every call, then EJB/JPA persist the log entity, probably we "wouldn't do like that".
One question I first thought is why we need persist log to database? writing the log entries to a database seems unusual, If we have to do this, and the log entries are in great number, probably we need take performance inconsideration. Do we need consider transaction per log or per request? I also find the default production install use the DEBUG level log, does this reasonable?
Another approach is H2, I am not sure H2 can used in production, but from thread dump trace, there are pause in H2 driver, can we use H2 in production?
I thought to enhance this issue is use JDBC/batch the insertions to replace EJB/JPA, do you think this is a feasible way ?
[1] https://github.com/teiid/teiid-extensions/blob/master/database-logging-ap...
> Teiid DatabaseAppender JPA perisist Command log consume high CPU
> ----------------------------------------------------------------
>
> Key: TEIID-3404
> URL: https://issues.jboss.org/browse/TEIID-3404
> Project: Teiid
> Issue Type: Bug
> Components: Tools
> Affects Versions: 8.7.1
> Environment: * DV 6.1
> * dynamicvdb-datafederation quickstart
> Reporter: Kylin Soong
> Assignee: Kylin Soong
>
> I have 4 - 5 concurrent client query to Portfolio VDB, this cause log DatabaseAppender JPA perisist Command log thread consume high CPU. In my test, one log threads always swallow high CPU, if the concurrent client query more than 10, it swallow 100% cpu.
> [1] have detailed reproduce steps, including thread dumps.
> [1] https://github.com/kylinsoong/teiid-samples/tree/master/command-logging-h...
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 9 months