This is an automated email from the ASF dual-hosted git repository.
vjasani pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push:
new 7f6cc3f26c PHOENIX-6960 Scan range is incorrect when query desc
columns (#1663)
7f6cc3f26c is described below
commit 7f6cc3f26ca13c324cc42499d2b65be9cb26ea6f
Author: Viraj Jasani <vjas...@apache.org>
AuthorDate: Thu Sep 14 11:21:28 2023 -0800
PHOENIX-6960 Scan range is incorrect when query desc columns (#1663)
---
.../apache/phoenix/end2end/LikeExpressionIT.java | 1569 ++++++++++++++++++++
.../org/apache/phoenix/compile/WhereOptimizer.java | 21 +-
.../apache/phoenix/iterate/ParallelIterators.java | 5 +-
.../apache/phoenix/compile/WhereOptimizerTest.java | 76 +
4 files changed, 1654 insertions(+), 17 deletions(-)
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
index 2645889992..9d724d1687 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.java
@@ -456,4 +456,1573 @@ public class LikeExpressionIT extends
ParallelStatsDisabledIT {
}
}
}
+
+ @Test
+ public void testLikeWithIndexDesc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name varchar, type integer,
status integer )");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type, name desc)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy012', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithFixedWidthIndexDesc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name char(5), type integer,
status integer )");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type, name desc)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithIndexAsc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name varchar, type integer,
status integer )");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type, name)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithFixedWidthIndexAsc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name char(5), type integer,
status integer )");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type, name)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithDesc() throws Exception {
+ String tableName = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName + " (id varchar, name
varchar, type decimal, "
+ + "status integer CONSTRAINT pk PRIMARY KEY(id desc,
type))");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyz',
'xyz' , 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyabc',
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xx',
'xx', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xz',
'xz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xxyz',
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy123',
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy',
'xy', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y', 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y012x',
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('w', 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('wxy01',
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xxyz", rs.getString(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xx", rs.getString(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithFixedWidthDesc() throws Exception {
+ String tableName = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName + " (id char(5) not null,
name varchar," +
+ " type decimal, status integer CONSTRAINT pk PRIMARY
KEY(id desc, type))");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyz',
'xyz' , 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyabc',
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xx',
'xx', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xz',
'xz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xxyz',
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy123',
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy',
'xy', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y', 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y012x',
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('w', 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('wxy01',
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xxyz", rs.getString(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xx", rs.getString(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithAsc() throws Exception {
+ String tableName = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName + " (id varchar, name
varchar, type decimal, "
+ + "status integer CONSTRAINT pk PRIMARY KEY(id, type))");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyz',
'xyz' , 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyabc',
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xx',
'xx', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xz',
'xz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xxyz',
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy123',
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy',
'xy', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y', 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y012x',
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('w', 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('wxy01',
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals("xx", rs.getString(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xxyz", rs.getString(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithFixedWidthAsc() throws Exception {
+ String tableName = generateUniqueName();
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName + " (id char(5) not null,
name varchar," +
+ " type decimal, status integer CONSTRAINT pk PRIMARY
KEY(id, type))");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyz',
'xyz' , 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xyabc',
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xx',
'xx', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xz',
'xz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xxyz',
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy123',
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('xy',
'xy', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y', 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('y012x',
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('w', 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES('wxy01',
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xy%'");
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'x%'");
+
+ assertTrue(rs.next());
+ assertEquals("xx", rs.getString(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xxyz", rs.getString(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy", rs.getString(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xy123", rs.getString(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyabc", rs.getString(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xyz", rs.getString(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'z012%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'v0%'");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and id like 'xz%'");
+
+ assertTrue(rs.next());
+ assertEquals("xz", rs.getString(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithOrderByDesc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name varchar, type integer,
status integer )");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%' order
by name desc");
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%' order
by name desc");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%' order
by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'
order by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%' order
by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%' order
by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%' order
by name desc");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithFixedWidthOrderByDesc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name char(5), type integer,
status integer )");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%' order
by name desc");
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%' order
by name desc");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%' order
by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'
order by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%' order
by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%' order
by name desc");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%' order
by name desc");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithOrderByAsc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name varchar, type integer,
status integer)");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%' order
by name");
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%' order
by name");
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%' order
by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'
order by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%' order
by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%' order
by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%' order
by name");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testLikeWithFixedWidthOrderByAsc() throws Exception {
+ String tableName = generateUniqueName();
+ String indexName = tableName + "_IDX";
+
+ try (Connection conn = DriverManager.getConnection(getUrl());
+ Statement stmt = conn.createStatement()) {
+ stmt.execute("CREATE TABLE " + tableName +
+ " (id integer primary key, name char(5), type integer,
status integer)");
+
+ stmt.execute("CREATE INDEX " + indexName + " ON " + tableName +
+ "(status, type)");
+
+ conn.commit();
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(1, 'xyz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(2,
'xyabc', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(3, 'xx',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(4, 'xz',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(5,
'xxyz', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(6,
'xy123', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(7, 'xy',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'y',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'y012x', 1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(8, 'w',
1, 1)");
+ stmt.executeUpdate("UPSERT INTO " + tableName + " VALUES(9,
'wxy01', 1, 1)");
+ conn.commit();
+
+ ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xy%' order
by name");
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'x%' order
by name");
+
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals("xx", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ assertEquals("xxyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(7, rs.getInt(1));
+ assertEquals("xy", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals("xy123", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals("xyabc", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals("xyz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z%' order
by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'z012%'
order by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v%' order
by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'v0%' order
by name");
+ assertFalse(rs.next());
+
+ rs = stmt.executeQuery("SELECT * FROM " + tableName +
+ " where type = 1 and status = 1 and name like 'xz%' order
by name");
+
+ assertTrue(rs.next());
+ assertEquals(4, rs.getInt(1));
+ assertEquals("xz", rs.getString(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+
+ assertFalse(rs.next());
+ }
+ }
+
}
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
index b7a3d0aa2d..7da2c62c41 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
@@ -1691,8 +1691,10 @@ public class WhereOptimizer {
KeySlots childSlots = childParts.get(0);
KeySlot childSlot = childSlots.getSlots().get(0);
final String startsWith = node.getLiteralPrefix();
- SortOrder sortOrder = node.getChildren().get(0).getSortOrder();
- byte[] key = PVarchar.INSTANCE.toBytes(startsWith, sortOrder);
+ // TODO: is there a case where we'd need to go through the
childPart to calculate the key range?
+ PColumn column = childSlot.getKeyPart().getColumn();
+ PDataType type = column.getDataType();
+ byte[] key = PVarchar.INSTANCE.toBytes(startsWith, SortOrder.ASC);
// If the expression is an equality expression against a fixed
length column
// and the key length doesn't match the column length, the
expression can
// never be true.
@@ -1702,9 +1704,6 @@ public class WhereOptimizer {
if (childNodeFixedLength != null && key.length >
childNodeFixedLength) {
return EMPTY_KEY_SLOTS;
}
- // TODO: is there a case where we'd need to go through the
childPart to calculate the key range?
- PColumn column = childSlot.getKeyPart().getColumn();
- PDataType type = column.getDataType();
byte[] lowerRange = key;
byte[] upperRange = ByteUtil.nextKey(key);
Integer columnFixedLength = column.getMaxLength();
@@ -1715,17 +1714,9 @@ public class WhereOptimizer {
lowerRange = type.pad(lowerRange, columnFixedLength,
SortOrder.ASC);
upperRange = type.pad(upperRange, columnFixedLength,
SortOrder.ASC);
}
- } else if (column.getSortOrder() == SortOrder.DESC &&
table.rowKeyOrderOptimizable()) {
- // Append a zero byte if descending since a \xFF byte will be
appended to the lowerRange
- // causing rows to be skipped that should be included. For
example, with rows 'ab', 'a',
- // a lowerRange of 'a\xFF' would skip 'ab', while 'a\x00\xFF'
would not.
- lowerRange = Arrays.copyOf(lowerRange, lowerRange.length+1);
- lowerRange[lowerRange.length-1] =
QueryConstants.SEPARATOR_BYTE;
- }
- KeyRange range = type.getKeyRange(lowerRange, true, upperRange,
false, SortOrder.ASC);
- if (column.getSortOrder() == SortOrder.DESC) {
- range = range.invert();
}
+ KeyRange range = type.getKeyRange(lowerRange, true, upperRange,
false,
+ SortOrder.ASC);
// Only extract LIKE expression if pattern ends with a wildcard
and everything else was extracted
return newKeyParts(childSlot, node.endsWithOnlyWildcard() ? node :
null, range);
}
diff --git
a/phoenix-core/src/main/java/org/apache/phoenix/iterate/ParallelIterators.java
b/phoenix-core/src/main/java/org/apache/phoenix/iterate/ParallelIterators.java
index 5b0b04b05f..9f26faab8a 100644
---
a/phoenix-core/src/main/java/org/apache/phoenix/iterate/ParallelIterators.java
+++
b/phoenix-core/src/main/java/org/apache/phoenix/iterate/ParallelIterators.java
@@ -141,8 +141,9 @@ public class ParallelIterators extends BaseResultIterators {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(LogUtil.addCustomAnnotations("Id: " +
scanId + ", Time: " +
- (EnvironmentEdgeManager.currentTimeMillis() -
startTime) +
- "ms, Scan: " + scan,
ScanUtil.getCustomAnnotations(scan)));
+
(EnvironmentEdgeManager.currentTimeMillis() - startTime) +
+ "ms, Table: " + physicalTableName + ",
Scan: " + scan,
+ ScanUtil.getCustomAnnotations(scan)));
}
allIterators.add(iterator);
diff --git
a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
index cdf14ccd9c..20bd097e04 100644
---
a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
+++
b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
@@ -1206,6 +1206,82 @@ public class WhereOptimizerTest extends
BaseConnectionlessQueryTest {
assertArrayEquals(stopRow, scan.getStopRow());
}
+ @Test
+ public void testLikeExpressionWithDescOrder() throws SQLException {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String tableName = generateUniqueName();
+ conn.createStatement().execute(
+ "CREATE TABLE " + tableName + " (id varchar, name varchar,
type decimal, "
+ + "status integer CONSTRAINT pk PRIMARY KEY(id desc,
type))");
+ String query = "SELECT * FROM " + tableName + " where type = 1 and id
like 'xy%'";
+ StatementContext context = compileStatement(query);
+ Scan scan = context.getScan();
+
+ assertTrue(scan.getFilter() instanceof SkipScanFilter);
+ SkipScanFilter filter = (SkipScanFilter) scan.getFilter();
+
+ byte[] lowerRange = filter.getSlots().get(0).get(0).getLowerRange();
+ byte[] upperRange = filter.getSlots().get(0).get(0).getUpperRange();
+ boolean lowerInclusive =
filter.getSlots().get(0).get(0).isLowerInclusive();
+ boolean upperInclusive =
filter.getSlots().get(0).get(0).isUpperInclusive();
+
+ byte[] startRow = PVarchar.INSTANCE.toBytes("xy");
+ byte[] invStartRow = new byte[startRow.length];
+ SortOrder.invert(startRow, 0, invStartRow, 0, startRow.length);
+
+ byte[] stopRow = PVarchar.INSTANCE.toBytes("xz");
+ byte[] invStopRow = new byte[startRow.length];
+ SortOrder.invert(stopRow, 0, invStopRow, 0, stopRow.length);
+
+ assertArrayEquals(invStopRow, lowerRange);
+ assertArrayEquals(invStartRow, upperRange);
+ assertFalse(lowerInclusive);
+ assertTrue(upperInclusive);
+
+ byte[] expectedStartRow = ByteUtil.concat(invStartRow, new byte[]{0},
+ PDecimal.INSTANCE.toBytes(new BigDecimal(1)));
+ assertArrayEquals(expectedStartRow, scan.getStartRow());
+
+ byte[] expectedStopRow = ByteUtil.concat(invStartRow,
+ new byte[]{(byte) (0xFF)}, PDecimal.INSTANCE.toBytes(new
BigDecimal(1)),
+ new byte[]{1});
+ assertArrayEquals(expectedStopRow, scan.getStopRow());
+
+ query = "SELECT * FROM " + tableName + " where type = 1 and id like
'x%'";
+ context = compileStatement(query);
+ scan = context.getScan();
+
+ assertTrue(scan.getFilter() instanceof SkipScanFilter);
+ filter = (SkipScanFilter) scan.getFilter();
+
+ lowerRange = filter.getSlots().get(0).get(0).getLowerRange();
+ upperRange = filter.getSlots().get(0).get(0).getUpperRange();
+ lowerInclusive = filter.getSlots().get(0).get(0).isLowerInclusive();
+ upperInclusive = filter.getSlots().get(0).get(0).isUpperInclusive();
+
+ startRow = PVarchar.INSTANCE.toBytes("x");
+ invStartRow = new byte[startRow.length];
+ SortOrder.invert(startRow, 0, invStartRow, 0, startRow.length);
+
+ stopRow = PVarchar.INSTANCE.toBytes("y");
+ invStopRow = new byte[startRow.length];
+ SortOrder.invert(stopRow, 0, invStopRow, 0, stopRow.length);
+
+ assertArrayEquals(invStopRow, lowerRange);
+ assertArrayEquals(invStartRow, upperRange);
+ assertFalse(lowerInclusive);
+ assertTrue(upperInclusive);
+
+ expectedStartRow = ByteUtil.concat(invStartRow, new byte[]{0},
+ PDecimal.INSTANCE.toBytes(new BigDecimal(1)));
+ assertArrayEquals(expectedStartRow, scan.getStartRow());
+
+ expectedStopRow = ByteUtil.concat(invStartRow,
+ new byte[]{(byte) (0xFF)}, PDecimal.INSTANCE.toBytes(new
BigDecimal(1)),
+ new byte[]{1});
+ assertArrayEquals(expectedStopRow, scan.getStopRow());
+ }
+
@Test
public void testLikeNoWildcardExpression() throws SQLException {
String tenantId = "000000000000001";
