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