phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-1225) Evaluation of OR between columns in PK and not in PK incorrect
Date Wed, 03 Sep 2014 02:59:51 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-1225?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14119229#comment-14119229
] 

ASF GitHub Bot commented on PHOENIX-1225:
-----------------------------------------

Github user cancylin closed the pull request at:

    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: 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)

Mime
View raw message