phoenix5-spark/src/it/java/org/apache/phoenix/spark/OrderByIT.java [300:389]: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - .option(PhoenixDataSource.ZOOKEEPER_URL, getUrl()).load(); phoenixDataSet.createOrReplaceTempView(tableName1); String dml = "UPSERT INTO " + tableName1 + " VALUES(?,?,?,?,?)"; PreparedStatement stmt = conn.prepareStatement(dml); stmt.setInt(1, 40); stmt.setString(2, "a"); stmt.setString(3, "b"); stmt.setString(4, "Y"); stmt.setString(5, "M"); stmt.execute(); stmt.setInt(1, 40); stmt.setString(2, "c"); stmt.setString(3, "d"); stmt.setNull(4, Types.VARCHAR); stmt.setString(5, "M"); stmt.execute(); stmt.setInt(1, 40); stmt.setString(2, "e"); stmt.setString(3, "f"); stmt.setString(4, "N"); stmt.setString(5, "M"); stmt.execute(); stmt.setInt(1, 41); stmt.setString(2, "f"); stmt.setString(3, "g"); stmt.setString(4, "N"); stmt.setString(5, "C"); stmt.execute(); conn.commit(); String query = "select count(*) from " + tableName1 + " where ENTITY_INSTANCE_ID = 40 and " + "(CANCELLATION_FLAG <> 'Y' OR CANCELLATION_FLAG IS NULL ) and CASE_MATCH_TYPE='M'"; Dataset dataset = sqlContext.sql(query); List rows = dataset.collectAsList(); ResultSet rs = new SparkResultSet(rows, dataset.columns()); assertTrue(rs.next()); assertEquals(2, rs.getLong(1)); assertFalse(rs.next()); query = "select count(*) from " + tableName1 + " where ENTITY_INSTANCE_ID = 40 and CASE_MATCH_TYPE='M' " + " OR CANCELLATION_FLAG <> 'Y'"; dataset = sqlContext.sql(query); rows = dataset.collectAsList(); rs = new SparkResultSet(rows, dataset.columns()); assertTrue(rs.next()); assertEquals(4, rs.getLong(1)); assertFalse(rs.next()); } } @Test public void testOrderByWithExpression() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); try { String tableName = generateUniqueName(); String ddl = "CREATE TABLE " + tableName + " (a_string varchar not null, col1 integer, col2 integer, col3 timestamp, col4 varchar" + " CONSTRAINT pk PRIMARY KEY (a_string))\n"; createTestTable(getUrl(), ddl); Date date = new Date(System.currentTimeMillis()); String dml = "UPSERT INTO " + tableName + " VALUES(?, ?, ?, ?, ?)"; PreparedStatement stmt = conn.prepareStatement(dml); stmt.setString(1, "a"); stmt.setInt(2, 40); stmt.setInt(3, 20); stmt.setDate(4, new Date(date.getTime())); stmt.setString(5, "xxyy"); stmt.execute(); stmt.setString(1, "b"); stmt.setInt(2, 50); stmt.setInt(3, 30); stmt.setDate(4, new Date(date.getTime()-500)); stmt.setString(5, "yyzz"); stmt.execute(); stmt.setString(1, "c"); stmt.setInt(2, 60); stmt.setInt(3, 20); stmt.setDate(4, new Date(date.getTime()-300)); stmt.setString(5, "ddee"); stmt.execute(); conn.commit(); SQLContext sqlContext = SparkUtil.getSparkSession().sqlContext(); Dataset phoenixDataSet = SparkUtil.getSparkSession().read().format("phoenix") - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - phoenix5-spark3-it/src/it/java/org/apache/phoenix/spark/OrderByIT.java [301:390]: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - .option(PhoenixDataSource.ZOOKEEPER_URL, getUrl()).load(); phoenixDataSet.createOrReplaceTempView(tableName1); String dml = "UPSERT INTO " + tableName1 + " VALUES(?,?,?,?,?)"; PreparedStatement stmt = conn.prepareStatement(dml); stmt.setInt(1, 40); stmt.setString(2, "a"); stmt.setString(3, "b"); stmt.setString(4, "Y"); stmt.setString(5, "M"); stmt.execute(); stmt.setInt(1, 40); stmt.setString(2, "c"); stmt.setString(3, "d"); stmt.setNull(4, Types.VARCHAR); stmt.setString(5, "M"); stmt.execute(); stmt.setInt(1, 40); stmt.setString(2, "e"); stmt.setString(3, "f"); stmt.setString(4, "N"); stmt.setString(5, "M"); stmt.execute(); stmt.setInt(1, 41); stmt.setString(2, "f"); stmt.setString(3, "g"); stmt.setString(4, "N"); stmt.setString(5, "C"); stmt.execute(); conn.commit(); String query = "select count(*) from " + tableName1 + " where ENTITY_INSTANCE_ID = 40 and " + "(CANCELLATION_FLAG <> 'Y' OR CANCELLATION_FLAG IS NULL ) and CASE_MATCH_TYPE='M'"; Dataset dataset = sqlContext.sql(query); List rows = dataset.collectAsList(); ResultSet rs = new SparkResultSet(rows, dataset.columns()); assertTrue(rs.next()); assertEquals(2, rs.getLong(1)); assertFalse(rs.next()); query = "select count(*) from " + tableName1 + " where ENTITY_INSTANCE_ID = 40 and CASE_MATCH_TYPE='M' " + " OR CANCELLATION_FLAG <> 'Y'"; dataset = sqlContext.sql(query); rows = dataset.collectAsList(); rs = new SparkResultSet(rows, dataset.columns()); assertTrue(rs.next()); assertEquals(4, rs.getLong(1)); assertFalse(rs.next()); } } @Test public void testOrderByWithExpression() throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); try { String tableName = generateUniqueName(); String ddl = "CREATE TABLE " + tableName + " (a_string varchar not null, col1 integer, col2 integer, col3 timestamp, col4 varchar" + " CONSTRAINT pk PRIMARY KEY (a_string))\n"; createTestTable(getUrl(), ddl); Date date = new Date(System.currentTimeMillis()); String dml = "UPSERT INTO " + tableName + " VALUES(?, ?, ?, ?, ?)"; PreparedStatement stmt = conn.prepareStatement(dml); stmt.setString(1, "a"); stmt.setInt(2, 40); stmt.setInt(3, 20); stmt.setDate(4, new Date(date.getTime())); stmt.setString(5, "xxyy"); stmt.execute(); stmt.setString(1, "b"); stmt.setInt(2, 50); stmt.setInt(3, 30); stmt.setDate(4, new Date(date.getTime()-500)); stmt.setString(5, "yyzz"); stmt.execute(); stmt.setString(1, "c"); stmt.setInt(2, 60); stmt.setInt(3, 20); stmt.setDate(4, new Date(date.getTime()-300)); stmt.setString(5, "ddee"); stmt.execute(); conn.commit(); SQLContext sqlContext = SparkUtil.getSparkSession().sqlContext(); Dataset phoenixDataSet = SparkUtil.getSparkSession().read().format("phoenix") - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -