Changeset: 9d20f7b5bd44 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9d20f7b5bd44
Modified Files:
monetdb5/optimizer/opt_emptybind.c
sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
Branch: default
Log Message:
Push emptybind through trivial module calls. It fixes the missalignment issue
diffs (165 lines):
diff --git a/monetdb5/optimizer/opt_emptybind.c
b/monetdb5/optimizer/opt_emptybind.c
--- a/monetdb5/optimizer/opt_emptybind.c
+++ b/monetdb5/optimizer/opt_emptybind.c
@@ -238,10 +238,14 @@ OPTemptybindImplementation(Client cntxt,
emptyresult(0);
}
}
- if( getModuleId(p) == batstrRef){
- if( empty[getArg(p,1)] || empty[getArg(p,2)] ){
- actions++;
- emptyresult(0);
+ if (getModuleId(p) == batmkeyRef || getModuleId(p) == batstrRef
|| getModuleId(p) == batmtimeRef ||
+ getModuleId(p) == batmmathRef || getModuleId(p) ==
batcalcRef || (getModuleId(p) == algebraRef && getFunctionId(p) ==
projectionpathRef)) {
+ for (int j = p->retc; j < p->argc; j++) {
+ if( empty[getArg(p,j)]){
+ actions++;
+ emptyresult(0);
+ break;
+ }
}
}
if (getModuleId(p)== batRef && isUpdateInstruction(p)){
diff --git
a/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
b/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
---
a/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
+++
b/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
@@ -7,6 +7,10 @@ start transaction
statement ok
CREATE TABLE "task" ("sys_class_name" VARCHAR(80),"priority"
BIGINT,"sys_created_on" TIMESTAMP)
+statement ok rowcount 4
+INSERT INTO "task" VALUES ('incident', 1, timestamp '2010-10-10 10:00:00'),
('incident', 2, timestamp '2011-10-10 10:00:00'),
+('incident', 3, timestamp '2012-10-10 10:00:00'), ('incident', 4, timestamp
'2013-10-10 10:00:00')
+
statement ok
create procedure profiler.starttrace() external name profiler."starttrace"
@@ -17,8 +21,58 @@ statement ok
call profiler."starttrace"()
query T rowsort
-SELECT sys.timestamp_to_str(case when task0."sys_created_on" >= '1999-10-31
09:00:00' and task0."sys_created_on" < '2000-04-02 10:00:00' then
task0."sys_created_on" + interval '-28800' second when task0."sys_created_on"
>= '2000-04-02 10:00:00' and task0."sys_created_on" < '2000-10-29 09:00:00'
then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2000-10-29 09:00:00' and task0."sys_created_on" <
'2001-04-01 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2001-04-01 10:00:00' and task0."sys_created_on"
< '2001-10-28 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2001-10-28 09:00:00' and task0."sys_created_on"
< '2002-04-07 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2002-04-07 10:00:00' and task0."sys_created_on"
< '2002-10-27 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0
."sys_created_on" >= '2002-10-27 09:00:00' and task0."sys_created_on" <
'2003-04-06 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2003-04-06 10:00:00' and task0."sys_created_on"
< '2003-10-26 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2003-10-26 09:00:00' and task0."sys_created_on"
< '2004-04-04 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2004-04-04 10:00:00' and task0."sys_created_on"
< '2004-10-31 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2004-10-31 09:00:00' and task0."sys_created_on"
< '2005-04-03 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2005-04-03 10:00:00' and task0."sys_created_on"
< '2005-10-30 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2005-10-30 09:00:00'
and task0."sys_created_on" < '2006-04-02 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2006-04-02 10:00:00'
and task0."sys_created_on" < '2006-10-29 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2006-10-29 09:00:00'
and task0."sys_created_on" < '2007-03-11 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2007-03-11 10:00:00'
and task0."sys_created_on" < '2007-11-04 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2007-11-04 09:00:00'
and task0."sys_created_on" < '2008-03-09 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2008-03-09 10:00:00'
and task0."sys_created_on" < '2008-11-02 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2008-11-02 09:00:00'
and task0."sys_created_on" < '2009-03-08 10
:00:00' then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2009-03-08 10:00:00' and task0."sys_created_on" <
'2009-11-01 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2009-11-01 09:00:00' and task0."sys_created_on"
< '2010-03-14 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2010-03-14 10:00:00' and task0."sys_created_on"
< '2010-11-07 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2010-11-07 09:00:00' and task0."sys_created_on"
< '2011-03-13 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2011-03-13 10:00:00' and task0."sys_created_on"
< '2011-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2011-11-06 09:00:00' and task0."sys_created_on"
< '2012-03-11 10:00:00' then task0."sys_created_on" + inter
val '-28800' second when task0."sys_created_on" >= '2012-03-11 10:00:00' and
task0."sys_created_on" < '2012-11-04 09:00:00' then task0."sys_created_on" +
interval '-25200' second when task0."sys_created_on" >= '2012-11-04 09:00:00'
and task0."sys_created_on" < '2013-03-10 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2013-03-10 10:00:00'
and task0."sys_created_on" < '2013-11-03 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2013-11-03 09:00:00'
and task0."sys_created_on" < '2014-03-09 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2014-03-09 10:00:00'
and task0."sys_created_on" < '2014-11-02 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2014-11-02 09:00:00'
and task0."sys_created_on" < '2015-03-08 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created
_on" >= '2015-03-08 10:00:00' and task0."sys_created_on" < '2015-11-01
09:00:00' then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2015-11-01 09:00:00' and task0."sys_created_on" <
'2016-03-13 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2016-03-13 10:00:00' and task0."sys_created_on"
< '2016-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2016-11-06 09:00:00' and task0."sys_created_on"
< '2017-03-12 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2017-03-12 10:00:00' and task0."sys_created_on"
< '2017-11-05 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2017-11-05 09:00:00' and task0."sys_created_on"
< '2018-03-11 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2018-03-11 10:00:00' and task0."sy
s_created_on" < '2018-11-04 09:00:00' then task0."sys_created_on" + interval
'-25200' second when task0."sys_created_on" >= '2018-11-04 09:00:00' and
task0."sys_created_on" < '2019-03-10 10:00:00' then task0."sys_created_on" +
interval '-28800' second when task0."sys_created_on" >= '2019-03-10 10:00:00'
and task0."sys_created_on" < '2019-11-03 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2019-11-03 09:00:00'
and task0."sys_created_on" < '2020-03-08 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2020-03-08 10:00:00'
and task0."sys_created_on" < '2020-11-01 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2020-11-01 09:00:00'
and task0."sys_created_on" < '2021-03-14 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2021-03-14 10:00:00'
and task0."sys_created_on" < '2021-11-07 09:00:00' then
task0."sys_created_on" + interval '-25200' second when task0."sys_created_on"
>= '2021-11-07 09:00:00' and task0."sys_created_on" < '2022-03-13 10:00:00'
then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2022-03-13 10:00:00' and task0."sys_created_on" <
'2022-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
else task0."sys_created_on" + interval '-28800' second end,'%Y') AS yearref
FROM task task0 WHERE task0."sys_class_name" = 'incident' AND task0."priority"
IS NOT NULL
+SELECT sys.timestamp_to_str(case when task0."sys_created_on" >= '1999-10-31
09:00:00' and task0."sys_created_on" < '2000-04-02 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2000-04-02 10:00:00' and task0."sys_created_on" <
'2000-10-29 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2000-10-29 09:00:00' and task0."sys_created_on" <
'2001-04-01 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2001-04-01 10:00:00' and task0."sys_created_on" <
'2001-10-28 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2001-10-28 09:00:00' and task0."sys_created_on" <
'2002-04-07 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2002-04-07 10:00:00' and task0."sys_created_on" <
'2002-10-27 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2002-10-27 09:00:00' and task0."sys_created_on" <
'2003-04-06 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2003-04-06 10:00:00' and task0."sys_created_on" <
'2003-10-26 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2003-10-26 09:00:00' and task0."sys_created_on" <
'2004-04-04 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2004-04-04 10:00:00' and task0."sys_created_on" <
'2004-10-31 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2004-10-31 09:00:00' and task0."sys_created_on" <
'2005-04-03 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2005-04-03 10:00:00' and task0."sys_created_on" <
'2005-10-30 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2005-10-30 09:00:00' and task0."sys_created_on" <
'2006-04-02 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2006-04-02 10:00:00' and task0."sys_created_on" <
'2006-10-29 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2006-10-29 09:00:00' and task0."sys_created_on" <
'2007-03-11 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2007-03-11 10:00:00' and task0."sys_created_on" <
'2007-11-04 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2007-11-04 09:00:00' and task0."sys_created_on" <
'2008-03-09 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2008-03-09 10:00:00' and task0."sys_created_on" <
'2008-11-02 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2008-11-02 09:00:00' and task0."sys_created_on" <
'2009-03-08 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2009-03-08 10:00:00' and task0."sys_created_on" <
'2009-11-01 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2009-11-01 09:00:00' and task0."sys_created_on" <
'2010-03-14 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2010-03-14 10:00:00' and task0."sys_created_on" <
'2010-11-07 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2010-11-07 09:00:00' and task0."sys_created_on" <
'2011-03-13 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2011-03-13 10:00:00' and task0."sys_created_on" <
'2011-11-06 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2011-11-06 09:00:00' and task0."sys_created_on" <
'2012-03-11 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2012-03-11 10:00:00' and task0."sys_created_on" <
'2012-11-04 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2012-11-04 09:00:00' and task0."sys_created_on" <
'2013-03-10 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2013-03-10 10:00:00' and task0."sys_created_on" <
'2013-11-03 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2013-11-03 09:00:00' and task0."sys_created_on" <
'2014-03-09 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2014-03-09 10:00:00' and task0."sys_created_on" <
'2014-11-02 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2014-11-02 09:00:00' and task0."sys_created_on" <
'2015-03-08 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2015-03-08 10:00:00' and task0."sys_created_on" <
'2015-11-01 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2015-11-01 09:00:00' and task0."sys_created_on" <
'2016-03-13 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2016-03-13 10:00:00' and task0."sys_created_on" <
'2016-11-06 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2016-11-06 09:00:00' and task0."sys_created_on" <
'2017-03-12 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2017-03-12 10:00:00' and task0."sys_created_on" <
'2017-11-05 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2017-11-05 09:00:00' and task0."sys_created_on" <
'2018-03-11 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2018-03-11 10:00:00' and task0."sys_created_on" <
'2018-11-04 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2018-11-04 09:00:00' and task0."sys_created_on" <
'2019-03-10 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2019-03-10 10:00:00' and task0."sys_created_on" <
'2019-11-03 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2019-11-03 09:00:00' and task0."sys_created_on" <
'2020-03-08 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2020-03-08 10:00:00' and task0."sys_created_on" <
'2020-11-01 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2020-11-01 09:00:00' and task0."sys_created_on" <
'2021-03-14 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2021-03-14 10:00:00' and task0."sys_created_on" <
'2021-11-07 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2021-11-07 09:00:00' and task0."sys_created_on" <
'2022-03-13 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2022-03-13 10:00:00' and task0."sys_created_on" <
'2022-11-06 09:00:00'
+then task0."sys_created_on" + interval '-25200' second else
task0."sys_created_on" + interval '-28800' second end,'%Y') AS yearref FROM
task task0 WHERE task0."sys_class_name" = 'incident' AND task0."priority" IS
NOT NULL
----
+2010
+2011
+2012
+2013
statement ok
call profiler.stoptrace()
@@ -31,7 +85,7 @@ 46
query I rowsort
select count(*) from sys.tracelog() where stmt like '%algebra.thetaselect%'
----
-46
+47
query I rowsort
select count(*) from sys.tracelog() where stmt like '%bat.replace%'
@@ -54,8 +108,58 @@ select count(*) from sys.tracelog() wher
1
query T rowsort
-SELECT sys.timestamp_to_str(case when task0."sys_created_on" >= '1999-10-31
09:00:00' and task0."sys_created_on" < '2000-04-02 10:00:00' then
task0."sys_created_on" + interval '-28800' second when task0."sys_created_on"
>= '2000-04-02 10:00:00' and task0."sys_created_on" < '2000-10-29 09:00:00'
then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2000-10-29 09:00:00' and task0."sys_created_on" <
'2001-04-01 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2001-04-01 10:00:00' and task0."sys_created_on"
< '2001-10-28 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2001-10-28 09:00:00' and task0."sys_created_on"
< '2002-04-07 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2002-04-07 10:00:00' and task0."sys_created_on"
< '2002-10-27 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0
."sys_created_on" >= '2002-10-27 09:00:00' and task0."sys_created_on" <
'2003-04-06 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2003-04-06 10:00:00' and task0."sys_created_on"
< '2003-10-26 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2003-10-26 09:00:00' and task0."sys_created_on"
< '2004-04-04 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2004-04-04 10:00:00' and task0."sys_created_on"
< '2004-10-31 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2004-10-31 09:00:00' and task0."sys_created_on"
< '2005-04-03 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2005-04-03 10:00:00' and task0."sys_created_on"
< '2005-10-30 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2005-10-30 09:00:00'
and task0."sys_created_on" < '2006-04-02 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2006-04-02 10:00:00'
and task0."sys_created_on" < '2006-10-29 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2006-10-29 09:00:00'
and task0."sys_created_on" < '2007-03-11 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2007-03-11 10:00:00'
and task0."sys_created_on" < '2007-11-04 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2007-11-04 09:00:00'
and task0."sys_created_on" < '2008-03-09 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2008-03-09 10:00:00'
and task0."sys_created_on" < '2008-11-02 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2008-11-02 09:00:00'
and task0."sys_created_on" < '2009-03-08 10
:00:00' then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2009-03-08 10:00:00' and task0."sys_created_on" <
'2009-11-01 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2009-11-01 09:00:00' and task0."sys_created_on"
< '2010-03-14 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2010-03-14 10:00:00' and task0."sys_created_on"
< '2010-11-07 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2010-11-07 09:00:00' and task0."sys_created_on"
< '2011-03-13 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2011-03-13 10:00:00' and task0."sys_created_on"
< '2011-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2011-11-06 09:00:00' and task0."sys_created_on"
< '2012-03-11 10:00:00' then task0."sys_created_on" + inter
val '-28800' second when task0."sys_created_on" >= '2012-03-11 10:00:00' and
task0."sys_created_on" < '2012-11-04 09:00:00' then task0."sys_created_on" +
interval '-25200' second when task0."sys_created_on" >= '2012-11-04 09:00:00'
and task0."sys_created_on" < '2013-03-10 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2013-03-10 10:00:00'
and task0."sys_created_on" < '2013-11-03 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2013-11-03 09:00:00'
and task0."sys_created_on" < '2014-03-09 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2014-03-09 10:00:00'
and task0."sys_created_on" < '2014-11-02 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2014-11-02 09:00:00'
and task0."sys_created_on" < '2015-03-08 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created
_on" >= '2015-03-08 10:00:00' and task0."sys_created_on" < '2015-11-01
09:00:00' then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2015-11-01 09:00:00' and task0."sys_created_on" <
'2016-03-13 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2016-03-13 10:00:00' and task0."sys_created_on"
< '2016-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2016-11-06 09:00:00' and task0."sys_created_on"
< '2017-03-12 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2017-03-12 10:00:00' and task0."sys_created_on"
< '2017-11-05 09:00:00' then task0."sys_created_on" + interval '-25200' second
when task0."sys_created_on" >= '2017-11-05 09:00:00' and task0."sys_created_on"
< '2018-03-11 10:00:00' then task0."sys_created_on" + interval '-28800' second
when task0."sys_created_on" >= '2018-03-11 10:00:00' and task0."sy
s_created_on" < '2018-11-04 09:00:00' then task0."sys_created_on" + interval
'-25200' second when task0."sys_created_on" >= '2018-11-04 09:00:00' and
task0."sys_created_on" < '2019-03-10 10:00:00' then task0."sys_created_on" +
interval '-28800' second when task0."sys_created_on" >= '2019-03-10 10:00:00'
and task0."sys_created_on" < '2019-11-03 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2019-11-03 09:00:00'
and task0."sys_created_on" < '2020-03-08 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2020-03-08 10:00:00'
and task0."sys_created_on" < '2020-11-01 09:00:00' then task0."sys_created_on"
+ interval '-25200' second when task0."sys_created_on" >= '2020-11-01 09:00:00'
and task0."sys_created_on" < '2021-03-14 10:00:00' then task0."sys_created_on"
+ interval '-28800' second when task0."sys_created_on" >= '2021-03-14 10:00:00'
and task0."sys_created_on" < '2021-11-07 09:00:00' then
task0."sys_created_on" + interval '-25200' second when task0."sys_created_on"
>= '2021-11-07 09:00:00' and task0."sys_created_on" < '2022-03-13 10:00:00'
then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2022-03-13 10:00:00' and task0."sys_created_on" <
'2022-11-06 09:00:00' then task0."sys_created_on" + interval '-25200' second
else task0."sys_created_on" + interval '-28800' second end,'%Y') AS yearref
FROM task task0 WHERE task0."sys_class_name" = 'incident' AND task0."priority"
IS NOT NULL
+SELECT sys.timestamp_to_str(case when task0."sys_created_on" >= '1999-10-31
09:00:00' and task0."sys_created_on" < '2000-04-02 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2000-04-02 10:00:00' and task0."sys_created_on" <
'2000-10-29 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2000-10-29 09:00:00' and task0."sys_created_on" <
'2001-04-01 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2001-04-01 10:00:00' and task0."sys_created_on" <
'2001-10-28 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2001-10-28 09:00:00' and task0."sys_created_on" <
'2002-04-07 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2002-04-07 10:00:00' and task0."sys_created_on" <
'2002-10-27 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2002-10-27 09:00:00' and task0."sys_created_on" <
'2003-04-06 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2003-04-06 10:00:00' and task0."sys_created_on" <
'2003-10-26 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2003-10-26 09:00:00' and task0."sys_created_on" <
'2004-04-04 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2004-04-04 10:00:00' and task0."sys_created_on" <
'2004-10-31 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2004-10-31 09:00:00' and task0."sys_created_on" <
'2005-04-03 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2005-04-03 10:00:00' and task0."sys_created_on" <
'2005-10-30 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2005-10-30 09:00:00' and task0."sys_created_on" <
'2006-04-02 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2006-04-02 10:00:00' and task0."sys_created_on" <
'2006-10-29 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2006-10-29 09:00:00' and task0."sys_created_on" <
'2007-03-11 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2007-03-11 10:00:00' and task0."sys_created_on" <
'2007-11-04 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2007-11-04 09:00:00' and task0."sys_created_on" <
'2008-03-09 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2008-03-09 10:00:00' and task0."sys_created_on" <
'2008-11-02 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2008-11-02 09:00:00' and task0."sys_created_on" <
'2009-03-08 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2009-03-08 10:00:00' and task0."sys_created_on" <
'2009-11-01 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2009-11-01 09:00:00' and task0."sys_created_on" <
'2010-03-14 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2010-03-14 10:00:00' and task0."sys_created_on" <
'2010-11-07 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2010-11-07 09:00:00' and task0."sys_created_on" <
'2011-03-13 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2011-03-13 10:00:00' and task0."sys_created_on" <
'2011-11-06 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2011-11-06 09:00:00' and task0."sys_created_on" <
'2012-03-11 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2012-03-11 10:00:00' and task0."sys_created_on" <
'2012-11-04 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2012-11-04 09:00:00' and task0."sys_created_on" <
'2013-03-10 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2013-03-10 10:00:00' and task0."sys_created_on" <
'2013-11-03 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2013-11-03 09:00:00' and task0."sys_created_on" <
'2014-03-09 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2014-03-09 10:00:00' and task0."sys_created_on" <
'2014-11-02 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2014-11-02 09:00:00' and task0."sys_created_on" <
'2015-03-08 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2015-03-08 10:00:00' and task0."sys_created_on" <
'2015-11-01 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2015-11-01 09:00:00' and task0."sys_created_on" <
'2016-03-13 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2016-03-13 10:00:00' and task0."sys_created_on" <
'2016-11-06 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2016-11-06 09:00:00' and task0."sys_created_on" <
'2017-03-12 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2017-03-12 10:00:00' and task0."sys_created_on" <
'2017-11-05 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2017-11-05 09:00:00' and task0."sys_created_on" <
'2018-03-11 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2018-03-11 10:00:00' and task0."sys_created_on" <
'2018-11-04 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2018-11-04 09:00:00' and task0."sys_created_on" <
'2019-03-10 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2019-03-10 10:00:00' and task0."sys_created_on" <
'2019-11-03 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2019-11-03 09:00:00' and task0."sys_created_on" <
'2020-03-08 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2020-03-08 10:00:00' and task0."sys_created_on" <
'2020-11-01 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2020-11-01 09:00:00' and task0."sys_created_on" <
'2021-03-14 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2021-03-14 10:00:00' and task0."sys_created_on" <
'2021-11-07 09:00:00'
+then task0."sys_created_on" + interval '-25200' second when
task0."sys_created_on" >= '2021-11-07 09:00:00' and task0."sys_created_on" <
'2022-03-13 10:00:00'
+then task0."sys_created_on" + interval '-28800' second when
task0."sys_created_on" >= '2022-03-13 10:00:00' and task0."sys_created_on" <
'2022-11-06 09:00:00'
+then task0."sys_created_on" + interval '-25200' second else
task0."sys_created_on" + interval '-28800' second end,'%Y') AS yearref FROM
task task0 WHERE task0."sys_class_name" = 'incident' AND task0."priority" IS
NOT NULL
----
+2010
+2011
+2012
+2013
statement ok
rollback
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
