Changeset: 54e21e04d706 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=54e21e04d706
Modified Files:
sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.sql
sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
Branch: Jul2015
Log Message:
Extended the test for Bug 3834 to test that the original UPDATE statement also
produces correct results.
diffs (137 lines):
diff --git
a/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.sql
b/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.sql
---
a/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.sql
+++
b/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.sql
@@ -5,6 +5,17 @@ CREATE TABLE daterange_wrong (startdate
INSERT INTO daterange_wrong (startdate, enddate, name) VALUES
('2012-03-01','2012-03-31','A');
-- returns empty result, which is wrong:
SELECT * FROM datepoint_wrong A, daterange_wrong B WHERE A.mydate BETWEEN
B.startdate AND B.enddate;
+UPDATE datepoint_wrong
+SET (insiderange, rangename) = (
+ SELECT TRUE, name
+ FROM (
+ SELECT startdate, enddate, name
+ FROM daterange_wrong
+ ) AS B
+ WHERE datepoint_wrong.mydate >= B.startdate
+ AND datepoint_wrong.mydate <= B.enddate
+);
+SELECT * FROM datepoint_wrong;
-- Two alternatives with which the SELECT query returns correct results:
@@ -17,6 +28,17 @@ CREATE TABLE daterange_correct1 (startda
INSERT INTO daterange_correct1 (startdate, enddate, name) VALUES
('2012-03-01','2012-03-31','A');
-- returns correct result: one tuple with "mydate" value '2012-03-09'
SELECT * FROM datepoint_correct1 A, daterange_correct1 B WHERE A.mydate
BETWEEN B.startdate AND B.enddate;
+UPDATE datepoint_correct1
+SET (insiderange, rangename) = (
+ SELECT TRUE, name
+ FROM (
+ SELECT startdate, enddate, name
+ FROM daterange_correct1
+ ) AS B
+ WHERE datepoint_correct1.mydate >= B.startdate
+ AND datepoint_correct1.mydate <= B.enddate
+);
+SELECT * FROM datepoint_correct1;
CREATE TABLE datepoint_correct2 (mydate DATE, insiderange BOOLEAN DEFAULT
FALSE, rangename CHAR(8));
INSERT INTO datepoint_correct2 (mydate) VALUES ('2012-03-09');
@@ -25,6 +47,18 @@ CREATE TABLE daterange_correct2 (startda
INSERT INTO daterange_correct2 (startdate, enddate, name) VALUES
('2012-03-01','2012-03-31','A');
-- returns correct result: one tuple with "mydate" value '2012-03-09'
SELECT * FROM datepoint_correct2 A, daterange_correct2 B WHERE A.mydate
BETWEEN B.startdate AND B.enddate;
+UPDATE datepoint_correct2
+SET (insiderange, rangename) = (
+ SELECT TRUE, name
+ FROM (
+ SELECT startdate, enddate, name
+ FROM daterange_correct2
+ ) AS B
+ WHERE datepoint_correct2.mydate >= B.startdate
+ AND datepoint_correct2.mydate <= B.enddate
+);
+SELECT * FROM datepoint_correct2;
+
DROP TABLE datepoint_wrong;
DROP TABLE datepoint_correct1;
diff --git
a/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
b/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
---
a/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
+++
b/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
@@ -76,6 +76,24 @@ Ready.
% date, boolean, char, date, date, char # type
% 10, 5, 8, 10, 10, 8 # length
[ 2012-03-09, false, NULL, 2012-03-01, 2012-03-31, "A" ]
+#UPDATE datepoint_wrong
+#SET (insiderange, rangename) = (
+# SELECT TRUE, name
+# FROM (
+# SELECT startdate, enddate, name
+# FROM daterange_wrong
+# ) AS B
+# WHERE datepoint_wrong.mydate >= B.startdate
+# AND datepoint_wrong.mydate <= B.enddate
+#);
+[ 1 ]
+#SELECT * FROM datepoint_wrong;
+% sys.datepoint_wrong, sys.datepoint_wrong, sys.datepoint_wrong # table_name
+% mydate, insiderange, rangename # name
+% date, boolean, char # type
+% 10, 5, 8 # length
+[ 2012-05-09, false, NULL ]
+[ 2012-03-09, true, "A" ]
#CREATE TABLE datepoint_correct1 (mydate DATE, insiderange BOOLEAN DEFAULT
FALSE, rangename CHAR(8));
#INSERT INTO datepoint_correct1 (mydate) VALUES ('2012-05-09');
[ 1 ]
@@ -92,6 +110,25 @@ Ready.
% date, boolean, char, date, date, char # type
% 10, 5, 8, 10, 10, 8 # length
[ 2012-03-09, false, NULL, 2012-03-01, 2012-03-31, "A" ]
+#UPDATE datepoint_correct1
+#SET (insiderange, rangename) = (
+# SELECT TRUE, name
+# FROM (
+# SELECT startdate, enddate, name
+# FROM daterange_correct1
+# ) AS B
+# WHERE datepoint_correct1.mydate >= B.startdate
+# AND datepoint_correct1.mydate <= B.enddate
+#);
+[ 1 ]
+#SELECT * FROM datepoint_correct1;
+% sys.datepoint_correct1, sys.datepoint_correct1, sys.datepoint_correct1
# table_name
+% mydate, insiderange, rangename # name
+% date, boolean, char # type
+% 10, 5, 8 # length
+[ 2012-05-09, false, NULL ]
+[ 2012-03-09, true, "A" ]
+[ 2012-04-09, false, NULL ]
#CREATE TABLE datepoint_correct2 (mydate DATE, insiderange BOOLEAN DEFAULT
FALSE, rangename CHAR(8));
#INSERT INTO datepoint_correct2 (mydate) VALUES ('2012-03-09');
[ 1 ]
@@ -106,6 +143,24 @@ Ready.
% date, boolean, char, date, date, char # type
% 10, 5, 8, 10, 10, 8 # length
[ 2012-03-09, false, NULL, 2012-03-01, 2012-03-31, "A" ]
+#UPDATE datepoint_correct2
+#SET (insiderange, rangename) = (
+# SELECT TRUE, name
+# FROM (
+# SELECT startdate, enddate, name
+# FROM daterange_correct2
+# ) AS B
+# WHERE datepoint_correct2.mydate >= B.startdate
+# AND datepoint_correct2.mydate <= B.enddate
+#);
+[ 1 ]
+#SELECT * FROM datepoint_correct2;
+% sys.datepoint_correct2, sys.datepoint_correct2, sys.datepoint_correct2
# table_name
+% mydate, insiderange, rangename # name
+% date, boolean, char # type
+% 10, 5, 8 # length
+[ 2012-03-09, true, "A" ]
+[ 2012-05-09, false, NULL ]
#DROP TABLE datepoint_wrong;
#DROP TABLE datepoint_correct1;
#DROP TABLE datepoint_correct2;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
