Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5290

NOW() function returns the same value for each call in BEGIN...END block

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 10.2, 10.1.3, 10.0.5
    • 10.1
    • Query Engine
    • None
    • Hide

      Try to run the following anonymous procedures and check if the return different values for NOW() function:

      -- FAILURE(values are the same)
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 ;;

      and

      --FAILURE(values are the same)
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 ;;
      Show
      Try to run the following anonymous procedures and check if the return different values for NOW() function: -- FAILURE( values are the same) 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 ;; and --FAILURE( values are the same) 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 ;;

      Run the following queries:

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

      and

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

      NOW() function returns the same value for each call in BEGIN...END block and it doesn't matter if it is atomic or not.

              rhn-engineering-shawkins Steven Hawkins
              dalex005 Dmitrii Pogorelov
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: