[JBoss JIRA] (TEIID-5409) left join is reversed
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5409:
----------------------------------
Component/s: Query Engine
Description:
A query that is run on teiid 9.1 results in 56186 rows.
The same query that is run on teiid 10.3.2 results in 5919 rows.
It seems like the left join is reversed.
I've attached both query plans.
The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
was:
A query that is run on teiid 9.1 results in 56186 rows.
The same query that is run on teiid 10.3.2 results in 5919 rows.
It seems like the left join is considered as a inner join.
I've attached both query plans.
The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
Summary: left join is reversed (was: left join is ignored)
> left join is reversed
> ---------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 10.3.1, 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.1, 11.0.1, 10.3.3
>
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is reversed.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5406) Allow usage of long datatype as the increment parameter in TimestampAdd function
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5406?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5406.
-----------------------------------
Resolution: Done
Addressed the fractional second handling under a subtask for backport tracking. To prevent unintended issues with the long parameter handling, the logic will still only accept an integer value both in the engine and push down - using an explicit cast or conversion to int literal as necessary.
> Allow usage of long datatype as the increment parameter in TimestampAdd function
> --------------------------------------------------------------------------------
>
> Key: TEIID-5406
> URL: https://issues.jboss.org/browse/TEIID-5406
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 10.2
> Environment: teiid-10.2.0 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Fix For: 11.1
>
>
> Currently the TIMESTAMPADD(interval, count, timestamp) expects the count parameter to be integer. This is inconsistent with the return value of the TIMSTAMPDIFF function that is long. Thus we cannot take the fraction of the count returned by TIMSTAMPDIFF and submit it to TIMESTAMPADD. Since the SQL_TSI_FRAC_SECOND difference is too large for most human values (and it's the only way to retrieve millis, even though millions of millis), we need a way to submit this data to TIMSTAMPADD.
> 1. There is an inconsistency when adding 1 billion fractions of a second. Please execute the script below, one adds one billion minus one, the next one exactly 1bln fractions which results in two values, one of which is 1 fraction larger than expected:
> {code:sql}
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999, Cast(CurDate() as timestamp))
> Union All
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999 + 1, Cast(CurDate() as timestamp));;
> {code}
> 2. Another example:
> {code:sql}
> Begin
> Declare timestamp initialDate = CurDate();
> Declare long diff = TimestampDiff(SQL_TSI_FRAC_SECOND, initialDate, TimestampAdd(SQL_TSI_DAY, 1, initialDate));
> Declare timestamp back = TimestampAdd(SQL_TSI_FRAC_SECOND, diff, initialDate);
> select initialDate, back;
> End ;;
> {code}
> which leads to the following error message:
> {code:noformat}
> 2018-07-03 10:10:33,942 WARN [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue47) 1RBg/ooz5vx7 TEIID30020 Processing exception for request 1RBg/ooz5vx7.27 'TEIID30070 The function
> 'TimestampAdd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted usi
> ng implicit type conversions.'. Originally QueryResolverException ResolverVisitor.java:757.: org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'TimestampA
> dd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit
> type conversions.
> at org.teiid.query.resolver.util.ResolverVisitor.resolveFunction(ResolverVisitor.java:757)
> at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:392)
> at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
> at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:50)
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:57)
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:194)
> at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
> at org.teiid.query.resolver.util.ResolverVisitor.resolveLanguageObject(ResolverVisitor.java:1456)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveStatement(UpdateProcedureResolver.java:234)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveBlock(UpdateProcedureResolver.java:127)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveCommand(UpdateProcedureResolver.java:110)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:282)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:128)
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:282)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:418)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:660)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5410) Incorrect result from timestampadd with 1 billion or more fractional seconds
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5410?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5410.
-----------------------------------
Fix Version/s: 11.1
11.0.1
10.3.3
Resolution: Done
Corrected the logic to appropriately handle both positive and negative fractional second counts along with correcting the overflow logic.
> Incorrect result from timestampadd with 1 billion or more fractional seconds
> ----------------------------------------------------------------------------
>
> Key: TEIID-5410
> URL: https://issues.jboss.org/browse/TEIID-5410
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.1, 11.0.1, 10.3.3
>
>
> A query such as:
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999 + 1, Cast(CurDate() as timestamp));
> Returns a result with the incorrect amount of factional seconds.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5409) left join is ignored
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5409:
---------------------------------------
This is like a regression introduced by TEIID-4886
> left join is ignored
> --------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.1, 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.1, 11.0.1, 10.3.3
>
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is considered as a inner join.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5406) Allow usage of long datatype as the increment parameter in TimestampAdd function
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5406?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5406:
---------------------------------------
It looks like there is some bad code handling the fractional add. It will be easy to correct. The expectation of an integer argument that is to match the expected behavior of most sources. Converting to a long is possible, but it may require adding casts to existing push down logic. Unfortunately there is no agreement on what fractional seconds mean - you can find millis, micros, and nanos.
> Allow usage of long datatype as the increment parameter in TimestampAdd function
> --------------------------------------------------------------------------------
>
> Key: TEIID-5406
> URL: https://issues.jboss.org/browse/TEIID-5406
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 10.2
> Environment: teiid-10.2.0 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Fix For: 11.1
>
>
> Currently the TIMESTAMPADD(interval, count, timestamp) expects the count parameter to be integer. This is inconsistent with the return value of the TIMSTAMPDIFF function that is long. Thus we cannot take the fraction of the count returned by TIMSTAMPDIFF and submit it to TIMESTAMPADD. Since the SQL_TSI_FRAC_SECOND difference is too large for most human values (and it's the only way to retrieve millis, even though millions of millis), we need a way to submit this data to TIMSTAMPADD.
> 1. There is an inconsistency when adding 1 billion fractions of a second. Please execute the script below, one adds one billion minus one, the next one exactly 1bln fractions which results in two values, one of which is 1 fraction larger than expected:
> {code:sql}
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999, Cast(CurDate() as timestamp))
> Union All
> Select TimestampAdd(SQL_TSI_FRAC_SECOND, 999999999 + 1, Cast(CurDate() as timestamp));;
> {code}
> 2. Another example:
> {code:sql}
> Begin
> Declare timestamp initialDate = CurDate();
> Declare long diff = TimestampDiff(SQL_TSI_FRAC_SECOND, initialDate, TimestampAdd(SQL_TSI_DAY, 1, initialDate));
> Declare timestamp back = TimestampAdd(SQL_TSI_FRAC_SECOND, diff, initialDate);
> select initialDate, back;
> End ;;
> {code}
> which leads to the following error message:
> {code:noformat}
> 2018-07-03 10:10:33,942 WARN [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue47) 1RBg/ooz5vx7 TEIID30020 Processing exception for request 1RBg/ooz5vx7.27 'TEIID30070 The function
> 'TimestampAdd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted usi
> ng implicit type conversions.'. Originally QueryResolverException ResolverVisitor.java:757.: org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'TimestampA
> dd(SQL_TSI_FRAC_SECOND, VARIABLES.diff, VARIABLES.initialDate)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit
> type conversions.
> at org.teiid.query.resolver.util.ResolverVisitor.resolveFunction(ResolverVisitor.java:757)
> at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:392)
> at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
> at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:50)
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:57)
> at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:194)
> at org.teiid.query.sql.symbol.Function.acceptVisitor(Function.java:169)
> at org.teiid.query.resolver.util.ResolverVisitor.resolveLanguageObject(ResolverVisitor.java:1456)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveStatement(UpdateProcedureResolver.java:234)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveBlock(UpdateProcedureResolver.java:127)
> at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveCommand(UpdateProcedureResolver.java:110)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:282)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:128)
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:282)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:418)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:660)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5409) left join is ignored
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Work on TEIID-5409 started by Steven Hawkins.
---------------------------------------------
> left join is ignored
> --------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.1, 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.1, 11.0.1, 10.3.3
>
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is considered as a inner join.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5409) left join is ignored
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5409:
----------------------------------
Fix Version/s: 11.1
11.0.1
10.3.3
Priority: Blocker (was: Major)
> left join is ignored
> --------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.1, 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 11.1, 11.0.1, 10.3.3
>
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is considered as a inner join.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5409) left join is ignored
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-5409:
--------------------------------
Affects Version/s: 10.3.1
> left join is ignored
> --------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.1, 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is considered as a inner join.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months
[JBoss JIRA] (TEIID-5409) left join is ignored
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5409?page=com.atlassian.jira.plugin... ]
Bram Gadeyne updated TEIID-5409:
--------------------------------
Attachment: plan_teiid_9.1.txt
plan_teiid_10.3.2.txt
> left join is ignored
> --------------------
>
> Key: TEIID-5409
> URL: https://issues.jboss.org/browse/TEIID-5409
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.3.2
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: plan_teiid_10.3.2.txt, plan_teiid_9.1.txt
>
>
> A query that is run on teiid 9.1 results in 56186 rows.
> The same query that is run on teiid 10.3.2 results in 5919 rows.
> It seems like the left join is considered as a inner join.
> I've attached both query plans.
> The queries are exactly the same. Note that on 9.1 we've prefixed table names with cos2_5_.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 5 months