[JBoss JIRA] (TEIID-3267) OPTION NOCACHE causes ConcurrentModificationException
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3267?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3267:
------------------------------------------------
jolee(a)redhat.com changed the Status of [bug 1175653|https://bugzilla.redhat.com/show_bug.cgi?id=1175653] from NEW to MODIFIED
> OPTION NOCACHE causes ConcurrentModificationException
> -----------------------------------------------------
>
> Key: TEIID-3267
> URL: https://issues.jboss.org/browse/TEIID-3267
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4.3, 8.7.1, 8.9
> Reporter: Hisanobu Okuda
> Assignee: Steven Hawkins
> Fix For: 8.7.1, 8.10
>
> Attachments: test.csv, test.vdb
>
>
> I have the following virtual procedure.
> {code}
> CREATE VIRTUAL PROCEDURE
> BEGIN
> DECLARE string VARIABLES.strSql = 'select v1.id, v2.ccc2 from vvv1.vvv as v1, vvv2.vvv as v2 where v1.id=v2.id option nocache v1';
> EXECUTE IMMEDIATE VARIABLES.strSql AS id string, name string;
> END
> {code}
> When I specify no argument for 'option nocache', it works. But, when I specify a view name as an argument for 'option nocache', it throws ConcurrentModificationException.
> {code}
> 17:34:04,285 ERROR [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue0) TEIID30019 Unexpected exception for request nSVB2nm4gO3M.0: java.util.ConcurrentModificationException
> at java.util.ArrayList$Itr.checkForComodification(ArrayList.java:859) [rt.jar:1.7.0_45]
> at java.util.ArrayList$Itr.next(ArrayList.java:831) [rt.jar:1.7.0_45]
> at org.teiid.query.optimizer.relational.RelationalPlanner.generatePlan(RelationalPlanner.java:609) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.optimizer.relational.RelationalPlanner.addNestedCommand(RelationalPlanner.java:1173) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.optimizer.relational.RelationalPlanner.buildTree(RelationalPlanner.java:1071) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.optimizer.relational.RelationalPlanner.createQueryPlan(RelationalPlanner.java:904) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.optimizer.relational.RelationalPlanner.createQueryPlan(RelationalPlanner.java:863) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.optimizer.relational.RelationalPlanner.generatePlan(RelationalPlanner.java:619) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:226) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:186) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:356) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:283) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:257) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:157) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:139) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.4.1-redhat-7.hokuda.jar:8.4.1-redhat-7]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_45]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_45]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_45]
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years
[JBoss JIRA] (TEIID-3276) Data result not correct when joinning subquery over a date field
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3276?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3276:
----------------------------------
FYI I was using is 8.9.0.Alpha2, I migrated to 8.9.1 and retried it and still getting the same results.
I have difference in results between teiid and mysql in other cases too, the thing in common with this issue is the join subquery on date field.
> 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
> Fix For: 8.10
>
>
> 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)
10 years
[JBoss JIRA] (TEIID-3279) SOLR: Error when date, time or timestamp literal is in where clause
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3279?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-3279:
-------------------------------------
So, there are two issues
* Convert time and date to timestamp to support them
* Convert all the timestamps to UTC
So, I made the changes here https://github.com/teiid/teiid/commit/733b690f9a5139c6f8210954b7538c31d99...
Any other suggestions?
> SOLR: Error when date,time or timestamp literal is in where clause
> ------------------------------------------------------------------
>
> Key: TEIID-3279
> URL: https://issues.jboss.org/browse/TEIID-3279
> Project: Teiid
> Issue Type: Bug
> Components: Connector API
> Affects Versions: 8.7
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
>
> Query fails when time,date or timestamp literal is used in where clause.
> Examples:
> Query:
> {code}select intkey from smalla where timevalue = '11:30:20'{code}
> Error:
> {code}
> '11:30:20'org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where datevalue = '2002-02-02'{code}
> Error:
> {code}
> org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where timestampvalue = '2000-01-01 00:00:04'{code}
> Error:
> {code} org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'2000-01-01T00-00-04'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> SOLR supports only timestamps (YYYY-mm-ddThh:mm:ssZ) so date and time types should be converted into timestamp before a query is send to SOLR.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years
[JBoss JIRA] (TEIID-3279) SOLR: Error when date, time or timestamp literal is in where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3279?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3279:
---------------------------------------
It seems fine to support them as we can convert them easily to timestamp. We just have to be careful with the Z/UTC representation. There is already handling for this with XMLSystemFunctions.convertToAtomicValue(timestamp).getStringValue(), or you can adjust the timezone for output to GMT.
> SOLR: Error when date,time or timestamp literal is in where clause
> ------------------------------------------------------------------
>
> Key: TEIID-3279
> URL: https://issues.jboss.org/browse/TEIID-3279
> Project: Teiid
> Issue Type: Bug
> Components: Connector API
> Affects Versions: 8.7
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
>
> Query fails when time,date or timestamp literal is used in where clause.
> Examples:
> Query:
> {code}select intkey from smalla where timevalue = '11:30:20'{code}
> Error:
> {code}
> '11:30:20'org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where datevalue = '2002-02-02'{code}
> Error:
> {code}
> org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where timestampvalue = '2000-01-01 00:00:04'{code}
> Error:
> {code} org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'2000-01-01T00-00-04'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> SOLR supports only timestamps (YYYY-mm-ddThh:mm:ssZ) so date and time types should be converted into timestamp before a query is send to SOLR.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years
[JBoss JIRA] (TEIID-3279) SOLR: Error when date, time or timestamp literal is in where clause
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3279?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-3279:
-------------------------------------
[1] specifies
Solr's TrieDateField (and deprecated DateField) represents a point in time with millisecond precision. The
format used is a restricted form of the canonical representation of dateTime in the XML Schema specification:
{code}
YYYY-MM-DDThh:mm:ssZ
{code}
* YYYY is the year.
* MM is the month.
* DD is the day of the month.
* hh is the hour of the day as on a 24-hour clock.
* mm is minutes.
* ss is seconds.
* Z is a literal 'Z' character indicating that this string representation of the date is in UTC
[http://www.interior-dsgn.com/apache/lucene/solr/ref-guide/apache-solr-ref...]
So, should we not support these types or convert date and time to timestamps?
> SOLR: Error when date,time or timestamp literal is in where clause
> ------------------------------------------------------------------
>
> Key: TEIID-3279
> URL: https://issues.jboss.org/browse/TEIID-3279
> Project: Teiid
> Issue Type: Bug
> Components: Connector API
> Affects Versions: 8.7
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
>
> Query fails when time,date or timestamp literal is used in where clause.
> Examples:
> Query:
> {code}select intkey from smalla where timevalue = '11:30:20'{code}
> Error:
> {code}
> '11:30:20'org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where datevalue = '2002-02-02'{code}
> Error:
> {code}
> org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where timestampvalue = '2000-01-01 00:00:04'{code}
> Error:
> {code} org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'2000-01-01T00-00-04'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> SOLR supports only timestamps (YYYY-mm-ddThh:mm:ssZ) so date and time types should be converted into timestamp before a query is send to SOLR.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years
[JBoss JIRA] (TEIID-3279) SOLR: Error when date, time or timestamp literal is in where clause
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3279?page=com.atlassian.jira.plugin... ]
Ramesh Reddy reassigned TEIID-3279:
-----------------------------------
Assignee: Ramesh Reddy (was: Steven Hawkins)
> SOLR: Error when date,time or timestamp literal is in where clause
> ------------------------------------------------------------------
>
> Key: TEIID-3279
> URL: https://issues.jboss.org/browse/TEIID-3279
> Project: Teiid
> Issue Type: Bug
> Components: Connector API
> Affects Versions: 8.7
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
>
> Query fails when time,date or timestamp literal is used in where clause.
> Examples:
> Query:
> {code}select intkey from smalla where timevalue = '11:30:20'{code}
> Error:
> {code}
> '11:30:20'org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where datevalue = '2002-02-02'{code}
> Error:
> {code}
> org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where timestampvalue = '2000-01-01 00:00:04'{code}
> Error:
> {code} org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'2000-01-01T00-00-04'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> SOLR supports only timestamps (YYYY-mm-ddThh:mm:ssZ) so date and time types should be converted into timestamp before a query is send to SOLR.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years
[JBoss JIRA] (TEIID-3279) SOLR: Error when date, time or timestamp literal is in where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3279?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3279:
---------------------------------------
Ramesh do you want this one or should I take a crack at it?
> SOLR: Error when date,time or timestamp literal is in where clause
> ------------------------------------------------------------------
>
> Key: TEIID-3279
> URL: https://issues.jboss.org/browse/TEIID-3279
> Project: Teiid
> Issue Type: Bug
> Components: Connector API
> Affects Versions: 8.7
> Reporter: Filip Elias
> Assignee: Steven Hawkins
>
> Query fails when time,date or timestamp literal is used in where clause.
> Examples:
> Query:
> {code}select intkey from smalla where timevalue = '11:30:20'{code}
> Error:
> {code}
> '11:30:20'org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where datevalue = '2002-02-02'{code}
> Error:
> {code}
> org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'11-30-20'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> Query:
> {code}select intkey from smalla where timestampvalue = '2000-01-01 00:00:04'{code}
> Error:
> {code} org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: Invalid Date String:'2000-01-01T00-00-04'
> at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:491)
> {code}
> SOLR supports only timestamps (YYYY-mm-ddThh:mm:ssZ) so date and time types should be converted into timestamp before a query is send to SOLR.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years
[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 commented on TEIID-3276:
---------------------------------------
With TEIID-3282 addressed, Teiid and mysql (using the weekofyear function) should produce the same results. I'll also add under this issue expression pushing to expressions used in joins similar to TEIID-3227 to mitigate this circumstance.
> 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
> Fix For: 8.10
>
>
> 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)
10 years
[JBoss JIRA] (TEIID-3282) inconsistencies with the week function
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3282?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3282.
-----------------------------------
Resolution: Done
Addressed the inconsistencies with the week function as much as possible. Teiid will default to the iso 8601 calculation for consistency and sources were updated to match. The dayOfWeek function was made to always return 1=sunday - 7=saturday regardless as that is part of the odbc standard and is matched by most sources - that is a slight change from the behavior that attempt to match the postgresql extract dow logic previous when the iso8601Week property was set.
We could introduce specific iso_year and iso_day_of_week functions to further allow iso calculations.
> inconsistencies with the week function
> --------------------------------------
>
> Key: TEIID-3282
> URL: https://issues.jboss.org/browse/TEIID-3282
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> For consistency we could enforce a standard iso behavior for the week function by default.
> For mysql there is the weekofyear function that matches the iso behavior.
> hsql uses iso by default
> oracle should use iw, not ww
> However:
> h2 uses the locale (the current teiid behavior)
> sqlserver/sybase you can use SET DATEFIRST to move the first day of the week to monday, but the return values are still not fully iso.
> And there are still several others to verify
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years
[JBoss JIRA] (TEIID-3282) inconsistencies with the week function
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3282?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3282:
----------------------------------
Affects: Documentation (Ref Guide, User Guide, etc.),Release Notes
> inconsistencies with the week function
> --------------------------------------
>
> Key: TEIID-3282
> URL: https://issues.jboss.org/browse/TEIID-3282
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> For consistency we could enforce a standard iso behavior for the week function by default.
> For mysql there is the weekofyear function that matches the iso behavior.
> hsql uses iso by default
> oracle should use iw, not ww
> However:
> h2 uses the locale (the current teiid behavior)
> sqlserver/sybase you can use SET DATEFIRST to move the first day of the week to monday, but the return values are still not fully iso.
> And there are still several others to verify
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
10 years