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";