[
https://issues.apache.org/jira/browse/PHOENIX-1225?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17749504#comment-17749504
]
ASF GitHub Bot commented on PHOENIX-1225:
-----------------------------------------
stoty closed pull request #9: PHOENIX-1225
URL: https://github.com/apache/phoenix/pull/9
> Evaluation of OR between columns in PK and not in PK incorrect
> --------------------------------------------------------------
>
> Key: PHOENIX-1225
> URL: https://issues.apache.org/jira/browse/PHOENIX-1225
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 3.1.0, 4.1.0
> Reporter: James R. Taylor
> Assignee: NTHU-SSLAB
> Priority: Major
> Fix For: 4.2.0, 3.2.0
>
>
> When I run the sql query "select * from DIE where ID=6 or Name between
> 'Tester1' and 'Tester3', It gets the different result from mysql or postgres.
> Attache is included about the schema of table DIE and inserted data.
> In the query result of mysql or postgres,
> ----------------------------------------------------------------------------------------------
> | ID | NAME | ADDRESS | SBIN | MONEY | NUMBER | inDate
> | passwd |
> ----------------------------------------------------------------------------------------------
> | 1 | Tester1 | HSINCHU | | 0 | 1.1 | 2012-12-23
> 10:00:00 | 6655447788 |
> | 2 | Tester2 | | | 0 | 2.1 | 2012-10-25
> 14:00:00 | 11223344 |
> | 3 | Tester3 | | | 0 | 2 | 2012-12-25
> 10:09:53 | 6654321897 |
> | 6 | LikeTesterEnd | | | 0 | 7.1 | 2012-12-25
> 11:00:00 | 9876543210 |
> | 8 | Tester3 | Taipei | | 0 | 1 | 2011-12-25
> 10:00:00 | 5678 |
> | 25 | Tester1 | HSINCHUU | | 0 | 1.1 | 2012-12-23
> 10:00:00 | 6655447788 |
> | 26 | Tester100 | HSINCHUUXX | | 12.03 | 100.1 | 2010-10-10
> 10:10:10 | pass |
> ----------------------------------------------------------------------------------------------
> However, In phoenix, the result is only one row.
> ----------------------------------------------------------------------------------------------
> | ID | NAME | ADDRESS | SBIN | MONEY | NUMBER | inDate
> | passwd |
> ----------------------------------------------------------------------------------------------
> | 6 | LikeTesterEnd | | | 0 | 7.1 | 2012-12-25
> 11:00:00 | 9876543210 |
> ----------------------------------------------------------------------------------------------
> After trace the code, I find the reason is because Phoenix will do where
> optimizer, and shorten the scan key ranges.
> However, the "name" is not the key, so it needs Phoenix do the scan
> filter during all the key ranges.
> I try to modify the code, WhereOptimizer.java, and it can let Phoenix
> get the right result.
> My thought is if one of childSlot has null keyslot, it needs to search
> all ranges.
>
> ---------------------- Modify Code In
> WhereOptimizer.java---------------------------------
> private KeySlots orKeySlots(OrExpression orExpression, List<KeySlots>
> childSlots) {
> ---Ignore---
> // TODO: Do the same optimization that we do for IN if the
> childSlots specify a fully qualified row key
> for (KeySlot slot : childSlot) {
> // We have a nested OR with nothing for this slot, so
> continue
> if (slot == null) {
> //continue; // FIXME:I don't think this is ever
> necessary //mark by Kathy
> return null; // modify by Kathy
> }
> ---Ignore---
> }
>
> ---------------------------------------------------------------------------------------------------------------------------------------
> After testing other sql query, Phoenix will also do well.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
