Changeset: 87e0dac22e7d for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=87e0dac22e7d
Added Files:
sql/test/BugTracker-2013/Tests/case_when.Bug-3395.sql
Modified Files:
sql/test/BugTracker-2013/Tests/All
Branch: Feb2013
Log Message:
Added a new bug report
diffs (157 lines):
diff --git a/sql/test/BugTracker-2013/Tests/All
b/sql/test/BugTracker-2013/Tests/All
--- a/sql/test/BugTracker-2013/Tests/All
+++ b/sql/test/BugTracker-2013/Tests/All
@@ -52,3 +52,4 @@ python-explain.Bug-3380
case-orderby.Bug-3388
unop_vs_aggr.Bug-3885
singleton-median.Bug-3389
+case_when.Bug-3395
diff --git a/sql/test/BugTracker-2013/Tests/case_when.Bug-3395.sql
b/sql/test/BugTracker-2013/Tests/case_when.Bug-3395.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2013/Tests/case_when.Bug-3395.sql
@@ -0,0 +1,144 @@
+create table aaa1 (a varchar(100), b varchar(100), c int);
+create table aaa2 (a varchar(100), b varchar(100), c decimal(9,6));
+create table aaa3 (a varchar(100), b varchar(100), c double);
+
+insert into aaa1 values ('a1aaaaaaaaaaaaaaaaa', 'b1bbbbbbbbbbbbbbbbbb', 100);
+insert into aaa1 values ('a2aaaaaaaaaaaaaaaaa', 'b2bbbbbbbbbbbbbbbbbb', 100);
+insert into aaa1 values ('a3aaaaaaaaaaaaaaaaa', 'b3bbbbbbbbbbbbbbbbbb', 100);
+
+insert into aaa2 values ('a1aaaaaaaaaaaaaaaaa', 'b1bbbbbbbbbbbbbbbbbb', 100);
+insert into aaa2 values ('a2aaaaaaaaaaaaaaaaa', 'b2bbbbbbbbbbbbbbbbbb', 100);
+insert into aaa2 values ('a3aaaaaaaaaaaaaaaaa', 'b3bbbbbbbbbbbbbbbbbb', 100);
+
+insert into aaa3 values ('a1aaaaaaaaaaaaaaaaa', 'b1bbbbbbbbbbbbbbbbbb', 100.0);
+insert into aaa3 values ('a2aaaaaaaaaaaaaaaaa', 'b2bbbbbbbbbbbbbbbbbb', 100.0);
+insert into aaa3 values ('a3aaaaaaaaaaaaaaaaa', 'b3bbbbbbbbbbbbbbbbbb', 100.0);
+
+SELECT a, CASE WHEN cast (c >=100.000000
+ AND c <=200.000000 THEN 'IntValue1' WHEN c>=200.000000
+ AND c <=300.000000 THEN 'IntValue2' ELSE 'Out of range' END AS
+CaseSentence
+FROM aaa1
+WHERE
+(
+ a = 'aaa'
+ and
+ (
+ (
+ CASE WHEN c>=100.000000
+ AND c<=200.000000 THEN 'IntValue1' WHEN c>=200.000000
+ AND c<=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
+ ) = 'IntValue1'
+ )
+)
+or
+(
+ a = 'bbb'
+ and
+ (
+ (
+ CASE WHEN c>=100.000000
+ AND c<=200.000000 THEN 'IntValue1' WHEN c>=200.000000
+ AND c<=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
+ ) = 'IntValue2'
+ )
+);
+
+-- an explicit cast function
+SELECT a, CASE WHEN cast (c as double) >=100.000000
+ AND cast(c as double) <=200.000000 THEN 'IntValue1' WHEN cast(c as
double)>=200.000000
+ AND cast(c as double) <=300.000000 THEN 'IntValue2' ELSE 'Out of
range' END AS
+CaseSentence
+FROM aaa1
+WHERE
+(
+ a = 'aaa'
+ and
+ (
+ (
+ CASE WHEN cast(c as double)>=100.000000
+ AND cast(c as double)<=200.000000 THEN 'IntValue1' WHEN cast(c as
double)>=200.000000
+ AND cast(c as double)<=300.000000 THEN 'IntValue2' ELSE 'Out of
range' END
+ ) = 'IntValue1'
+ )
+)
+or
+(
+ a = 'bbb'
+ and
+ (
+ (
+ CASE WHEN cast(c as double)>=100.000000
+ AND cast(c as double)<=200.000000 THEN 'IntValue1' WHEN cast(c as
double)>=200.000000
+ AND cast(c as double)<=300.000000 THEN 'IntValue2' ELSE 'Out of
range' END
+ ) = 'IntValue2'
+ )
+);
+
+
+
+-- the decimal case version
+SELECT a, CASE WHEN c >=100.000000
+ AND c <=200.000000 THEN 'IntValue1' WHEN c >=200.000000
+ AND c <=300.000000 THEN 'IntValue2' ELSE 'Out of range' END AS
+CaseSentence
+FROM aaa2
+WHERE
+(
+ a = 'aaa'
+ and
+ (
+ (
+ CASE WHEN c >=100.000000
+ AND c <=200.000000 THEN 'IntValue1' WHEN c >=200.000000
+ AND c <=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
+ ) = 'IntValue1'
+ )
+)
+or
+(
+ a = 'bbb'
+ and
+ (
+ (
+ CASE WHEN c >=100.000000
+ AND c <=200.000000 THEN 'IntValue1' WHEN c >=200.000000
+ AND c <=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
+ ) = 'IntValue2'
+ )
+);
+
+-- The double version
+SELECT a, CASE WHEN cast (c >=100.000000
+ AND c <=200.000000 THEN 'IntValue1' WHEN c>=200.000000
+ AND c <=300.000000 THEN 'IntValue2' ELSE 'Out of range' END AS
+CaseSentence
+FROM aaa1
+WHERE
+(
+ a = 'aaa'
+ and
+ (
+ (
+ CASE WHEN c>=100.000000
+ AND c<=200.000000 THEN 'IntValue1' WHEN c>=200.000000
+ AND c<=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
+ ) = 'IntValue1'
+ )
+)
+or
+(
+ a = 'bbb'
+ and
+ (
+ (
+ CASE WHEN c>=100.000000
+ AND c<=200.000000 THEN 'IntValue1' WHEN c>=200.000000
+ AND c<=300.000000 THEN 'IntValue2' ELSE 'Out of range' END
+ ) = 'IntValue2'
+ )
+);
+
+drop table aaa1;
+drop table aaa2;
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
