[JBoss JIRA] (TEIID-3312) Subquery field giving a group by error
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3312?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3312.
---------------------------------
> Subquery field giving a group by error
> --------------------------------------
>
> Key: TEIID-3312
> URL: https://issues.jboss.org/browse/TEIID-3312
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> Hello,
> I'm using Teiid 8.9.0.Alpha2, Mysql 5.7 and mysql5 Translator
> I have query, in which i'm using a subquery field containing a criteria using a date field coming from the main query YEAR("activities"."ActivityDate").
> Teiid is forcing me to put "activities"."ActivityDate" in main query group by, while i want to group my data by YEAR("activities"."ActivityDate").
> I'm getting the following teiid error :
> TEIID30020 Processing exception for request fOwqPyy91bbL.0 'TEIID30492 [activities.ActivityDate] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.'.
> Here is the query:
> select COUNT( distinct "activities"."ActEmpName") as "activities_ActEmpName"
> ,YEAR(activities.ActivityDate) as "activities_ActivityDate"
> ,(select COUNT( distinct "employees_sub"."EmpFullName") as "employees_EmpFullName"
> from "TrackerModel".tracker_datawarehouse."employees" "employees_sub"
> where ( YEAR(employees_sub.EmpHireDate) <= YEAR("activities"."ActivityDate"))
> LIMIT 0 , 1) as "SubQuery Employees"
> from "TrackerModel".tracker_datawarehouse."activities" "activities"
> group by YEAR(activities.ActivityDate)
> LIMIT 0 , 10
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3275) remaining work should always be canceled
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3275?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3275.
---------------------------------
> remaining work should always be canceled
> ----------------------------------------
>
> Key: TEIID-3275
> URL: https://issues.jboss.org/browse/TEIID-3275
> Project: Teiid
> Issue Type: Quality Risk
> Components: JDBC Connector, Query Engine
> Affects Versions: 7.0
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> When a result is partially read we typically send a cancel to source queries that remain open for faster cleanup. Depending upon the plan and the usage of max rows this may not happen in all circumstances. We should ensure that cancel is always called on partially read results.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3272) JDBC Translator | Segregation of batch execution and commit mode
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3272?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3272.
---------------------------------
> JDBC Translator | Segregation of batch execution and commit mode
> -----------------------------------------------------------------
>
> Key: TEIID-3272
> URL: https://issues.jboss.org/browse/TEIID-3272
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Shiveeta Mattoo
> Assignee: Steven Hawkins
> Labels: Batch-Commit, JDBC-Transator
> Attachments: batch1.png
>
>
> In JDBC Translator, the batch execution and commit are coupled together. Hence, we after every batch execution, a commit is executed.
> This enhancement is for providing an ability to support the commit after a configurable 'x' no. of batches has completed, instead of an automatic commit after every batch.
> The no of batches after which a commit is desired should be a configurable option.
>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3285) Optional Left Join deleted while it has a condition in the where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3285?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3285.
---------------------------------
> Optional Left Join deleted while it has a condition in the where clause
> -----------------------------------------------------------------------
>
> Key: TEIID-3285
> URL: https://issues.jboss.org/browse/TEIID-3285
> Project: Teiid
> Issue Type: Bug
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I'm using Teiid 8.9.0.Alpha2
> I'm executing via Teiid the following query in which I have an optional left join with a where condition related to it.
> The JDBC query executed by Teiid is removing the optional join and the corresponding where condition.
> SELECT AVG(TicketViewRed.CalculatedField13) AS TicketViewRed_CalculatedFiel,
> TicketViewRed.jiraissue_PROJECT AS TicketViewRed_jiraissue_PROJ11
> FROM implifyBusinessModel.TicketViewRed TicketViewRed
> LEFT JOIN /* optional */ implifyBusinessModel.DimDate DimDate ON TicketViewRed.jiraissue_RESOLUTIONDATE = DimDate.DimDate_iTn_241214_Date
> WHERE (((TicketViewRed.jiraissue_PROJECT IN (10700, 11300))))
> AND ((DimDate.DimDate_iTn_241214_Date BETWEEN PARSETIMESTAMP('2014-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')
> AND PARSETIMESTAMP('2014-09-02 00:00:00', 'yyyy-MM-dd HH:mm:ss')))
> GROUP BY TicketViewRed.jiraissue_PROJECT
> ORDER BY TicketViewRed.jiraissue_PROJECT DESC
> JDBC query executed by Teiid
> SELECT AVG(g_0.`CalculatedField13`) AS c_0, g_0.`jiraissue_PROJECT` AS c_1
> FROM `implify_view`.`TicketViewRed` AS g_0
> WHERE g_0.`jiraissue_PROJECT` IN (10700, 11300)
> GROUP BY g_0.`jiraissue_PROJECT` ORDER BY c_1 DESC
> And here is the corresponding execution plan:
> <?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Output Columns"><value>TicketViewRed_CalculatedFiel (double)</value><value>TicketViewRed_jiraissue_PROJ11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 9</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT AVG(g_0.CalculatedField13) AS c_0, g_0.jiraissue_PROJECT AS c_1 FROM implify_viewModel.implify_view.TicketViewRed AS g_0 WHERE g_0.jiraissue_PROJECT IN (10700, 11300) GROUP BY g_0.jiraissue_PROJECT ORDER BY c_1 DESC</value></property><property name="Model Name"><value>implify_viewModel</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3276) Data result not correct when joinning subquery over a date field
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3276?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3276.
---------------------------------
> Data result not correct when joinning subquery over a date field
> ----------------------------------------------------------------
>
> Key: TEIID-3276
> URL: https://issues.jboss.org/browse/TEIID-3276
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.7
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> Hello,
> I'm using Teiid 8.9.0 To execute the below query and the execution is done without the problem; the issue is that I'm getting different results when executing the same query over mysql
> The original query is much bigger but i tried to simplify it to isolate the problem.
> SELECT DISTINCT
> WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet,
> sessionid_Sub.contactcalldetail_sessionid
> AS contactcalldetail_sessionid
> FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail
> LEFT JOIN /* optional */
> (SELECT COUNT(DISTINCT contactcalldetail_sub.sessionid)
> AS contactcalldetail_sessionid,
> WEEK(contactcalldetail_sub.startdatetime)
> AS contactcalldetail_startdatet
> FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail contactcalldetail_sub
> WHERE (((contactcalldetail_sub.contacttype IN (1))))
> AND ((((contactcalldetail_sub.connecttime >= 300))))
> GROUP BY WEEK(contactcalldetail_sub.startdatetime)) sessionid_Sub
> ON (WEEK(contactcalldetail.startdatetime) =
> sessionid_Sub.contactcalldetail_startdatet)
> WHERE (((contactcalldetail.contacttype IN (1))))
> LIMIT 0, 10
> Here is Teiid execution plan :
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 1</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 12</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 3</value><value>Node Blocks: 2</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 13</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 11</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>expr (integer)</value></property><property name="Statistics"><value>Node Output Rows: 4096</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 7</value><value>Node Blocks: 5</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.startdatetime AS c_0, WEEK(g_0.startdatetime) AS c_1 FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE g_0.contacttype = 1 ORDER BY c_1</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>contactcalldetail_startdatet (integer)</value><value>contactcalldetail_sessionid (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="GroupingNode"><property name="Output Columns"><value>gcol0 (integer)</value><value>agg0 (integer)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 14</value><value>Node Cumulative Next Batch Process Time: 8</value><value>Node Cumulative Process Time: 15</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>startdatetime (timestamp)</value><value>sessionid (long)</value></property><property name="Statistics"><value>Node Output Rows: 772</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 9</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.startdatetime, g_0.sessionid FROM db_cra_8F_VDB_DSModel.db_cra.contactcalldetail AS g_0 WHERE (g_0.contacttype = 1) AND (g_0.connecttime >= 300)</value></property><property name="Model Name"><value>db_cra_8F_VDB_DSModel</value></property></node></property><property name="Grouping Columns"><value>WEEK(contactcalldetail_sub.startdatetime)</value></property><property name="Sort Mode"><value>false</value></property></node></property><property name="Select Columns"><value>anon_grp0.gcol0 AS contactcalldetail_startdatet</value><value>anon_grp0.agg0 AS contactcalldetail_sessionid</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>WEEK(contactcalldetail.startdatetime)=sessionid_Sub.contactcalldetail_startdatet</value></property></node></property><property name="Select Columns"><value>WEEK(contactcalldetail.startdatetime) AS contactcalldetail_startdatet</value><value>sessionid_Sub.contactcalldetail_sessionid AS contactcalldetail_sessionid</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>
> Here are the 2 JDBC queries executed by Teiid:
> SELECT DISTINCT g_0.`startdatetime` AS c_0, week(g_0.`startdatetime`) AS c_1 FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 ORDER BY c_1
> SELECT g_0.`startdatetime`, g_0.`sessionid` FROM `db_cra`.`contactcalldetail` AS g_0 WHERE g_0.`contacttype` = 1 AND g_0.`connecttime` >= 300
> Teiid query execution result is :
> week countsession
> 36 null
> 37 328
> 38 384
> mysql execution result:
> week countsession
> 35 328
> 36 384
> 37 60
> You can notice that the week is incremented by one and the count result is scrambled
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3142) Vanilla Hive sorting issue
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3142?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3142.
---------------------------------
> Vanilla Hive sorting issue
> --------------------------
>
> Key: TEIID-3142
> URL: https://issues.jboss.org/browse/TEIID-3142
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Affects Versions: 8.7.1
> Reporter: Filip Nguyen
> Assignee: Steven Hawkins
>
> It seems that Vanilla Apache Hive 0.13 sorts in a yet another different way than what we expect and also differently from Cloudera Imapla (another Hive flavor).
> SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA order by BQT1.SmallA.StringNum
> 1: -1
> 2: -10
> 3: -11
> 4: -12
> 5: -13
> 6: -14
> 7: -15
> 8: -16
> 9: -17
> 10: -18
> 11: -19
> 12: -2
> 13: -20
> 14: -21
> 15: -22
> 16: -24
> 17: -3
> 18: -4
> 19: -5
> 20: -6
> 21: -8
> 22: -9
> 23: 0
> 24: 1
> 25: 10
> 26: 11
> 27: 12
> 28: 13
> 29: 14
> 30: 15
> 31: 16
> 32: 17
> 33: 18
> 34: 19
> 35: 2
> 36: 20
> 37: 21
> 38: 22
> 39: 23
> 40: 24
> 41: 3
> 42: 4
> 43: 5
> 44: 6
> 45: 7
> 46: 8
> 47: null
> 48: null
> 49: null
> 50: null
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months
[JBoss JIRA] (TEIID-3317) MongoDB: group by doesn't work with MongoDB 2.6.3
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3317?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-3317.
---------------------------------
> MongoDB: group by doesn't work with MongoDB 2.6.3
> --------------------------------------------------
>
> Key: TEIID-3317
> URL: https://issues.jboss.org/browse/TEIID-3317
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.9.1
> Environment: Teiid 8.9.1 with MongoDB version: 2.6.3 using MongoDB Driver mongo-java-driver-2.12.3.jar
> Reporter: Ivan Chan
> Assignee: Ramesh Reddy
> Labels: Teiid
>
> Running group by sql through exception:
> select "FirstName"
> from "MongoDB_Array_263"."TeiidArray"
> group by "FirstName"
> order by "FirstName"
> limit 1000
> Exception:
> Caused by: java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
> at java.util.ArrayList.rangeCheck(ArrayList.java:604)
> at java.util.ArrayList.get(ArrayList.java:382)
> at org.teiid.translator.mongodb.MongoDBQueryExecution.next(MongoDBQueryExecution.java:132)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:386)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:204)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:601)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)
> at $Proxy182.more(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
> at java.util.concurrent.FutureTask.run(FutureTask.java:166)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274)
> 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:1110)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 10 months