db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2256) Wrong Results: Use of decimal values in an IN-list with INTEGER left operand can lead to extra rows.
Date Fri, 19 Jan 2007 16:20:30 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12466094
] 

A B commented on DERBY-2256:
----------------------------

There is code in InListOperatorNode.java that creates a BETWEEN clause from an IN-list if
the size of the IN list is greater than 1 and all values are constants:

	if (rightOperandList.size() == 1)
	{
		// just use an equality predicate
	}
	else if ((leftOperand instanceof ColumnReference) &&
		 rightOperandList.containsAllConstantNodes())
	{
		// convert to BETWEEN.
	}

As Bryan Pendleton pointed out on derby-dev, it looks like that could have something to do
with why

  select * from t1 where i in (2.8, 4.23);

returns a row ("4" is between 2.8 and 4.23).  It also explains why

  select * from t1 where i in (4.23);

correctly returns zero rows (it is simply treated as an equality predicate).

It's not clear to me how (or if) that explains the other error case posted by Yip above (thanks
Yip!), but maybe it's related?

> 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.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.1.3.2,
10.1.4.0, 10.2.1.6, 10.2.2.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

        

Mime
View raw message