[GitHub] trafodion pull request #1444: [TRAFODION-2954] add MySQL function unix_times...

[asfgit]

Github user asfgit closed the pull request at:

https://github.com/apache/trafodion/pull/1444


---

[GitHub] trafodion pull request #1444: [TRAFODION-2954] add MySQL function unix_times...

[traflm]

Github user traflm commented on a diff in the pull request:

https://github.com/apache/trafodion/pull/1444#discussion_r169868459
  
--- Diff: core/sql/regress/executor/TEST002 ---
@@ -1188,6 +1188,38 @@ select * from regexp_test where c1 regexp 
'^(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2
 select * from regexp_test where c1 regexp '(中文测试)';
 select * from regexp_test where c1 regexp '[^\';
 drop table regexp_test;
+
+--create table have 1K rows
+create table T002T1K (uniq int not null,
+ c1K int, c100 int,
+ c10 int, c1 int, c0 int  )
+ STORE BY (uniq)
+  ATTRIBUTES ALIGNED FORMAT
+  SALT USING 8 PARTITIONS
+  ;
+
+upsert using load into T002T1K select
+0 + (1000 * x10) + (100 * x1) + (10 * x1) + (1 * x01),
+0 + (100 * x10) + (10 * x1) + (1 * x01),
+0 + (10 * x1) + (1 * x01),
+0 + (1 * x01),
+0,
+X01
+from (values(0)) t
+transpose 0,1,2,3,4,5,6,7,8,9 as x10
+transpose 0,1,2,3,4,5,6,7,8,9 as x1
+transpose 0,1,2,3,4,5,6,7,8,9 as X01;
+
+create table t002timert (c0 int, c1 int, c2 largeint);
+create table t002tmp1 (c1 int);
+insert into t002tmp1 values(1),(2),(3);
+
+insert into t002timert select 1, sleep(5) , unix_timestamp() from t002tmp1;
+insert into t002timert select 2, sleep(5) , unix_timestamp() from t002tmp1;
+select 'sleeptimetest002', di from (select ( max(c2) - min(c2)) as di from 
t002timert ) where di between 5 and 9;
--- End diff --

above two INSERTS will insert two different unix_timestamp() , the 
difference is the second between their run, so if the sleep(5) eval once, the 
diff should be around 5 seconds, and less than 10. So the query is using 
'between 5 and 9'. If something wrong, this test query will return 0 rows and 
fail the test.


---

[GitHub] trafodion pull request #1444: [TRAFODION-2954] add MySQL function unix_times...

[traflm]

Github user traflm commented on a diff in the pull request:

https://github.com/apache/trafodion/pull/1444#discussion_r169511829
  
--- Diff: core/sql/optimizer/GroupAttr.cpp ---
@@ -1793,6 +1793,8 @@ void 
GroupAttributes::resolveCharacteristicInputs(const ValueIdSet& externalInpu
 ItemExpr * vidExpr = vid.getItemExpr();
 if ((vidExpr->getOperatorType() == ITM_CURRENT_USER) ||
 (vidExpr->getOperatorType() == ITM_CURRENT_TIMESTAMP) ||
+(vidExpr->getOperatorType() == ITM_UNIQUE_SHORT_ID) ||
+(vidExpr->getOperatorType() == ITM_UNIQUE_ID) ||
--- End diff --

You are right, Hans, I also think in the opposite, I just simply follow the 
current_timestamp behavior, and this really is not what I originally want. So I 
will change uuid() to be evaluated once per row. 


---

[GitHub] trafodion pull request #1444: [TRAFODION-2954] add MySQL function unix_times...

[zellerh]

Github user zellerh commented on a diff in the pull request:

https://github.com/apache/trafodion/pull/1444#discussion_r169392533
  
--- Diff: core/sql/optimizer/GroupAttr.cpp ---
@@ -1793,6 +1793,8 @@ void 
GroupAttributes::resolveCharacteristicInputs(const ValueIdSet& externalInpu
 ItemExpr * vidExpr = vid.getItemExpr();
 if ((vidExpr->getOperatorType() == ITM_CURRENT_USER) ||
 (vidExpr->getOperatorType() == ITM_CURRENT_TIMESTAMP) ||
+(vidExpr->getOperatorType() == ITM_UNIQUE_SHORT_ID) ||
+(vidExpr->getOperatorType() == ITM_UNIQUE_ID) ||
--- End diff --

This comment applies to all the changes in this commit: Don't these code 
changes do the opposite of what you want to achieve? Functions like 
CURRENT_TIMESTAMP and CURRENT_USER are carefully designed such that we execute 
them only once per query, in the master. You want to make sure your functions 
are evaluated once per row. Did you find that these changes helped fix the 
issues you were seeing?


---

[GitHub] trafodion pull request #1444: [TRAFODION-2954] add MySQL function unix_times...

[zellerh]

Github user zellerh commented on a diff in the pull request:

https://github.com/apache/trafodion/pull/1444#discussion_r169390910
  
--- Diff: core/sql/regress/executor/TEST002 ---
@@ -1188,6 +1188,32 @@ select * from regexp_test where c1 regexp 
'^(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2
 select * from regexp_test where c1 regexp '(中文测试)';
 select * from regexp_test where c1 regexp '[^\';
 drop table regexp_test;
+
+--create table have 1K rows
+create table T002T1K (uniq int not null,
+ c1K int, c100 int,
+ c10 int, c1 int, c0 int  )
+ STORE BY (uniq)
+  ATTRIBUTES ALIGNED FORMAT
+  SALT USING 8 PARTITIONS
+  ;
+
+upsert using load into T002T1K select
+0 + (1000 * x10) + (100 * x1) + (10 * x1) + (1 * x01),
+0 + (100 * x10) + (10 * x1) + (1 * x01),
+0 + (10 * x1) + (1 * x01),
+0 + (1 * x01),
+0,
+X01
+from (values(0)) t
+transpose 0,1,2,3,4,5,6,7,8,9 as x10
+transpose 0,1,2,3,4,5,6,7,8,9 as x1
+transpose 0,1,2,3,4,5,6,7,8,9 as X01;
+
+select sleep(5) from dual;
+select 'unixtimestamp',unix_timestamp() from dual;
+select 'uuidrow', uuid(), unix_timestamp() from T002T1K;
--- End diff --

If you want to ensure that we see different uuids and not the same one 
repeated, you could add a query select count(distinct uuid()) from T002T1K.


---