Github user hyunsik commented on a diff in the pull request:
https://github.com/apache/tajo/pull/624#discussion_r39639478
--- Diff:
tajo-core-tests/src/test/java/org/apache/tajo/engine/query/TestTablePartitions.java
---
@@ -1325,4 +1332,421 @@ public final void testDuplicatedPartitions() throws
Exception {
executeString("DROP TABLE " + tableName + " PURGE");
}
}
+
+ @Test
+ public final void testPatternMatchingPredicatesAndStringFunctions()
throws Exception {
+ ResultSet res = null;
+ String tableName =
CatalogUtil.normalizeIdentifier("testPatternMatchingPredicatesAndStringFunctions");
+ String expectedResult;
+
+ if (nodeType == NodeType.INSERT) {
+ executeString("create table " + tableName
+ + " (col1 int4, col2 int4) partition by column(l_shipdate text,
l_returnflag text) ").close();
+
+ assertTrue(catalog.existsTable(DEFAULT_DATABASE_NAME, tableName));
+ assertEquals(2, catalog.getTableDesc(DEFAULT_DATABASE_NAME,
tableName).getSchema().size());
+ assertEquals(4, catalog.getTableDesc(DEFAULT_DATABASE_NAME,
tableName).getLogicalSchema().size());
+
+ executeString(
+ "insert overwrite into " + tableName + " select l_orderkey,
l_partkey, l_shipdate, l_returnflag from lineitem");
+ } else {
+ executeString(
+ "create table " + tableName + "(col1 int4, col2 int4) partition by
column(l_shipdate text, l_returnflag text) "
+ + " as select l_orderkey, l_partkey, l_shipdate, l_returnflag
from lineitem");
+ }
+
+ assertTrue(client.existTable(tableName));
+
+ // Like
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE l_shipdate LIKE '1996%' and l_returnflag = 'N' order by
l_shipdate");
+
+ expectedResult = "col1,col2,l_shipdate,l_returnflag\n" +
+ "-------------------------------\n" +
+ "1,1,1996-03-13,N\n" +
+ "1,1,1996-04-12,N\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // Not like
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE l_shipdate NOT LIKE '1996%' and l_returnflag IN ('R')
order by l_shipdate");
+
+ expectedResult = "col1,col2,l_shipdate,l_returnflag\n" +
+ "-------------------------------\n" +
+ "3,3,1993-11-09,R\n" +
+ "3,2,1994-02-02,R\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // In
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE l_shipdate IN ('1993-11-09', '1994-02-02', '1997-01-28')
AND l_returnflag = 'R' order by l_shipdate");
+
+ expectedResult = "col1,col2,l_shipdate,l_returnflag\n" +
+ "-------------------------------\n" +
+ "3,3,1993-11-09,R\n" +
+ "3,2,1994-02-02,R\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // Similar to
+ res = executeString("SELECT * FROM " + tableName + " WHERE l_shipdate
similar to '1993%' order by l_shipdate");
+
+ expectedResult = "col1,col2,l_shipdate,l_returnflag\n" +
+ "-------------------------------\n" +
+ "3,3,1993-11-09,R\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // Regular expression
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE l_shipdate regexp
'[1-2][0-9][0-9][3-9]-[0-1][0-9]-[0-3][0-9]' "
+ + " AND l_returnflag <> 'N' ORDER BY l_shipdate");
+
+ expectedResult = "col1,col2,l_shipdate,l_returnflag\n" +
+ "-------------------------------\n" +
+ "3,3,1993-11-09,R\n" +
+ "3,2,1994-02-02,R\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // Concatenate
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE l_shipdate = ( '1996' || '-' || '03' || '-' || '13' )
order by l_shipdate");
+
+ expectedResult = "col1,col2,l_shipdate,l_returnflag\n" +
+ "-------------------------------\n" +
+ "1,1,1996-03-13,N\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ executeString("DROP TABLE " + tableName + " PURGE").close();
+ res.close();
+ }
+
+ @Test
+ public final void testDatePartitionColumn() throws Exception {
+ ResultSet res = null;
+ String tableName =
CatalogUtil.normalizeIdentifier("testDatePartitionColumn");
+ String expectedResult;
+
+ if (nodeType == NodeType.INSERT) {
+ executeString("create table " + tableName + " (col1 int4, col2 int4)
partition by column(key date) ").close();
+
+ assertTrue(catalog.existsTable(DEFAULT_DATABASE_NAME, tableName));
+ assertEquals(2, catalog.getTableDesc(DEFAULT_DATABASE_NAME,
tableName).getSchema().size());
+ assertEquals(3, catalog.getTableDesc(DEFAULT_DATABASE_NAME,
tableName).getLogicalSchema().size());
+
+ executeString(
+ "insert overwrite into " + tableName + " select l_orderkey,
l_partkey, l_shipdate from lineitem");
+ } else {
+ executeString(
+ "create table " + tableName + "(col1 int4, col2 int4) partition by
column(key date) "
+ + " as select l_orderkey, l_partkey, l_shipdate::date from
lineitem");
+ }
+
+ assertTrue(client.existTable(tableName));
+
+ // LessThanOrEquals
+ res = executeString("SELECT * FROM " + tableName + " WHERE key <= date
'1995-09-01' order by col1, col2, key");
+
+ expectedResult = "col1,col2,key\n" +
+ "-------------------------------\n" +
+ "3,2,1994-02-02\n" +
+ "3,3,1993-11-09\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // LessThan and GreaterThan
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE key > to_date('1993-01-01', 'YYYY-MM-DD') " +
+ " and key < to_date('1996-01-01', 'YYYY-MM-DD') order by col1, col2,
key desc");
+
+ expectedResult = "col1,col2,key\n" +
+ "-------------------------------\n" +
+ "3,2,1994-02-02\n" +
+ "3,3,1993-11-09\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // Between
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE key between date '1993-01-01' and date '1997-01-01' order
by col1, col2, key desc");
+
+ expectedResult = "col1,col2,key\n" +
+ "-------------------------------\n" +
+ "1,1,1996-04-12\n" +
+ "1,1,1996-03-13\n" +
+ "3,2,1994-02-02\n" +
+ "3,3,1993-11-09\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // Cast
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE key > '1993-01-01'::date " +
+ " and key < '1997-01-01'::timestamp order by col1, col2, key ");
+
+ expectedResult = "col1,col2,key\n" +
+ "-------------------------------\n" +
+ "1,1,1996-03-13\n" +
+ "1,1,1996-04-12\n" +
+ "3,2,1994-02-02\n" +
+ "3,3,1993-11-09\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // Interval
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE key > '1993-01-01'::date " +
+ " and key < date '1994-01-01' + interval '1 year' order by col1,
col2, key ");
+
+ expectedResult = "col1,col2,key\n" +
+ "-------------------------------\n" +
+ "3,2,1994-02-02\n" +
+ "3,3,1993-11-09\n";
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // DateTime Function #1
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE key > '1993-01-01'::date " +
+ " and key < add_months(date '1994-01-01', 12) order by col1, col2,
key ");
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ // DateTime Function #2
+ res = executeString("SELECT * FROM " + tableName
+ + " WHERE key > '1993-01-01'::date " +
+ " and key < add_months('1994-01-01'::timestamp, 12) order by col1,
col2, key ");
+
+ assertEquals(expectedResult, resultSetToString(res));
+ res.close();
+
+ executeString("DROP TABLE " + tableName + " PURGE").close();
+ res.close();
+ }
+
+ @Test
+ public final void testTimeStampPartitionColumn() throws Exception {
--- End diff --
Could you rename TimeStamp to Timestamp?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
