phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Created] (PHOENIX-1225) Evaluation of OR between columns in PK and not in PK incorrect
Date Sat, 30 Aug 2014 22:05:52 GMT
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)

Mime
View raw message