[teiid-issues] [JBoss JIRA] (TEIID-5290) NOW() function returns the same value for each call in BEGIN...END block
Steven Hawkins (JIRA)
issues at jboss.org
Tue Mar 20 08:45:01 EDT 2018
[ https://issues.jboss.org/browse/TEIID-5290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13548437#comment-13548437 ]
Steven Hawkins commented on TEIID-5290:
---------------------------------------
This is mostly about the function definition for now/current_timestamp - it is defined as command deterministic, which as per TEIID-5204 means that it should have a single value for the scope of the user command.
There is no difference in the behavior in this case between using "declare timestamp ts2 = now()" and "declare timestamp ts2 = (select now())" as we are still only looking for the command level value.
getdate and clock_timestamp are the fully non-deterministic timestamp functions for sql server and postgresql respectively. Postgresql actually offers:
transaction_timestamp() - same as pg now()
statement_timestamp()
clock_timestamp()
Our current behavior for now / current_timestamp matches pg statement_timestamp. If you use now() in the postgresql example above, it will produce the same value for each timestamp.
It appears that SQL Server and postgresql disagree on the meaning of current_timestamp - it's fully non-deterministc for SQL Server and it matches transaction_timetstamp for postgresql. The spec does not seem to fully specify what current means.
Our choices here are:
1. leave the Teiid current_timestamp / now functions as command deterministic as per TEIID-5204 and introduce a new fully non-deterministic timestamp function.
2. change the Teiid current_timestamp / now functions back to the non-determinisic implementation and update the metadata to indicate it is fully non-deterministic.
Do you have a preference as to the resolution?
> NOW() function returns the same value for each call in BEGIN...END block
> ------------------------------------------------------------------------
>
> Key: TEIID-5290
> URL: https://issues.jboss.org/browse/TEIID-5290
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 10.1
> Environment: teiid-10.1.0 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Fix For: 10.2
>
>
> Run the following queries:
> {code:sql}
> BEGIN
> declare timestamp ts1 = select now();
> DECLARE integer c = 20000;
> WHILE (c > 0)
> BEGIN
> c= c-1;
> END
> declare timestamp ts2 = select now();
> select ts1, ts2;
> END ;;
> {code}
> and
> {code:sql}
> BEGIN
> DECLARE integer c = 20000;
> create local temporary table #tem(ts timestamp);
> WHILE (c > 0)
> BEGIN
> INSERT INTO #tem VALUES((select NOW()));
> c= c-1;
> END
> select * from #tem;
> END ;;
> {code}
> NOW() function returns the same value for each call in BEGIN...END block and it doesn't matter if it is atomic or not.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list