This is an automated email from the ASF dual-hosted git repository. vjasani pushed a commit to branch 5.1 in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/5.1 by this push: new 48b3cb5b01 PHOENIX-7046 Query results return different values when PKs of view have DESC order (#1689) 48b3cb5b01 is described below commit 48b3cb5b01706c9d6e46393987afedf04e34178e Author: Viraj Jasani <vjas...@apache.org> AuthorDate: Tue Oct 10 20:57:31 2023 -0800 PHOENIX-7046 Query results return different values when PKs of view have DESC order (#1689) --- .../apache/phoenix/end2end/DescOrderKeysIT.java | 904 +++++++++++++++++++++ .../apache/phoenix/compile/ExpressionCompiler.java | 4 +- .../phoenix/compile/IndexStatementRewriter.java | 2 +- .../apache/phoenix/compile/ProjectionCompiler.java | 5 +- 4 files changed, 912 insertions(+), 3 deletions(-) diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DescOrderKeysIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DescOrderKeysIT.java index b72a239a5d..8fb2ee5cb3 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DescOrderKeysIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DescOrderKeysIT.java @@ -439,5 +439,909 @@ public class DescOrderKeysIT extends ParallelStatsDisabledIT { } } + @Test + public void testViewWithIntDescPkColumn() throws Exception { + final String tableName = generateUniqueName(); + final String view01 = "v01_" + tableName; + final String view02 = "v02_" + tableName; + + try (Connection conn = DriverManager.getConnection(getUrl())) { + final Statement stmt = conn.createStatement(); + + stmt.execute("CREATE TABLE " + tableName + + " (COL1 VARCHAR(10) NOT NULL, COL2 CHAR(5) NOT NULL, COL3 VARCHAR," + + " COL4 VARCHAR CONSTRAINT pk PRIMARY KEY(COL1, COL2))"); + stmt.execute("CREATE VIEW " + view01 + + " (VCOL1 INTEGER NOT NULL, COL5 VARCHAR CONSTRAINT pk PRIMARY KEY(VCOL1 DESC))" + + " AS SELECT * FROM " + tableName + " WHERE COL1 = 'col1'"); + stmt.execute("CREATE VIEW " + view02 + + " (VCOL2 CHAR(10) NOT NULL, COL6 VARCHAR CONSTRAINT pk PRIMARY KEY(VCOL2))" + + " AS SELECT * FROM " + view01 + " WHERE VCOL1 = 1"); + + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0001', 'vcol2_01', 'col5_01', 'col6_01')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0002', 'vcol2_02', 'col5_02', 'col6_02')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0003', 'vcol2_03', 'col5_03', 'col6_03')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0004', 2, 'col3_04', 'col4_04', 'col5_04')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0005', -2, 'col3_05', 'col4_05', 'col5_05')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0006', -1, 'col3_06', 'col4_06', 'col5_06')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0007', 1, 'col3_07', 'col4_07', 'col5_07')"); + conn.commit(); + + ResultSet rs = stmt.executeQuery("SELECT COL1, COL2, VCOL1 FROM " + view01); + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0001", rs.getString(2)); + Assert.assertEquals(1, rs.getInt(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertEquals(1, rs.getInt(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0003", rs.getString(2)); + Assert.assertEquals(1, rs.getInt(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0004", rs.getString(2)); + Assert.assertEquals(2, rs.getInt(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0005", rs.getString(2)); + Assert.assertEquals(-2, rs.getInt(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0006", rs.getString(2)); + Assert.assertEquals(-1, rs.getInt(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0007", rs.getString(2)); + Assert.assertEquals(1, rs.getInt(3)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT COL2, VCOL1, VCOL2, COL5, COL6 FROM " + view02); + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0001", rs.getString(1)); + Assert.assertEquals(1, rs.getInt(2)); + Assert.assertEquals("vcol2_01", rs.getString(3)); + Assert.assertEquals("col5_01", rs.getString(4)); + Assert.assertEquals("col6_01", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0002", rs.getString(1)); + Assert.assertEquals(1, rs.getInt(2)); + Assert.assertEquals("vcol2_02", rs.getString(3)); + Assert.assertEquals("col5_02", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0003", rs.getString(1)); + Assert.assertEquals(1, rs.getInt(2)); + Assert.assertEquals("vcol2_03", rs.getString(3)); + Assert.assertEquals("col5_03", rs.getString(4)); + Assert.assertEquals("col6_03", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0007", rs.getString(1)); + Assert.assertEquals(1, rs.getInt(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertEquals("col5_07", rs.getString(4)); + Assert.assertNull(rs.getString(5)); + + Assert.assertFalse(rs.next()); + } + } + + @Test + public void testViewWithCharDescPkColumn() throws Exception { + final String tableName = generateUniqueName(); + final String view01 = "v01_" + tableName; + final String view02 = "v02_" + tableName; + + try (Connection conn = DriverManager.getConnection(getUrl())) { + final Statement stmt = conn.createStatement(); + + stmt.execute("CREATE TABLE " + tableName + + " (COL1 VARCHAR(10) NOT NULL, COL2 CHAR(5) NOT NULL, COL3 VARCHAR," + + " COL4 VARCHAR CONSTRAINT pk PRIMARY KEY(COL1, COL2))"); + stmt.execute("CREATE VIEW " + view01 + + " (VCOL1 CHAR(8) NOT NULL, COL5 VARCHAR CONSTRAINT pk PRIMARY KEY(VCOL1 DESC))" + + " AS SELECT * FROM " + tableName + " WHERE COL1 = 'col1'"); + stmt.execute("CREATE VIEW " + view02 + + " (VCOL2 CHAR(10) NOT NULL, COL6 VARCHAR CONSTRAINT pk PRIMARY KEY(VCOL2))" + + " AS SELECT * FROM " + view01 + " WHERE VCOL1 = 'vcol1'"); + + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0001', 'vcol2_01', 'col5_01', 'col6_01')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0002', 'vcol2_02', 'col5_02', 'col6_02')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0003', 'vcol2_03', 'col5_03', 'col6_03')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0004', 'vcol2', 'col3_04', 'col4_04', 'col5_04')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0005', 'vcol-2', 'col3_05', 'col4_05', 'col5_05')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0006', 'vcol-1', 'col3_06', 'col4_06', 'col5_06')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0007', 'vcol1', 'col3_07', 'col4_07', 'col5_07')"); + conn.commit(); + + ResultSet rs = stmt.executeQuery("SELECT COL1, COL2, VCOL1 FROM " + view01); + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0001", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0003", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0004", rs.getString(2)); + Assert.assertEquals("vcol2", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0005", rs.getString(2)); + Assert.assertEquals("vcol-2", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0006", rs.getString(2)); + Assert.assertEquals("vcol-1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0007", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT COL2, VCOL1, VCOL2, COL5, COL6 FROM " + view02); + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0001", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_01", rs.getString(3)); + Assert.assertEquals("col5_01", rs.getString(4)); + Assert.assertEquals("col6_01", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0002", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_02", rs.getString(3)); + Assert.assertEquals("col5_02", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0003", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_03", rs.getString(3)); + Assert.assertEquals("col5_03", rs.getString(4)); + Assert.assertEquals("col6_03", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0007", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertEquals("col5_07", rs.getString(4)); + Assert.assertNull(rs.getString(5)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT COL1, COL2, VCOL1 FROM " + view01 + " ORDER BY " + + "VCOL1 DESC, COL2 ASC"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0004", rs.getString(2)); + Assert.assertEquals("vcol2", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0001", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0003", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0007", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0005", rs.getString(2)); + Assert.assertEquals("vcol-2", rs.getString(3)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0006", rs.getString(2)); + Assert.assertEquals("vcol-1", rs.getString(3)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT COL2, VCOL1, VCOL2, COL5, COL6 FROM " + view02 + + " ORDER BY VCOL2 DESC, VCOL1 DESC"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0007", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertEquals("col5_07", rs.getString(4)); + Assert.assertNull(rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0003", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_03", rs.getString(3)); + Assert.assertEquals("col5_03", rs.getString(4)); + Assert.assertEquals("col6_03", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0002", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_02", rs.getString(3)); + Assert.assertEquals("col5_02", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0001", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_01", rs.getString(3)); + Assert.assertEquals("col5_01", rs.getString(4)); + Assert.assertEquals("col6_01", rs.getString(5)); + + Assert.assertFalse(rs.next()); + } + } + + @Test + public void testViewWithDoubleDescPkColumn() throws Exception { + final String tableName = generateUniqueName(); + final String view01 = "v01_" + tableName; + final String view02 = "v02_" + tableName; + + try (Connection conn = DriverManager.getConnection(getUrl())) { + final Statement stmt = conn.createStatement(); + + stmt.execute("CREATE TABLE " + tableName + + " (COL1 VARCHAR(10) NOT NULL, COL2 CHAR(5) NOT NULL, COL3 VARCHAR," + + " COL4 VARCHAR CONSTRAINT pk PRIMARY KEY(COL1, COL2))"); + stmt.execute("CREATE VIEW " + view01 + + " (VCOL1 DOUBLE NOT NULL, COL5 VARCHAR CONSTRAINT pk PRIMARY KEY(VCOL1 DESC))" + + " AS SELECT * FROM " + tableName + " WHERE COL1 = 'col1'"); + stmt.execute("CREATE VIEW " + view02 + + " (VCOL2 CHAR(10) NOT NULL, COL6 VARCHAR CONSTRAINT pk PRIMARY KEY(VCOL2))" + + " AS SELECT * FROM " + view01 + " WHERE VCOL1 = 234.75"); + + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0001', 'vcol2_01', 'col5_01', 'col6_01')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0002', 'vcol2_02', 'col5_02', 'col6_02')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0003', 'vcol2_03', 'col5_03', 'col6_03')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0004', 236.49, 'col3_04', 'col4_04', 'col5_04')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0005', 17.053, 'col3_05', 'col4_05', 'col5_05')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0006', 98.8452, 'col3_06', 'col4_06', 'col5_06')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0007', 234.75, 'col3_07', 'col4_07', 'col5_07')"); + conn.commit(); + + ResultSet rs = stmt.executeQuery("SELECT COL1, COL2, VCOL1 FROM " + view01); + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0001", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0003", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0004", rs.getString(2)); + Assert.assertEquals(236.49, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0005", rs.getString(2)); + Assert.assertEquals(17.053, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0006", rs.getString(2)); + Assert.assertEquals(98.8452, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0007", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT COL2, VCOL1, VCOL2, COL5, COL6 FROM " + view02); + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0001", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertEquals("vcol2_01", rs.getString(3)); + Assert.assertEquals("col5_01", rs.getString(4)); + Assert.assertEquals("col6_01", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0002", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertEquals("vcol2_02", rs.getString(3)); + Assert.assertEquals("col5_02", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0003", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertEquals("vcol2_03", rs.getString(3)); + Assert.assertEquals("col5_03", rs.getString(4)); + Assert.assertEquals("col6_03", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0007", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertNull(rs.getString(3)); + Assert.assertEquals("col5_07", rs.getString(4)); + Assert.assertNull(rs.getString(5)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT COL1, COL2, VCOL1 FROM " + view01 + " ORDER BY " + + "VCOL1 DESC, COL2 ASC"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0004", rs.getString(2)); + Assert.assertEquals(236.49, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0001", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0003", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0007", rs.getString(2)); + Assert.assertEquals(234.75, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0006", rs.getString(2)); + Assert.assertEquals(98.8452, rs.getDouble(3), 0); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0005", rs.getString(2)); + Assert.assertEquals(17.053, rs.getDouble(3), 0); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT COL2, VCOL1, VCOL2, COL5, COL6 FROM " + view02 + + " ORDER BY VCOL2 DESC, VCOL1 DESC"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0007", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertNull(rs.getString(3)); + Assert.assertEquals("col5_07", rs.getString(4)); + Assert.assertNull(rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0003", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertEquals("vcol2_03", rs.getString(3)); + Assert.assertEquals("col5_03", rs.getString(4)); + Assert.assertEquals("col6_03", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0002", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertEquals("vcol2_02", rs.getString(3)); + Assert.assertEquals("col5_02", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0001", rs.getString(1)); + Assert.assertEquals(234.75, rs.getDouble(2), 0); + Assert.assertEquals("vcol2_01", rs.getString(3)); + Assert.assertEquals("col5_01", rs.getString(4)); + Assert.assertEquals("col6_01", rs.getString(5)); + + Assert.assertFalse(rs.next()); + } + } + + @Test + public void testViewWithCharDescPkColumnAndViewIndex() throws Exception { + final String tableName = generateUniqueName(); + final String view01 = "v01_" + tableName; + final String view02 = "v02_" + tableName; + final String index_view01 = "idx_v01_" + tableName; + final String index_view02 = "idx_v02_" + tableName; + + try (Connection conn = DriverManager.getConnection(getUrl())) { + final Statement stmt = conn.createStatement(); + + stmt.execute("CREATE TABLE " + tableName + + " (COL1 CHAR(10) NOT NULL, COL2 CHAR(5) NOT NULL, COL3 VARCHAR," + + " COL4 VARCHAR CONSTRAINT pk PRIMARY KEY(COL1 ASC, COL2 DESC))"); + stmt.execute("CREATE VIEW " + view01 + + " (VCOL1 CHAR(8), COL5 VARCHAR) AS SELECT * FROM " + tableName + + " WHERE COL1 = 'col1'"); + stmt.execute("CREATE VIEW " + view02 + " (VCOL2 CHAR(10), COL6 VARCHAR)" + + " AS SELECT * FROM " + view01 + " WHERE VCOL1 = 'vcol1'"); + stmt.execute("CREATE INDEX " + index_view01 + " ON " + view01 + " (COL5) INCLUDE " + + "(COL1, COL2, COL3)"); + stmt.execute("CREATE INDEX " + index_view02 + " ON " + view02 + " (COL6) INCLUDE " + + "(COL1, COL2, COL3)"); + + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0001', 'vcol2_01', 'col5_01', 'col6_01')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0002', 'vcol2_02', 'col5_02', 'col6_02')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0003', 'vcol2_03', 'col5_03', 'col6_03')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0004', 'vcol2', 'col3_04', 'col4_04', 'col5_04')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0005', 'vcol-2', 'col3_05', 'col4_05', 'col5_05')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0006', 'vcol-1', 'col3_06', 'col4_06', 'col5_06')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0007', 'vcol1', 'col3_07', 'col4_07', 'col5_07')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0008', 'vcol2_08', 'col5_08', 'col6_02')"); + conn.commit(); + + ResultSet rs = stmt.executeQuery("SELECT COL2, VCOL1, VCOL2, COL5, COL6 FROM " + view02); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0008", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_08", rs.getString(3)); + Assert.assertEquals("col5_08", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0007", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertEquals("col5_07", rs.getString(4)); + Assert.assertNull(rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0003", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_03", rs.getString(3)); + Assert.assertEquals("col5_03", rs.getString(4)); + Assert.assertEquals("col6_03", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0002", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_02", rs.getString(3)); + Assert.assertEquals("col5_02", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0001", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_01", rs.getString(3)); + Assert.assertEquals("col5_01", rs.getString(4)); + Assert.assertEquals("col6_01", rs.getString(5)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT * FROM " + view02); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0008", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_08", rs.getString(6)); + Assert.assertEquals("vcol2_08", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0007", rs.getString(2)); + Assert.assertEquals("col3_07", rs.getString(3)); + Assert.assertEquals("col4_07", rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_07", rs.getString(6)); + Assert.assertNull(rs.getString(7)); + Assert.assertNull(rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0003", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_03", rs.getString(6)); + Assert.assertEquals("vcol2_03", rs.getString(7)); + Assert.assertEquals("col6_03", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_02", rs.getString(6)); + Assert.assertEquals("vcol2_02", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0001", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_01", rs.getString(6)); + Assert.assertEquals("vcol2_01", rs.getString(7)); + Assert.assertEquals("col6_01", rs.getString(8)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery( + "SELECT * FROM " + view02 + " WHERE COL6 = 'col6_02'"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0008", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_08", rs.getString(6)); + Assert.assertEquals("vcol2_08", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_02", rs.getString(6)); + Assert.assertEquals("vcol2_02", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery( + "SELECT COL1, COL2, VCOL1, COL6 FROM " + view02 + " WHERE COL6 = 'col6_02'"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0008", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + Assert.assertEquals("col6_02", rs.getString(4)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("col1", rs.getString(1)); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + Assert.assertEquals("col6_02", rs.getString(4)); + + Assert.assertFalse(rs.next()); + } + } + + @Test + public void testViewWithTimestampDescPkColumnAndViewIndex() throws Exception { + final String tableName = generateUniqueName(); + final String view01 = "v01_" + tableName; + final String view02 = "v02_" + tableName; + final String index_view01 = "idx_v01_" + tableName; + final String index_view02 = "idx_v02_" + tableName; + + try (Connection conn = DriverManager.getConnection(getUrl())) { + final Statement stmt = conn.createStatement(); + + stmt.execute("CREATE TABLE " + tableName + + " (COL1 TIMESTAMP NOT NULL, COL2 CHAR(5) NOT NULL, COL3 VARCHAR," + + " COL4 VARCHAR CONSTRAINT pk PRIMARY KEY(COL1 ASC, COL2 DESC))"); + stmt.execute("CREATE VIEW " + view01 + + " (VCOL1 CHAR(8), COL5 VARCHAR) AS SELECT * FROM " + tableName + + " WHERE COL1 = TO_TIMESTAMP('2023-01-20 00:10:00')"); + stmt.execute("CREATE VIEW " + view02 + " (VCOL2 CHAR(10), COL6 VARCHAR)" + + " AS SELECT * FROM " + view01 + " WHERE VCOL1 = 'vcol1'"); + stmt.execute("CREATE INDEX " + index_view01 + " ON " + view01 + " (COL5) INCLUDE " + + "(COL1, COL2, COL3)"); + stmt.execute("CREATE INDEX " + index_view02 + " ON " + view02 + " (COL6) INCLUDE " + + "(COL1, COL2, COL3)"); + + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0001', 'vcol2_01', 'col5_01', 'col6_01')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0002', 'vcol2_02', 'col5_02', 'col6_02')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0003', 'vcol2_03', 'col5_03', 'col6_03')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0004', 'vcol2', 'col3_04', 'col4_04', 'col5_04')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0005', 'vcol-2', 'col3_05', 'col4_05', 'col5_05')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0006', 'vcol-1', 'col3_06', 'col4_06', 'col5_06')"); + stmt.execute("UPSERT INTO " + view01 + " (col2, vcol1, col3, col4, col5) values " + + "('0007', 'vcol1', 'col3_07', 'col4_07', 'col5_07')"); + stmt.execute("UPSERT INTO " + view02 + + " (col2, vcol2, col5, col6) values ('0008', 'vcol2_08', 'col5_08', 'col6_02')"); + conn.commit(); + + ResultSet rs = stmt.executeQuery("SELECT COL2, VCOL1, VCOL2, COL5, COL6 FROM " + view02); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0008", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_08", rs.getString(3)); + Assert.assertEquals("col5_08", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0007", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertEquals("col5_07", rs.getString(4)); + Assert.assertNull(rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0003", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_03", rs.getString(3)); + Assert.assertEquals("col5_03", rs.getString(4)); + Assert.assertEquals("col6_03", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0002", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_02", rs.getString(3)); + Assert.assertEquals("col5_02", rs.getString(4)); + Assert.assertEquals("col6_02", rs.getString(5)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("0001", rs.getString(1)); + Assert.assertEquals("vcol1", rs.getString(2)); + Assert.assertEquals("vcol2_01", rs.getString(3)); + Assert.assertEquals("col5_01", rs.getString(4)); + Assert.assertEquals("col6_01", rs.getString(5)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery("SELECT * FROM " + view02); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0008", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_08", rs.getString(6)); + Assert.assertEquals("vcol2_08", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0007", rs.getString(2)); + Assert.assertEquals("col3_07", rs.getString(3)); + Assert.assertEquals("col4_07", rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_07", rs.getString(6)); + Assert.assertNull(rs.getString(7)); + Assert.assertNull(rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0003", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_03", rs.getString(6)); + Assert.assertEquals("vcol2_03", rs.getString(7)); + Assert.assertEquals("col6_03", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_02", rs.getString(6)); + Assert.assertEquals("vcol2_02", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0001", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_01", rs.getString(6)); + Assert.assertEquals("vcol2_01", rs.getString(7)); + Assert.assertEquals("col6_01", rs.getString(8)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery( + "SELECT * FROM " + view02 + " WHERE COL6 = 'col6_02'"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0008", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_08", rs.getString(6)); + Assert.assertEquals("vcol2_08", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertNull(rs.getString(3)); + Assert.assertNull(rs.getString(4)); + Assert.assertEquals("vcol1", rs.getString(5)); + Assert.assertEquals("col5_02", rs.getString(6)); + Assert.assertEquals("vcol2_02", rs.getString(7)); + Assert.assertEquals("col6_02", rs.getString(8)); + + Assert.assertFalse(rs.next()); + + rs = stmt.executeQuery( + "SELECT COL1, COL2, VCOL1, COL6 FROM " + view02 + " WHERE COL6 = 'col6_02'"); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0008", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + Assert.assertEquals("col6_02", rs.getString(4)); + + Assert.assertTrue(rs.next()); + + Assert.assertEquals("2023-01-20T00:10:00Z", rs.getTimestamp(1).toInstant().toString()); + Assert.assertEquals("0002", rs.getString(2)); + Assert.assertEquals("vcol1", rs.getString(3)); + Assert.assertEquals("col6_02", rs.getString(4)); + + Assert.assertFalse(rs.next()); + } + } } diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java index 1edb9b633d..8467c60f56 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java @@ -416,7 +416,9 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio // If the column is matched in a WHERE clause against a constant not equal to it's constant, then the entire // query would become degenerate. if (!resolveViewConstants && IndexUtil.getViewConstantValue(column, ptr)) { - return LiteralExpression.newConstant(column.getDataType().toObject(ptr), column.getDataType()); + return LiteralExpression.newConstant( + column.getDataType().toObject(ptr, column.getSortOrder()), + column.getDataType(), column.getSortOrder()); } if (tableRef.equals(context.getCurrentTable()) && !SchemaUtil.isPKColumn(column)) { // project only kv columns addColumn(column); diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java index b20dc1e48c..1985d0f822 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/IndexStatementRewriter.java @@ -101,7 +101,7 @@ public class IndexStatementRewriter extends ParseNodeRewriter { // Ignore last byte, as it's only there so we can have a way to differentiate null // from the absence of a value. ptr.set(viewConstant, 0, viewConstant.length-1); - Object literal = dataCol.getDataType().toObject(ptr); + Object literal = dataCol.getDataType().toObject(ptr, dataCol.getSortOrder()); return new LiteralParseNode(literal, dataCol.getDataType()); } TableName tName = getReplacedTableName(dataTableRef); diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java index d56b44b3c8..86f2f80a52 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ProjectionCompiler.java @@ -178,7 +178,10 @@ public class ProjectionCompiler { expression = coerceIfNecessary(i-posOffset+projectedOffset, targetColumns, expression); ImmutableBytesWritable ptr = context.getTempPtr(); if (IndexUtil.getViewConstantValue(column, ptr)) { - expression = LiteralExpression.newConstant(column.getDataType().toObject(ptr), expression.getDataType()); + expression = LiteralExpression.newConstant( + column.getDataType().toObject(ptr, column.getSortOrder()), + expression.getDataType(), + column.getSortOrder()); } projectedExpressions.add(expression); boolean isCaseSensitive = !SchemaUtil.normalizeIdentifier(colName).equals(colName);