[
https://issues.apache.org/jira/browse/PHOENIX-1225?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14119484#comment-14119484
]
ASF GitHub Bot commented on PHOENIX-1225:
-----------------------------------------
Github user cancylin commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/9#discussion_r17034179
--- Diff:
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java ---
@@ -685,7 +685,7 @@ private KeySlots orKeySlots(OrExpression orExpression,
List<KeySlots> childSlots
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
+ return null; //If one childSlot does not have
the PK columns, let Phoenix scan all the key ranges of the table.
--- End diff --
I download the DBTools from http://www.dbtools.com.br/EN/queryit/.
Test the query in postgresql.
After modify the WhereOptiMizer.java.
Phoenix outputs the same result.
ID NAME ADDRESS SBIN MONEY NUMBER
INDATE PASSWD
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
1 Tester1 HSINCHU 0.0 1.1
2012-12-23 10:00:00 6655447788
2 Tester2 0.0 2.1
2012-10-25 14:00:00 11223344
3 Tester3 0.0 2.0
2012-12-25 10:09:53 6654321897
6 LikeTesterEnd 0.0 7.1
2012-12-25 11:00:00 9876543210
8 Tester3 Taipei 0.0 1.0
2011-12-25 10:00:00 5678
25 Tester1 HSINCHUU 0.0 1.1
2012-12-23 10:00:00 6655447788
26 Tester100 HSINCHUUXX 12.03 100.1
2010-10-10 10:10:10 pass
> 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: 5.0.0, 3.1, 4.1
> Reporter: James Taylor
>
> 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
(v6.3.4#6332)
