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 07:12:51 GMT

    [ 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.
    ![psqloutput](https://cloud.githubusercontent.com/assets/8636229/4130494/715d628c-3338-11e4-99c1-e2a077820415.png)
    
    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)

Mime
View raw message