Wrong Results: Use of decimal values in an IN-list with INTEGER left operand
can lead to extra rows.
----------------------------------------------------------------------------------------------------
Key: DERBY-2256
URL: https://issues.apache.org/jira/browse/DERBY-2256
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.2.0, 10.2.1.6, 10.1.3.1, 10.1.2.1, 10.1.1.0,
10.0.2.1, 10.0.2.0, 10.0.2.2, 10.1.3.2, 10.1.4.0, 10.2.2.1, 10.2.3.0, 10.3.0.0
Reporter: A B
While trying out some code changes for DERBY-47 I was running a few test cases
and happened to notice that there are a couple of cases in which Derby behaves
incorrectly (that or my understanding of what should be happening here is way
off).
First and most simply: the following query should return zero rows (unless I'm
missing something?), but it returns one:
ij> create table t1 (i int);
0 rows inserted/updated/deleted
ij> insert into t1 values 1, 2, 3, 4, 5;
5 rows inserted/updated/deleted
-- Correct returns zero rows.
ij> select * from t1 where i in (4.23);
I
-----------
0 rows selected
-- But this one returns 1 row...
ij> select * from t1 where i in (2.8, 4.23);
I
-----------
4
1 row selected
Secondly, if the IN-list contains a non-constant value then Derby can
incorrectly return rows that do not match the IN predicate. I think this is
because some internal casting is happening when it shouldn't?
ij> create table t1 (i int);
0 rows inserted/updated/deleted
ij> insert into t1 values 1, 2, 3, 4, 5;
5 rows inserted/updated/deleted
-- Following values clause returns "2.80", as expected.
ij> values cast (2.8 as decimal(4, 2));
1
-------
2.80
1 row selected
-- But if we use it in an IN-list it gets cast to "2" and thus returns a match.
-- We get 2 rows when we should get NONE.
ij> select * from t1 where i in (cast (2.8 as decimal(4, 2)), 4.23);
I
-----------
2
4
2 rows selected
I confirmed that we see these results on trunk, 10.2, 10.1, and even as far
back as svn #201660 for 10.0. I also ran the above statements on DB2 v8 as a
sanity check to confirm that NO results were returned there.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
