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 Thu, 04 Sep 2014 06:51:52 GMT

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

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

Github user JamesRTaylor commented on a diff in the pull request:

    https://github.com/apache/phoenix/pull/9#discussion_r17097884
  
    --- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/EvaluationOfORIT.java ---
    @@ -0,0 +1,203 @@
    +package org.apache.phoenix.end2end;
    +
    + 
    +import static org.apache.phoenix.util.TestUtil.ROW5;
    +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
    +import static org.junit.Assert.*;
    +
    + 
    +import java.sql.Connection;
    +import java.sql.Date;
    +import java.sql.DriverManager;
    +import java.sql.PreparedStatement;
    +import java.sql.ResultSet;
    +import java.sql.SQLException;
    +import java.util.Properties;
    +
    +import org.apache.phoenix.util.PhoenixRuntime;
    +import org.junit.Before;
    +import org.junit.Test;
    +
    +public class EvaluationOfORIT extends BaseClientManagedTimeIT{
    +	private static final String tenantId = getOrganizationId();
    +	private long ts;
    +	private Date date;
    +	
    +	@Before
    +        public void initTable() throws Exception {
    +            ts = nextTimestamp();
    +            initATableValues(tenantId, getDefaultSplits(tenantId), date=new Date(System.currentTimeMillis()),
ts);        
    +        }
    +	
    +	@Test
    +	public void testPKOrNotPKInOREvaluation() throws SQLException {
    +            Connection conn = DriverManager.getConnection(getUrl());
    +            String create = "CREATE TABLE DIE ( ID INTEGER NOT NULL PRIMARY KEY,NAME
VARCHAR(50) NOT NULL)";
    +            PreparedStatement createStmt = conn.prepareStatement(create);
    +            createStmt.executeUpdate();
    +            conn.close();
    +            Connection conn1 = DriverManager.getConnection(getUrl());
    +            PreparedStatement stmt = conn1.prepareStatement(
    +                    "upsert into " +
    +                    "DIE VALUES (?, ?)");
    +            stmt.setInt(1, 1);
    +            stmt.setString(2, "Tester1");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,2);
    +            stmt.setString(2, "Tester2");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,3);
    +            stmt.setString(2, "Tester3");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,4);
    +            stmt.setString(2, "LikeTester1");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,5);
    +            stmt.setString(2, "LikeTester2");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,6);
    +            stmt.setString(2, "LikeTesterEnd");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,7);
    +            stmt.setString(2, "LikeTesterEnd2");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,8);
    +            stmt.setString(2, "Tester3");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,9);
    +            stmt.setString(2, "Tester4");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,10);
    +            stmt.setString(2, "Tester5");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,11);
    +            stmt.setString(2, "Tester6");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,12);
    +            stmt.setString(2, "tester6");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,13);
    +            stmt.setString(2, "lester1");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,14);
    +            stmt.setString(2, "le50ster1");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,15);
    +            stmt.setString(2, "LE50ster1");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,16);
    +            stmt.setString(2, "LiketesterEnd");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,17);
    +            stmt.setString(2, "la50ster1");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,18);
    +            stmt.setString(2, "lA50ster0");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,19);
    +            stmt.setString(2, "lA50ster2");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,20);
    +            stmt.setString(2, "la50ster0");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,21);
    +            stmt.setString(2, "la50ster2");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,22);
    +            stmt.setString(2, "La50ster3");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,23);
    +            stmt.setString(2, "la50ster3");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,24);
    +            stmt.setString(2, "l[50ster3");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,25);
    +            stmt.setString(2, "Tester1");
    +            stmt.execute();
    +            
    +            stmt.setInt(1,26);
    +            stmt.setString(2, "Tester100");
    +            stmt.execute();		   
    +            
    +            conn1.commit();
    +            conn1.close();
    +            
    +            Connection conn2 = DriverManager.getConnection(getUrl());
    +            String select = "Select * from DIE where ID=6 or Name between 'Tester1' and
'Tester3'";
    +            ResultSet rs;
    +            rs = conn2.createStatement().executeQuery(select);
    +            assertTrue(rs.next());
    +            assertEquals(1,rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(2,rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(3,rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(6,rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(8,rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(25,rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(26,rs.getInt(1));		        
    +            conn2.close();               
    +	}
    +    
    +	@Test
    +        public void testUnfoundSingleColumnCaseStatement() throws Exception {
    --- End diff --
    
    Unless there's another, different bug, this test is not necessary and should be removed.


> 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