[
https://issues.apache.org/jira/browse/TAJO-1493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14769055#comment-14769055
]
ASF GitHub Bot commented on TAJO-1493:
--------------------------------------
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?
> Make partition pruning based on catalog informations
> ----------------------------------------------------
>
> Key: TAJO-1493
> URL: https://issues.apache.org/jira/browse/TAJO-1493
> Project: Tajo
> Issue Type: Sub-task
> Components: Catalog, Planner/Optimizer
> Reporter: Jaehwa Jung
> Assignee: Jaehwa Jung
> Fix For: 0.11.0, 0.12.0
>
> Attachments: TAJO-1493.patch, TAJO-1493_2.patch, TAJO-1493_3.patch,
> TAJO-1493_4.patch
>
>
> Currently, PartitionedTableRewriter take a look into partition directories
> for rewriting filter conditions. It get all sub directories of table path
> because catalog doesn’t provide partition directories. But if there are lots
> of sub directories on HDFS, such as, more than 10,000 directories, it might
> be cause overload to NameNode. Thus, CatalogStore need to provide partition
> directories for specified filter conditions. I designed new method to
> CatalogStore as follows:
> * method name: getPartitionsWithConditionFilters
> * first parameter: database name
> * second parameter: table name
> * third parameter: where clause (included target column name and partition
> value)
> * return values:
> List<org.apache.tajo.catalog.proto.CatalogProtos.TablePartitionProto>
> * description: It scan right partition directories on CatalogStore with where
> caluse.
> For examples, users set parameters as following:
> ** first parameter: default
> ** second parameter: table1
> ** third parameter: COLUMN_NAME = 'col1' AND PARTITION_VALUE = '3
> In the previous cases, this method will create select clause as follows.
> {code:xml}
> SELECT DISTINCT A.PATH
> FROM PARTITIONS A, (
> SELECT B.PARTITION_ID
> FROM PARTITION_KEYS B
> WHERE B.PARTITION_ID > 0
> AND (
> COLUMN_NAME = 'col1' AND PARTITION_VALUE = '3'
> )
> ) B
> WHERE A.PARTITION_ID > 0
> AND A.TID = ${table_id}
> AND A.PARTITION_ID = B.PARTITION_ID
> {code}
> At the first time, I considered to use EvalNode instead of where clause. But
> I can’t use it because of recursive related problems between tajo-catalog
> module and tajo-plan module. So, I’ll implement utility class to convert
> EvalNode to SQL.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
