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] Updated: (DERBY-2256) Wrong Results: Use of decimal values in an IN-list with INTEGER left operand can lead to extra rows.
Date Wed, 21 Mar 2007 23:22:32 GMT

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

A B updated DERBY-2256:
-----------------------

    Attachment: d2256_v1.stat
                d2256_v1.patch

Posting d2256_v1.patch, which is a first attempt at a patch for this issue.  The general idea
is to always make sure that IN-list comparisons are done with the "dominant" type when two
values have different type precedences.  More specifically:

  - When determining the "judge" type in InListOperatorNode.preprocess(), iterate through
    all of the values to find out what the dominant type is, and then use that as the
    "judge" for sorting.  Prior to these changes we just used the type of the left operand
    as judge, but that was not correct (as mentioned in my previous comment).

    The obvious downside to this approach is that we may have to iterate through a potentially
    large list of IN values in order to determine the dominant type.  I thought about leaving
    InListOpNode.preprocess() alone and adding new logic to ValueNodeList.sortInAscendingOrder()
    to account for different precedences, but it seemed to me like that wouldn't really save
    us anything and it is not as "clean" as the InListOpNode.preprocess() changes.

    Of course I am open to suggestions if anyone disagrees here.

  - At execution time (i.e. in DataType.in()), add logic to ensure that all search
    comparisons are done using the dominant type of the values being compared.

    An alternative here would have been to explicitly _cast_ the IN-list values to
    the dominant type during preprocessing, in which case we would have satisfied
    the original assumption in DataType.in() and thus no changes to that file would
    have been necessary.  But in the end I think it is quite a bit more costly to
    add CAST nodes to every IN value at compile time than it is to just do a
    precedence check at execution-time (which is what d2256_v1.patch does).

    Again, I am willing to change this approach if anyone feels strongly about it.

  - Added the test cases referenced in this Jira to the lang/inbetween.sql test.

I ran derbyall and suites.All on Red Hat Linux with ibm142 and saw no new failures. I think
the changes are fairly contained and my hope is that the comments are sufficient, so if anyone
is available for a review, that'd be great... 

> 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
>         Assigned To: A B
>         Attachments: d2256_v1.patch, d2256_v1.stat
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message