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

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-5290:
---------------------------------------

> on MSSQL the getdate() function is treated as a non-deterministic

In the scope of a procedure loop yes, but for a single client statement it does appear to be deterministic.  

For pg I see that same behavior:

{code}
create temporary table tem (ts timestamp);

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT * FROM generate_series(1,3)
    LOOP
        perform pg_sleep(1);
        insert into tem values ((select clock_timestamp()));
    END LOOP;
END$$;

select * from tem;
{code}

All inserted values have a different timestamp.

> What do you think can we do the same here?

Yes, we can change the behavior in the context of procedure loops to be deterministic only to the statement level and not for the entire loop evaluation.

> 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)