James Taylor created PHOENIX-1225:
-------------------------------------
Summary: 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.2#6252)
