[
https://issues.apache.org/jira/browse/DERBY-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12562125#action_12562125
]
A B commented on DERBY-3279:
----------------------------
> What if there was a multi-column index [...] if we decided to do in-list
> multi-probing
> against the NAME_IDX, would we have an ambiguity about whether we wanted to
> sort the (LAST_NAME,FIRST_NAME) values in ASC or DESC order?
Great question, Bryan. Thanks for bringing it up!
I think the answer is, in fact, that "this isn't possible". A given
multi-probing result set only ever works with a single column--even if that
column is part of a multi-column index. See the following comments from
TableScanResultSet:
* Note that it *is* possible for a start/stop key to contain more
* than one column (ex. if we're scanning a multi-column index). In
* that case we plug probeValue into the first column of the start
* and/or stop key and leave the rest of the key as it is. As an
* example, assume we have the following predicates:
*
* ... where d in (1, 20000) and b > 200 and b <= 500
*
* And assume further that we have an index defined on (d, b).
* In this case it's possible that we have TWO start predicates
* and TWO stop predicates: the IN list will give us "d = probeVal",
* which is a start predicate and a stop predicate; then "b > 200"
* may give us a second start predicate, while "b <= 500" may give
* us a second stop predicate. So in this situation we want our
* start key to be:
*
* (probeValue, 200)
*
* and our stop key to be:
*
* (probeValue, 500).
*
* This will effectively limit the scan so that it only returns
* rows whose "D" column equals probeValue and whose "B" column
* falls in the range of 200 thru 500.
*
* Note: Derby currently only allows a single start/stop predicate
* per column. See PredicateList.orderUsefulPredicates().
It's also worth noting that Derby will only do multi-probing IF the column in
question is the FIRST column in the index. This comes from the comments in
PredicateList.orderUsefulPredicates():
else if (pred.isInListProbePredicate()
&& (indexPosition > 0))
{
/* If the predicate is an IN-list probe predicate
* then we only consider it to be useful if the
* referenced column is the *first* one in the
* index (i.e. if (indexPosition == 0)). Otherwise
* the predicate would be treated as a qualifier
* for store, which could lead to incorrect
* results.
*/
indexCol = null;
}
So in the example you gave above, we would (should) never do multi-probing for
FIRST_NAME. We would either do multi-probing based on LAST_NAME, in which case
the sort would have to be ASC, or we would do no multi-probing at all. The
FIRST_NAME IN list would be treated as a "normal" (non-probing) IN list
operator.
At least, that's the theory. If you'd like me to add a test case for that,
just to be sure, let me know and I can certainly do so.
If this still isnt' clear, please feel free to ask again. And thanks, as
always, for your great comments!
> Derby 10.3.X ignores ORDER BY DESC when target column has an index and is
> used in an OR clause or an IN list.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3279
> URL: https://issues.apache.org/jira/browse/DERBY-3279
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1
> Environment: Rational Application Developer 7.0.0.2 (Eclipse 3.2.2),
> J2RE 1.5.0 IBM J9 2.3 Windows XP
> Reporter: Ajay Bhala
> Assignee: A B
> Attachments: d3279_v1.patch
>
>
> Running the following produces the error seen in Derby 10.3.X but not in
> 10.2.X nor in 10.1.X.
> Don't know if this related to DERBY-3231.
> First query is incorrectly sorted whereas the second one is okay when there
> is an index on the table.
> If the table is not indexed, the sort works correctly in DESC order.
> ------
> create table CHEESE (
> CHEESE_CODE VARCHAR(5),
> CHEESE_NAME VARCHAR(20),
> CHEESE_COST DECIMAL(7,4)
> );
> create index cheese_index on CHEESE (CHEESE_CODE DESC, CHEESE_NAME DESC,
> CHEESE_COST DESC);
> INSERT INTO CHEESE (
> CHEESE_CODE,
> CHEESE_NAME,
> CHEESE_COST)
> VALUES ('00000', 'GOUDA', 001.1234),
> ('00000', 'EDAM', 002.1111),
> ('54321', 'EDAM', 008.5646),
> ('12345', 'GORGONZOLA', 888.2309),
> ('AAAAA', 'EDAM', 999.8888),
> ('54321', 'MUENSTER', 077.9545);
> SELECT * FROM CHEESE
> WHERE (CHEESE_CODE='00000' OR CHEESE_CODE='54321') AND CHEESE_NAME='EDAM'
> ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC;
> SELECT * FROM CHEESE
> WHERE (CHEESE_CODE='AAAAA' OR CHEESE_CODE='54321') AND CHEESE_NAME='EDAM'
> ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
