[teiid-issues] [JBoss JIRA] (TEIID-5290) NOW() function returns the same value for each call in BEGIN...END block

[dalex dalex (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13547928#comment-13547928 ] 

dalex dalex commented on TEIID-5290:
------------------------------------

> That behavior isn't altered when it appears in a procedure block. Have you found other pl/sql languages that behave differently?
I indeed checked it on PostgreSQL and it has the same behavior as in teiid, that is as a deterministic value.
Actually I suspected that behavior will be like a deterministic value and I've already encountered with it in scope of the TEIID-4423 issue. But there we agreed about such behavior:
"So now:
update test.textagg_test_2 set id =(select uuid()) where id = 'xxxx';
will evaluate to a single id value.
update test.textagg_test_2 set id =uuid() where id = 'xxxx';
will set an id per row, which in this case without a key will result in an error."
What do you think can we do the same here? For example, if we use explicitly the "select now()" then it should be treated as a deterministic value but if we use just now() function, say, like here:
{code:sql}
BEGIN
	declare timestamp ts1 = now();
	DECLARE integer c = 20000;
	WHILE (c > 0) 
		BEGIN
			c= c-1; 
		END
	declare timestamp ts2 = now();
	select ts1, ts2;
END ;;
{code}
it will be treated as a non-deterministic value.

> 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
>            Priority: Blocker
>
> 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)