tajo-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hyun...@apache.org
Subject [07/51] [partial] TAJO-752: Escalate sub modules in tajo-core into the top-level modules. (hyunsik)
Date Fri, 18 Apr 2014 10:31:19 GMT
http://git-wip-us.apache.org/repos/asf/tajo/blob/026368be/tajo-core/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java b/tajo-core/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java
new file mode 100644
index 0000000..fa13011
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/function/TestStringOperatorsAndFunctions.java
@@ -0,0 +1,610 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.function;
+
+
+import org.apache.commons.lang.StringEscapeUtils;
+import org.apache.tajo.catalog.Schema;
+import org.apache.tajo.engine.eval.ExprTestBase;
+import org.junit.Test;
+
+import java.io.IOException;
+
+import static org.apache.tajo.common.TajoDataTypes.Type.*;
+
+public class TestStringOperatorsAndFunctions extends ExprTestBase {
+
+  @Test
+  public void testConcatenateOnLiteral() throws IOException {
+    testSimpleEval("select ('abc' || 'def') col1 ", new String[]{"abcdef"});
+    testSimpleEval("select 'abc' || 'def' as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select 1 || 'def' as col1 ", new String[]{"1def"});
+    testSimpleEval("select 'abc' || 2 as col1 ", new String[]{"abc2"});
+  }
+
+  @Test
+  public void testConcatenateOnExpressions() throws IOException {
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", INT4);
+    schema.addColumn("col3", FLOAT8);
+
+    testSimpleEval("select (1+3) || 2 as col1 ", new String[]{"42"});
+
+    testEval(schema, "table1", "abc,2,3.14", "select col1 || col2 || col3 from table1", new String[]{"abc23.14"});
+    testEval(schema, "table1", "abc,2,3.14", "select col1 || '---' || col3 from table1", new String[]{"abc---3.14"});
+  }
+
+  @Test
+  public void testFunctionCallIngoreCases() throws IOException {
+    testSimpleEval("select ltrim(' trim') ", new String[]{"trim"});
+    testSimpleEval("select LTRIM(' trim') ", new String[]{"trim"});
+    testSimpleEval("select lTRim(' trim') ", new String[]{"trim"});
+    testSimpleEval("select ltrIM(' trim') ", new String[]{"trim"});
+  }
+
+  @Test
+  public void testLTrim() throws IOException {
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+
+    testSimpleEval("select ltrim(' trim') ", new String[]{"trim"});
+    testSimpleEval("select ltrim('xxtrim', 'xx') ", new String[]{"trim"});
+
+    testSimpleEval("select trim(leading 'xx' from 'xxtrim') ", new String[]{"trim"});
+    testSimpleEval("select trim(leading from '  trim') ", new String[]{"trim"});
+    testSimpleEval("select trim('  trim') ", new String[]{"trim"});
+
+    testEval(schema, "table1", "  trim,abc", "select ltrim(col1) from table1", new String[]{"trim"});
+    testEval(schema, "table1", "xxtrim,abc", "select ltrim(col1, 'xx') from table1", new String[]{"trim"});
+    testEval(schema, "table1", "xxtrim,abc", "select trim(leading 'xx' from col1) from table1", new String[]{"trim"});
+
+    testEval(schema, "table1", "  trim,  abc", "select ltrim(col1) || ltrim(col2) from table1",
+        new String[]{"trimabc"});
+  }
+
+  @Test
+  public void testRTrim() throws IOException {
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+
+    testSimpleEval("select rtrim('trim ') ", new String[]{"trim"});
+    testSimpleEval("select rtrim('trimxx', 'xx') ", new String[]{"trim"});
+
+    testSimpleEval("select trim(trailing 'xx' from 'trimxx') ", new String[]{"trim"});
+    testSimpleEval("select trim(trailing from 'trim  ') ", new String[]{"trim"});
+    testSimpleEval("select trim('trim  ') ", new String[]{"trim"});
+
+    testEval(schema, "table1", "trim  ,abc", "select rtrim(col1) from table1", new String[]{"trim"});
+    testEval(schema, "table1", "trimxx,abc", "select rtrim(col1, 'xx') from table1", new String[]{"trim"});
+    testEval(schema, "table1", "trimxx,abc", "select trim(trailing 'xx' from col1) from table1", new String[]{"trim"});
+
+    testEval(schema, "table1", "trim  ,abc  ", "select rtrim(col1) || rtrim(col2) from table1",
+        new String[]{"trimabc"});
+  }
+
+  @Test
+  public void testTrim() throws IOException {
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+
+    testSimpleEval("select trim(' trim ') ", new String[]{"trim"});
+    testSimpleEval("select btrim('xxtrimxx', 'xx') ", new String[]{"trim"});
+
+    testSimpleEval("select trim(both 'xx' from 'xxtrimxx') ", new String[]{"trim"});
+    testSimpleEval("select trim(both from '  trim  ') ", new String[]{"trim"});
+    testSimpleEval("select trim('  trim  ') ", new String[]{"trim"});
+
+    testEval(schema, "table1", "  trim  ,abc", "select trim(col1) from table1", new String[]{"trim"});
+    testEval(schema, "table1", "xxtrimxx,abc", "select trim(col1, 'xx') from table1", new String[]{"trim"});
+    testEval(schema, "table1", "xxtrimxx,abc", "select trim(both 'xx' from col1) from table1", new String[]{"trim"});
+
+    testEval(schema, "table1", "  trim  ,xxabcxx", "select trim(col1) || trim(col2,'xx') from table1",
+        new String[]{"trimabc"});
+  }
+
+  @Test
+  public void testRegexReplace() throws IOException {
+    testSimpleEval("select regexp_replace('abcdef','bc','--') as col1 ", new String[]{"a--def"});
+
+    // null test
+    testSimpleEval("select regexp_replace(null, 'bc', '--') as col1 ", new String[]{""});
+    testSimpleEval("select regexp_replace('abcdef', null, '--') as col1 ", new String[]{""});
+    testSimpleEval("select regexp_replace('abcdef','bc', null) as col1 ", new String[]{""});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+
+    // find matches and replace from column values
+    testEval(schema, "table1", "------,(^--|--$),ab", "select regexp_replace(col1, col2, col3) as str from table1",
+        new String[]{"ab--ab"});
+
+    // null test from a table
+    testEval(schema, "table1", ",(^--|--$),ab", "select regexp_replace(col1, col2, col3) as str from table1",
+        new String[]{""});
+    testEval(schema, "table1", "------,(^--|--$),", "select regexp_replace(col1, col2, col3) as str from table1",
+        new String[]{""});
+  }
+
+  @Test
+  public void testLeft() throws IOException {
+    testSimpleEval("select left('abcdef',1) as col1 ", new String[]{"a"});
+    testSimpleEval("select left('abcdef',2) as col1 ", new String[]{"ab"});
+    testSimpleEval("select left('abcdef',3) as col1 ", new String[]{"abc"});
+    testSimpleEval("select left('abcdef',4) as col1 ", new String[]{"abcd"});
+    testSimpleEval("select left('abcdef',5) as col1 ", new String[]{"abcde"});
+    testSimpleEval("select left('abcdef',6) as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select left('abcdef',7) as col1 ", new String[]{"abcdef"});
+
+    testSimpleEval("select left('abcdef',-1) as col1 ", new String[]{"abcde"});
+    testSimpleEval("select left('abcdef',-2) as col1 ", new String[]{"abcd"});
+    testSimpleEval("select left('abcdef',-3) as col1 ", new String[]{"abc"});
+    testSimpleEval("select left('abcdef',-4) as col1 ", new String[]{"ab"});
+    testSimpleEval("select left('abcdef',-5) as col1 ", new String[]{"a"});
+    testSimpleEval("select left('abcdef',-6) as col1 ", new String[]{""});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", INT4);
+    schema.addColumn("col3", TEXT);
+
+    // for null tests
+    testEval(schema, "table1", ",1,ghi", "select left(col1,1) is null from table1", new String[]{"t"});
+    testEval(schema, "table1", "abc,,ghi", "select left(col1,col2) is null from table1", new String[]{"t"});
+
+    testEval(schema, "table1", "abc,1,ghi", "select left(col1,1) || left(col3,3) from table1", new String[]{"aghi"});
+  }
+
+  @Test
+  public void testRight() throws IOException {
+    testSimpleEval("select right('abcdef',1) as col1 ", new String[]{"f"});
+    testSimpleEval("select right('abcdef',2) as col1 ", new String[]{"ef"});
+    testSimpleEval("select right('abcdef',3) as col1 ", new String[]{"def"});
+    testSimpleEval("select right('abcdef',4) as col1 ", new String[]{"cdef"});
+    testSimpleEval("select right('abcdef',5) as col1 ", new String[]{"bcdef"});
+    testSimpleEval("select right('abcdef',6) as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select right('abcdef',7) as col1 ", new String[]{"abcdef"});
+
+    testSimpleEval("select right('abcdef',-1) as col1 ", new String[]{"bcdef"});
+    testSimpleEval("select right('abcdef',-2) as col1 ", new String[]{"cdef"});
+    testSimpleEval("select right('abcdef',-3) as col1 ", new String[]{"def"});
+    testSimpleEval("select right('abcdef',-4) as col1 ", new String[]{"ef"});
+    testSimpleEval("select right('abcdef',-5) as col1 ", new String[]{"f"});
+    testSimpleEval("select right('abcdef',-6) as col1 ", new String[]{""});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", INT4);
+    schema.addColumn("col3", TEXT);
+
+    // for null tests
+    testEval(schema, "table1", ",1,ghi", "select right(col1,1) is null from table1", new String[]{"t"});
+    testEval(schema, "table1", "abc,,ghi", "select right(col1,col2) is null from table1", new String[]{"t"});
+
+    testEval(schema, "table1", "abc,1,ghi", "select right(col1,1) || right(col3,3) from table1", new String[]{"cghi"});
+  }
+
+  @Test
+  public void testReverse() throws IOException {
+    testSimpleEval("select reverse('abcdef') as col1 ", new String[]{"fedcba"});
+    testSimpleEval("select reverse('가') as col1 ", new String[]{"가"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "abc,efg,3.14", "select reverse(col1) || reverse(col2) from table1",
+        new String[]{"cbagfe"});
+  }
+
+  @Test
+  public void testRepeat() throws IOException {
+    testSimpleEval("select repeat('ab',4) as col1 ", new String[]{"abababab"});
+    testSimpleEval("select repeat('가',3) as col1 ", new String[]{"가가가"});
+    testSimpleEval("select repeat('a',2) as col1 ", new String[]{"aa"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "abc,efg,3.14", "select repeat(col1,2) from table1", new String[]{"abcabc"});
+  }
+
+
+  @Test
+  public void testUpper() throws IOException {
+    testSimpleEval("select upper('abcdef') as col1 ", new String[]{"ABCDEF"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "abc,efg,3.14", "select upper(col1), upper(col2) from table1",
+        new String[]{"ABC", "EFG"});
+    testEval(schema, "table1", "abc,efg,3.14", "select upper(col1) || upper(col2) from table1", new String[]{"ABCEFG"});
+  }
+
+  @Test
+  public void testLower() throws IOException {
+    testSimpleEval("select lower('ABCdEF') as col1 ", new String[]{"abcdef"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "ABC,DEF,3.14", "select lower(col1), lower(col2) from table1",
+        new String[]{"abc", "def"});
+    testEval(schema, "table1", "ABC,DEF,3.14", "select lower(col1) || lower(col2) from table1", new String[]{"abcdef"});
+  }
+
+  @Test
+  public void testCharLength() throws IOException {
+    testSimpleEval("select char_length('123456') as col1 ", new String[]{"6"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "ABC,DEF,3.14", "select character_length(lower(col1) || lower(col2)) from table1",
+        new String[]{"6"});
+  }
+
+  @Test
+  public void testLength() throws IOException {
+    testSimpleEval("select length('123456') as col1 ", new String[]{"6"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "ABC,DEF,3.14", "select length(lower(col1) || lower(col2)) from table1",
+        new String[]{"6"});
+  }
+
+  @Test
+  public void testMd5() throws IOException {
+    testSimpleEval("select md5('1') as col1 ", new String[]{"c4ca4238a0b923820dcc509a6f75849b"});
+    testSimpleEval("select md5('tajo') as col1 ", new String[]{"742721b3a79f71a9491681b8e8a7ce85"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "abc,efg,3.14", "select md5(col1) from table1",
+        new String[]{"900150983cd24fb0d6963f7d28e17f72"});
+  }
+
+  @Test
+  public void testDigest() throws IOException {
+    testSimpleEval("select digest('tajo', 'md2') as col1 ", new String[]{"bf523bce8241982f6bea9af0f7fd37ff"});
+    testSimpleEval("select digest('tajo', 'md5') as col1 ", new String[]{"742721b3a79f71a9491681b8e8a7ce85"});
+    testSimpleEval("select digest('tajo', 'sha1') as col1 ", new String[]{"02b0e20540b89f0b735092bbac8093eb2e3804cf"});
+    testSimpleEval("select digest('tajo', 'sha256') as col1 ", 
+      new String[]{"6440083be076869a9f9d0271a4bf298d98c8aa3ecb49df841895fbcddbb04a70"});
+    testSimpleEval("select digest('tajo', 'sha384') as col1 ", 
+      new String[]{"59ff99b0e274eb3d8e10f221b6b949bfc1244d2a1226c5c720062fb03d82272be633e4a0f2babccffbfdff7cc1cb06fb"});
+    testSimpleEval("select digest('tajo', 'sha512') as col1 ", 
+      new String[]{"ee8ba254d331ddfb1bca9aaf0c4b8c58aea5331928cbd20168c87828afb853b0c096af71ec69a23b669217a1dddd2934edaac33b1296fe526b22abd28a15c4b3"});
+    testSimpleEval("select digest('tajo', 'not') as col1 ", new String[]{""});
+  }
+
+  @Test
+  public void testHex() throws IOException {
+    testSimpleEval("select to_hex(1) as col1 ", new String[]{"1"});
+    testSimpleEval("select to_hex(10) as col1 ", new String[]{"a"});
+    testSimpleEval("select to_hex(1234) as col1 ", new String[]{"4d2"});
+    testSimpleEval("select to_hex(1023456788888888) as col1 ", new String[]{"3a2d41a583d38"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", ",abcdef,3.14", "select to_hex(10) from table1",
+        new String[]{"a"});
+  }
+
+  @Test
+  public void testBin() throws IOException {
+    testSimpleEval("select to_bin(1) as col1 ", new String[]{"1"});
+    testSimpleEval("select to_bin(10) as col1 ", new String[]{"1010"});
+    testSimpleEval("select to_bin(1234) as col1 ", new String[]{"10011010010"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", ",abcdef,3.14", "select to_bin(20) from table1",
+        new String[]{"10100"});
+  }
+
+  @Test
+  public void testOctetLength() throws IOException {
+    testSimpleEval("select octet_length('123456') as col1 ", new String[]{"6"});
+    testSimpleEval("select octet_length('1') as col1 ", new String[]{"1"});
+    testSimpleEval("select octet_length('가') as col1 ", new String[]{"3"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "ABC,DEF,3.14", "select octet_length(lower(col1) || lower(col2)) from table1",
+        new String[]{"6"});
+  }
+
+  @Test
+  public void testSplitPart() throws IOException {
+    testSimpleEval("select split_part('1386577650.123', '.', 1) as col1 ", new String[]{"1386577650"});
+    testSimpleEval("select split_part('1386577650.123', '.', 2) as col1 ", new String[]{"123"});
+    // If part is larger than the number of string portions, it will returns NULL.
+    testSimpleEval("select split_part('1386577650.123', '.', 3) is null", new String[]{"t"});
+
+    // null handling tests
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "t1", ",.,1", "select split_part(col1, col2, col3::int) is null from t1", new String[]{"t"});
+    testEval(schema, "t1", "1386577650.123,,1", "select split_part(col1, col2, col3::int) from t1",
+        new String[]{"1386577650.123"});
+    testEval(schema, "t1", "1386577650.123,.,", "select split_part(col1, col2, col3::int) is null from t1",
+        new String[]{"t"});
+  }
+
+  @Test
+  public void testSubstr() throws IOException {
+    testSimpleEval("select substr('abcdef', 3, 2) as col1 ", new String[]{"cd"});
+    testSimpleEval("select substr('abcdef', 3) as col1 ", new String[]{"cdef"});
+    testSimpleEval("select substr('abcdef', 1, 1) as col1 ", new String[]{"a"});
+    testSimpleEval("select substr('abcdef', 0, 1) as col1 ", new String[]{""});
+    testSimpleEval("select substr('abcdef', 0, 2) as col1 ", new String[]{"a"});
+    testSimpleEval("select substr('abcdef', 0) as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select substr('abcdef', 1, 100) as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select substr('abcdef', 0, 100) as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select substr('일이삼사오', 2, 2) as col1 ", new String[]{"이삼"});
+    testSimpleEval("select substr('일이삼사오', 3) as col1 ", new String[]{"삼사오"});
+
+    testSimpleEval("select substr('abcdef', -1) as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select substr('abcdef', -1, 100) as col1 ", new String[]{"abcdef"});
+    testSimpleEval("select substr('abcdef', -1, 3) as col1 ", new String[]{"a"});
+    testSimpleEval("select substr('abcdef', -1, 1) as col1 ", new String[]{""});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", ",abcdef,3.14", "select substr(lower(col2), 2, 3) from table1",
+        new String[]{"bcd"});
+  }
+  
+  @Test
+  public void testLocate() throws IOException {
+    // normal case
+    testSimpleEval("select locate('abcdef', 'a') as col1 ", new String[]{"1"});
+    testSimpleEval("select locate('abcdef', 'a', 0) as col1 ", new String[]{"1"});
+    testSimpleEval("select locate('abcdef', 'a', 1) as col1 ", new String[]{"1"});
+    testSimpleEval("select locate('abcdef', 'z') as col1 ", new String[]{"0"});
+    testSimpleEval("select locate('abcdef', 'z', 1) as col1 ", new String[]{"0"});
+    testSimpleEval("select locate('foobarbar', 'bar') as col1 ", new String[]{"4"});
+    testSimpleEval("select locate('foobarbar', 'bar', 0) as col1 ", new String[]{"4"});
+    testSimpleEval("select locate('foobarbar', 'bar', 1) as col1 ", new String[]{"4"});
+    testSimpleEval("select locate('foobarbar', 'bar', 5) as col1 ", new String[]{"7"});
+    testSimpleEval("select locate('foobarbar', 'bar', 9) as col1 ", new String[]{"0"});
+    testSimpleEval("select locate('가나다라마라마', '라마') as col1 ", new String[]{"4"});
+    testSimpleEval("select locate('가나다라마라마', '라마', 5) as col1 ", new String[]{"6"});
+    // empty string
+    testSimpleEval("select locate('abcdef', '') as col1 ", new String[]{"1"});
+    testSimpleEval("select locate('abcdef', '', 2) as col1 ", new String[]{"2"});
+    // pos = last index of string (expected value(6) is tested on mysql)
+    testSimpleEval("select locate('abcdef', '', 6) as col1 ", new String[]{"6"});
+    // pos = last index + 1 (expected value(7) is tested on mysql)
+    testSimpleEval("select locate('abcdef', '', 7) as col1 ", new String[]{"7"});
+    // pos = greater then last index + 1 (expected value(0) is tested on mysql)
+    testSimpleEval("select locate('abcdef', '', 8) as col1 ", new String[]{"0"});
+    // pos = greater then last index + 1 (expected value(0) is tested on mysql)
+    testSimpleEval("select locate('abcdef', '', 9) as col1 ", new String[]{"0"});
+    testSimpleEval("select locate('가나다라', '', 2) as col1 ", new String[]{"2"});
+    testSimpleEval("select locate('가나다라', '', 4) as col1 ", new String[]{"4"});
+    testSimpleEval("select locate('가나다라', '', 5) as col1 ", new String[]{"5"});
+    testSimpleEval("select locate('가나다라', '', 6) as col1 ", new String[]{"0"});
+    
+    // negative pos    
+    testSimpleEval("select locate('abcdef', 'a', -1) as col1 ", new String[]{"0"});
+    testSimpleEval("select locate('abcdef', 'a', -5) as col1 ", new String[]{"0"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'cd') from table1", new String[]{"3"});
+    testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'cd', 1) from table1", new String[]{"3"});
+    testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'cd', 4) from table1", new String[]{"0"});
+    testEval(schema, "table1", ",abcdef,3.14", "select locate(col2, 'xy') from table1", new String[]{"0"});
+    // null string
+    testEval(schema, "table1", ",abcdef,3.14", "select locate(col1, 'cd') is null from table1", new String[]{"t"});
+    // nul substring
+    testEval(schema, "table1", ",abcdef,3.14", "select locate('cd', col1) is null from table1", new String[]{"t"});
+  }
+
+  @Test
+  public void testBitLength() throws IOException {
+    testSimpleEval("select bit_length('123456') as col1 ", new String[]{"48"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "ABC,DEF,3.14", "select bit_length(lower(col1) || lower(col2)) from table1",
+        new String[]{"48"});
+  }
+
+  @Test
+  public void testStrpos() throws IOException {
+    testSimpleEval("select strpos('tajo','jo') as col1 ", new String[]{"3"});
+    testSimpleEval("select strpos('tajo','') as col1 ", new String[]{"1"});
+    testSimpleEval("select strpos('tajo','abcdef') as col1 ", new String[]{"0"});
+    testSimpleEval("select strpos('일이삼사오육','삼사') as col1 ", new String[]{"3"});
+    testSimpleEval("select strpos('일이삼사오육','일이삼') as col1 ", new String[]{"1"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "ABCDEF,HIJKLMN,3.14", "select strpos(lower(col1) || lower(col2), 'fh') from table1",
+        new String[]{"6"});
+  }
+
+  @Test
+  public void testStrposb() throws IOException {
+    testSimpleEval("select strposb('tajo','jo') as col1 ", new String[]{"3"});
+    testSimpleEval("select strposb('tajo','') as col1 ", new String[]{"1"});
+    testSimpleEval("select strposb('tajo','abcdef') as col1 ", new String[]{"0"});
+    testSimpleEval("select strposb('일이삼사오육','삼사') as col1 ", new String[]{"7"});    //utf8 1 korean word = 3 chars
+    testSimpleEval("select strposb('일이삼사오육','삼사일') as col1 ", new String[]{"0"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    schema.addColumn("col3", TEXT);
+    testEval(schema, "table1", "ABCDEF,HIJKLMN,3.14", "select strposb(lower(col1) || lower(col2), 'fh') from table1",
+        new String[]{"6"});
+  }
+
+  @Test
+  public void testInitcap() throws IOException {
+    testSimpleEval("select initcap('hi bro') ", new String[]{"Hi Bro"});
+    testSimpleEval("select initcap('HI BRO') ", new String[]{"Hi Bro"});
+  }
+
+  @Test
+  public void testAscii() throws IOException {
+    testSimpleEval("select ascii('abc') as col1 ", new String[]{"97"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    testEval(schema, "table1", "abc", "select ascii(col1) from table1",
+            new String[]{"97"});
+    testEval(schema, "table1", "12", "select ascii(col1) from table1",
+            new String[]{"49"});
+
+  }
+
+  @Test
+  public void testChr() throws IOException {
+    testSimpleEval("select chr(48) as col1 ", new String[]{"0"});
+    testSimpleEval("select chr(49) as col1 ", new String[]{"1"});
+    testSimpleEval("select chr(50) as col1 ", new String[]{"2"});
+    testSimpleEval("select chr(64) as col1 ", new String[]{"@"});
+
+    Schema schema = new Schema();
+    schema.addColumn("col1", INT4);
+    testEval(schema, "table1", "65", "select chr(col1) from table1", new String[]{"A"});
+    testEval(schema, "table1", "66", "select chr(col1) from table1", new String[]{"B"});
+    testEval(schema, "table1", "52512", "select chr(col1) from table1", new String[]{"촠"});
+  }
+
+  @Test
+  public void testLpad() throws IOException {
+    testSimpleEval("select lpad('hi', 5, 'xy') ", new String[]{"xyxhi"});
+    testSimpleEval("select LPAD('hello', 7, 'xy') ", new String[]{"xyhello"});
+    testSimpleEval("select LPAD('hello', 3, 'xy') ", new String[]{"hel"});
+    testSimpleEval("select lPAD('hello', 7) ", new String[]{"  hello"});
+    testSimpleEval("select lPAD('가나다라', 3) ", new String[]{"가나다"});
+
+  }
+
+  @Test
+  public void testRpad() throws IOException {
+    testSimpleEval("select rpad('hi', 5, 'xy') ", new String[]{"hixyx"});
+    testSimpleEval("select RPAD('hello', 7, 'xy') ", new String[]{"helloxy"});
+    testSimpleEval("select RPAD('hello', 3, 'xy') ", new String[]{"hel"});
+    testSimpleEval("select rPAD('hello', 7) ", new String[]{"hello  "});
+    testSimpleEval("select rPAD('가나다라', 3) ", new String[]{"가나다"});
+
+  }
+
+  @Test
+  public void testQuote_ident() throws IOException {
+    testSimpleEval("select quote_ident('Foo bar') ", new String[]{"\"Foo bar\""});
+    testSimpleEval("select QUOTE_IDENT('Tajo Function') ", new String[]{"\"Tajo Function\""});
+  }
+
+  @Test
+  public void testEncode() throws IOException {
+    testSimpleEval("select encode('Hello\nworld', 'base64') ", new String[]{"SGVsbG8Kd29ybGQ="});
+    testSimpleEval("select encode('Hello\nworld', 'hex') ",
+        new String[]{"0x480x650x6c0x6c0x6f0x0a0x770x6f0x720x6c0x64"});
+    testSimpleEval("select encode('한글', 'base64') ", new String[]{"7ZWc6riA"});
+    testSimpleEval("select encode('한글', 'hex') ", new String[]{"0xd55c0xae00"});
+    testSimpleEval("select encode('한글\n테스트\t입니다.', 'hex') ",
+        new String[]{"0xd55c0xae000x0a0xd14c0xc2a40xd2b80x090xc7850xb2c80xb2e40x2e"});
+  }
+
+
+  @Test
+  public void testDecode() throws IOException {
+    testSimpleEval("select decode('SGVsbG8Kd29ybGQ=', 'base64') ",
+        new String[]{StringEscapeUtils.escapeJava("Hello\nworld")});
+    testSimpleEval("select decode('0x480x650x6c0x6c0x6f0x0a0x770x6f0x720x6c0x64', 'hex') ",
+        new String[]{StringEscapeUtils.escapeJava("Hello\nworld")});
+    testSimpleEval("select decode('7ZWc6riA', 'base64') ", new String[]{StringEscapeUtils.escapeJava("한글")});
+    testSimpleEval("select decode('0xd55c0xae00', 'hex') ", new String[]{StringEscapeUtils.escapeJava("한글")});
+    testSimpleEval("select decode('0xd55c0xae000x0a0xd14c0xc2a40xd2b80x090xc7850xb2c80xb2e40x2e', 'hex') ",
+        new String[]{StringEscapeUtils.escapeJava("한글\n" + "테스트\t입니다.")});
+  }
+
+  @Test
+  public void testFindInSet() throws IOException {
+    // abnormal cases
+    testSimpleEval("select find_in_set('cr','crt') as col1 ", new String[]{"0"}); // there is no matched string
+    testSimpleEval("select find_in_set('c,r','crt,c,cr,c,def') as col1 ", new String[]{"0"}); // abnormal parameter
+
+    // normal cases
+    testSimpleEval("select find_in_set('crt','crt,c,cr,d,def') as col1 ", new String[]{"1"});
+    testSimpleEval("select find_in_set('c','crt,c,cr,d,def') as col1 ", new String[]{"2"});
+    testSimpleEval("select find_in_set('def','crt,c,cr,d,def') as col1 ", new String[]{"5"});
+    // unicode test
+    testSimpleEval("select find_in_set('딸기','사과,배,옥수수,감자,딸기,수박') as col1 ", new String[]{"5"});
+
+    // null test
+    Schema schema = new Schema();
+    schema.addColumn("col1", TEXT);
+    schema.addColumn("col2", TEXT);
+    testEval(schema, "table1", "|crt,c,cr,c,def", "select find_in_set(col1, col2) is null from table1",
+        new String[]{"t"}, '|', true);
+    testEval(schema, "table1", "cr|", "select find_in_set(col1, col2) is null from table1",
+        new String[]{"t"}, '|', true);
+  }
+
+  @Test
+  public void testConcat() throws IOException {
+    testSimpleEval("select concat('333', '22') ", new String[]{"33322"});
+    testSimpleEval("select concat('한글', '22') ", new String[]{"한글22"});
+  }
+
+  @Test
+  public void testConcat_ws() throws IOException {
+    testSimpleEval("select concat_ws(',', '333', '22') ", new String[]{"333,22"});
+    testSimpleEval("select concat_ws(',', '한글', '22') ", new String[]{"한글,22"});
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/026368be/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestHiveQLAnalyzer.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestHiveQLAnalyzer.java b/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestHiveQLAnalyzer.java
new file mode 100644
index 0000000..ef21dc3
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestHiveQLAnalyzer.java
@@ -0,0 +1,271 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.parser;
+
+import com.google.common.base.Preconditions;
+import org.antlr.v4.runtime.ANTLRInputStream;
+import org.antlr.v4.runtime.CommonTokenStream;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.tajo.engine.parser.SQLParser.SqlContext;
+import org.apache.tajo.algebra.Expr;
+import org.apache.tajo.util.FileUtil;
+import org.junit.Test;
+
+import java.io.File;
+import java.io.IOException;
+
+import static org.junit.Assert.assertEquals;
+
+public class TestHiveQLAnalyzer {
+  private static final Log LOG = LogFactory.getLog(TestHiveQLAnalyzer.class.getName());
+  protected static final String BASE_PATH = "src/test/resources/queries/default/";
+
+  public static Expr parseQuery(String sql) {
+    ANTLRInputStream input = new ANTLRInputStream(sql);
+    SQLLexer lexer = new SQLLexer(input);
+    CommonTokenStream tokens = new CommonTokenStream(lexer);
+    SQLParser parser = new SQLParser(tokens);
+    parser.setBuildParseTree(true);
+    SQLAnalyzer visitor = new SQLAnalyzer();
+    SqlContext context = parser.sql();
+    return visitor.visitSql(context);
+  }
+
+  public static Expr parseHiveQL(String sql) {
+    HiveQLAnalyzer converter = new HiveQLAnalyzer();
+    return converter.parse(sql);
+  }
+
+  public static String getMethodName(int depth) {
+    final StackTraceElement[] ste = Thread.currentThread().getStackTrace();
+    return ste[depth].getMethodName();
+  }
+
+  public static void compareJsonResult(String sqlPath) throws IOException {
+      Preconditions.checkNotNull(sqlPath);
+      compareJsonResult(sqlPath, sqlPath);
+  }
+
+  public static void compareJsonResult(String sqlPath, String hiveqlPath) throws IOException {
+    Preconditions.checkNotNull(sqlPath, hiveqlPath);
+    String sql = FileUtil.readTextFile(new File(BASE_PATH + sqlPath));
+    String hiveQL = FileUtil.readTextFile(new File(BASE_PATH + hiveqlPath));
+    Expr expr = parseQuery(sql);
+    Expr hiveExpr = parseHiveQL(hiveQL);
+    assertEquals(expr.toJson(), hiveExpr.toJson());
+  }
+
+  @Test
+  public void testSelect1() throws IOException {
+    compareJsonResult("select_1.sql");
+  }
+
+  @Test
+  public void testSelect3() throws IOException {
+    compareJsonResult("select_3.sql");
+  }
+
+  @Test
+  public void testSelect4() throws IOException {
+    compareJsonResult("select_4.sql");
+  }
+
+  @Test
+  public void testSelect5() throws IOException {
+    compareJsonResult("select_5.sql");
+  }
+
+  @Test
+  public void testSelect7() throws IOException {
+    compareJsonResult("select_7.sql");
+  }
+
+  @Test
+  public void testSelect8() throws IOException {
+    compareJsonResult("select_8.sql");
+  }
+
+  @Test
+  public void testSelect9() throws IOException {
+    compareJsonResult("select_9.sql", "select_9.hiveql");
+  }
+
+  @Test
+  public void testSelect10() throws IOException {
+    compareJsonResult("select_10.sql", "select_10.hiveql");
+  }
+
+  //TODO: support beween condition
+  //@Test
+//  public void testSelect11() throws IOException {
+//    compareJsonResult("select_11.sql", "select_11.hiveql");
+//  }
+
+  @Test
+  public void testSelect12() throws IOException {
+    compareJsonResult("select_12.hiveql");
+  }
+
+  @Test
+  public void testSelect13() throws IOException {
+    compareJsonResult("select_13.sql", "select_13.hiveql");
+  }
+
+  @Test
+  public void testSelect14() throws IOException {
+    compareJsonResult("select_14.sql");
+  }
+
+  @Test
+  public void testSelect15() throws IOException {
+    compareJsonResult("select_15.sql", "select_15.hiveql");
+  }
+
+  @Test
+  public void testAsterisk1() throws IOException {
+    compareJsonResult("asterisk_1.sql");
+  }
+
+  @Test
+  public void testAsterisk2() throws IOException {
+    compareJsonResult("asterisk_2.sql");
+  }
+
+  @Test
+  public void testAsterisk3() throws IOException {
+    compareJsonResult("asterisk_3.sql");
+  }
+
+  @Test
+  public void testAsterisk4() throws IOException {
+    compareJsonResult("asterisk_4.sql");
+  }
+
+  @Test
+  public void testGroupby1() throws IOException {
+    compareJsonResult("groupby_1.sql");
+  }
+
+  @Test
+  public void testGroupby2() throws IOException {
+    compareJsonResult("groupby_2.sql");
+  }
+
+  @Test
+  public void testGroupby3() throws IOException {
+    compareJsonResult("groupby_3.sql");
+  }
+
+  @Test
+  public void testGroupby4() throws IOException {
+    compareJsonResult("groupby_4.sql");
+  }
+
+  @Test
+  public void testGroupby5() throws IOException {
+    compareJsonResult("groupby_5.sql");
+  }
+
+  @Test
+  public void testJoin2() throws IOException {
+    compareJsonResult("join_2.sql");
+  }
+
+  @Test
+  public void testJoin5() throws IOException {
+    compareJsonResult("join_5.sql");
+  }
+
+  @Test
+  public void testJoin6() throws IOException {
+    compareJsonResult("join_6.sql");
+  }
+
+  @Test
+  public void testJoin7() throws IOException {
+    compareJsonResult("join_7.sql");
+  }
+
+    //TODO: support complex join conditions
+    //@Test
+//  public void testJoin9() throws IOException {
+//    compareJsonResult("join_9.sql");
+//  }
+
+  @Test
+  public void testJoin12() throws IOException {
+    compareJsonResult("join_12.sql");
+  }
+
+  @Test
+  public void testJoin13() throws IOException {
+    compareJsonResult("join_13.sql");
+  }
+
+  @Test
+  public void testJoin14() throws IOException {
+    compareJsonResult("join_14.sql");
+  }
+
+  @Test
+  public void testJoin15() throws IOException {
+    compareJsonResult("join_15.sql", "join_15.hiveql");
+  }
+
+  @Test
+  public void testUnion1() throws IOException {
+    compareJsonResult("union_1.hiveql");
+  }
+
+  @Test
+  public void testInsert1() throws IOException {
+    compareJsonResult("insert_into_select_1.sql");
+  }
+
+  @Test
+  public void testInsert2() throws IOException {
+    compareJsonResult("insert_overwrite_into_select_2.sql", "insert_overwrite_into_select_2.hiveql");
+  }
+
+  @Test
+  public void testCreate1() throws IOException {
+    compareJsonResult("create_table_1.sql", "create_table_1.hiveql");
+  }
+
+  @Test
+  public void testCreate2() throws IOException {
+    compareJsonResult("create_table_2.sql", "create_table_2.hiveql");
+  }
+
+  @Test
+  public void testCreate11() throws IOException {
+    compareJsonResult("create_table_11.sql", "create_table_11.hiveql");
+  }
+
+  @Test
+  public void testCreate12() throws IOException {
+    compareJsonResult("create_table_12.sql", "create_table_12.hiveql");
+  }
+
+  @Test
+  public void testDrop() throws IOException {
+    compareJsonResult("drop_table.sql");
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/026368be/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestSQLAnalyzer.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestSQLAnalyzer.java b/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestSQLAnalyzer.java
new file mode 100644
index 0000000..eac9234
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/parser/TestSQLAnalyzer.java
@@ -0,0 +1,473 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.parser;
+
+import org.antlr.v4.runtime.ANTLRInputStream;
+import org.antlr.v4.runtime.CommonTokenStream;
+import org.apache.tajo.algebra.CreateTable;
+import org.apache.tajo.algebra.Expr;
+import org.apache.tajo.algebra.LiteralValue;
+import org.apache.tajo.algebra.OpType;
+import org.apache.tajo.engine.parser.SQLParser.SqlContext;
+import org.apache.tajo.util.FileUtil;
+import org.junit.Test;
+
+import java.io.File;
+import java.io.IOException;
+import java.util.Iterator;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+/**
+ * This unit tests uses a number of query files located in tajo/tajo-core/src/test/resources/queries.
+ * So, you must set tajo/tajo-core as the working directory.
+ */
+public class TestSQLAnalyzer {
+
+  public static Expr parseQuery(String sql) {
+    ANTLRInputStream input = new ANTLRInputStream(sql);
+    SQLLexer lexer = new SQLLexer(input);
+    CommonTokenStream tokens = new CommonTokenStream(lexer);
+    SQLParser parser = new SQLParser(tokens);
+    parser.setBuildParseTree(true);
+    SQLAnalyzer visitor = new SQLAnalyzer();
+    SqlContext context = parser.sql();
+    return visitor.visitSql(context);
+  }
+
+
+  @Test
+  public void testSelect1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/select_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSelect2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/select_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSelect3() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/select_3.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSelect4() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/select_4.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSelect5() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/select_5.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testAsterisk1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/asterisk_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testAsterisk2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/asterisk_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testAsterisk3() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/asterisk_3.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testAsterisk4() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/asterisk_4.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testGroupby1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/groupby_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin3() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_3.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin4() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_4.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin5() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_5.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin6() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_6.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin7() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_7.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin8() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_8.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin9() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_9.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin10() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_10.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testJoin11() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/join_11.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSet1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/set_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSet2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/set_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSet3() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/set_3.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testSet4() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/set_4.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testDropTable() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/drop_table.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable3() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_3.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable4() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_4.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable5() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_5.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable6() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_6.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable7() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_7.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable8() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_8.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable9() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_9.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTable10() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_10.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testCreateTablePartitionByHash1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_partition_by_hash_1.sql"));
+    Expr expr = parseQuery(sql);
+    assertEquals(OpType.CreateTable, expr.getType());
+    CreateTable createTable = (CreateTable) expr;
+    assertTrue(createTable.hasPartition());
+    assertEquals(CreateTable.PartitionType.HASH, createTable.getPartitionMethod().getPartitionType());
+    CreateTable.HashPartition hashPartition = createTable.getPartitionMethod();
+    assertEquals("col1", hashPartition.getColumns()[0].getCanonicalName());
+    assertTrue(hashPartition.hasQuantifier());
+  }
+
+  @Test
+  public void testCreateTablePartitionByHash2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_partition_by_hash_2.sql"));
+    Expr expr = parseQuery(sql);
+    assertEquals(OpType.CreateTable, expr.getType());
+    CreateTable createTable = (CreateTable) expr;
+    assertTrue(createTable.hasPartition());
+    assertEquals(CreateTable.PartitionType.HASH, createTable.getPartitionMethod().getPartitionType());
+    CreateTable.HashPartition hashPartition = createTable.getPartitionMethod();
+    assertEquals("col1", hashPartition.getColumns()[0].getCanonicalName());
+    assertTrue(hashPartition.hasSpecifiers());
+    assertEquals(3, hashPartition.getSpecifiers().size());
+  }
+
+  @Test
+  public void testCreateTablePartitionByRange() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_partition_by_range.sql"));
+    Expr expr = parseQuery(sql);
+    assertEquals(OpType.CreateTable, expr.getType());
+    CreateTable createTable = (CreateTable) expr;
+    assertTrue(createTable.hasPartition());
+    assertEquals(CreateTable.PartitionType.RANGE, createTable.getPartitionMethod().getPartitionType());
+    CreateTable.RangePartition rangePartition = createTable.getPartitionMethod();
+    assertEquals("col1", rangePartition.getColumns()[0].getCanonicalName());
+    assertEquals(3, rangePartition.getSpecifiers().size());
+  }
+
+  @Test
+  public void testCreateTablePartitionByList() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_partition_by_list.sql"));
+    Expr expr = parseQuery(sql);
+    assertEquals(OpType.CreateTable, expr.getType());
+    CreateTable createTable = (CreateTable) expr;
+    assertTrue(createTable.hasPartition());
+    assertEquals(CreateTable.PartitionType.LIST, createTable.getPartitionMethod().getPartitionType());
+    CreateTable.ListPartition listPartition = createTable.getPartitionMethod();
+    assertEquals("col1", listPartition.getColumns()[0].getCanonicalName());
+    assertEquals(2, listPartition.getSpecifiers().size());
+    Iterator<CreateTable.ListPartitionSpecifier> iterator = listPartition.getSpecifiers().iterator();
+    CreateTable.ListPartitionSpecifier specifier = iterator.next();
+    LiteralValue value1 = (LiteralValue) specifier.getValueList().getValues()[0];
+    LiteralValue value2 = (LiteralValue) specifier.getValueList().getValues()[1];
+    assertEquals("Seoul", value1.getValue());
+    assertEquals("서울", value2.getValue());
+
+    specifier = iterator.next();
+    value1 = (LiteralValue) specifier.getValueList().getValues()[0];
+    value2 = (LiteralValue) specifier.getValueList().getValues()[1];
+    assertEquals("Busan", value1.getValue());
+    assertEquals("부산", value2.getValue());
+  }
+
+  @Test
+  public void testCreateTablePartitionByColumn() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/create_table_partition_by_column.sql"));
+    Expr expr = parseQuery(sql);
+    assertEquals(OpType.CreateTable, expr.getType());
+    CreateTable createTable = (CreateTable) expr;
+    assertTrue(createTable.hasPartition());
+    assertEquals(CreateTable.PartitionType.COLUMN, createTable.getPartitionMethod().getPartitionType());
+    CreateTable.ColumnPartition columnPartition = createTable.getPartitionMethod();
+    assertEquals(3, columnPartition.getColumns().length);
+    assertEquals("col3", columnPartition.getColumns()[0].getColumnName());
+    assertEquals("col4", columnPartition.getColumns()[1].getColumnName());
+    assertEquals("col5", columnPartition.getColumns()[2].getColumnName());
+  }
+
+  @Test
+  public void testTableSubQuery1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/table_subquery1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testTableSubQuery2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/table_subquery2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInSubquery1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/in_subquery_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInSubquery2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/in_subquery_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testExistsPredicate1() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/exists_predicate_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testExistsPredicate2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/exists_predicate_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInsertIntoTable() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/insert_into_select_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInsertIntoLocation() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/insert_into_select_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInsertIntoTable2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/insert_into_select_3.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInsertOverwriteIntoTable() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/insert_overwrite_into_select_1.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInsertOverwriteIntoLocation() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/insert_overwrite_into_select_2.sql"));
+    parseQuery(sql);
+  }
+
+  @Test
+  public void testInsertOverwriteIntoTable2() throws IOException {
+    String sql = FileUtil.readTextFile(new File("src/test/resources/queries/default/insert_overwrite_into_select_3.sql"));
+    parseQuery(sql);
+  }
+
+  static String[] exprs = {
+      "1 + 2", // 0
+      "3 - 4", // 1
+      "5 * 6", // 2
+      "7 / 8", // 3
+      "10 % 2", // 4
+      "1 * 2 > 3 / 4", // 5
+      "1 * 2 < 3 / 4", // 6
+      "1 * 2 = 3 / 4", // 7
+      "1 * 2 != 3 / 4", // 8
+      "1 * 2 <> 3 / 4", // 9
+      "gender in ('male', 'female')", // 10
+      "gender not in ('male', 'female')", // 11
+      "score > 90 and age < 20", // 12
+      "score > 90 and age < 20 and name != 'hyunsik'", // 13
+      "score > 90 or age < 20", // 14
+      "score > 90 or age < 20 and name != 'hyunsik'", // 15
+      "((a+3 > 1) or 1=1) and (3 != (abc + 4) and type in (3,4))", // 16
+      "3", // 17
+      "1.2", // 18
+      "sum(age)", // 19
+      "now()", // 20
+      "not (90 > 100)", // 21
+      "type like '%top'", // 22
+      "type not like 'top%'", // 23
+      "col = 'value'", // 24
+      "col is null", // 25
+      "col is not null", // 26
+      "col = null", // 27
+      "col != null", // 38
+  };
+
+  public static Expr parseExpr(String sql) {
+    ANTLRInputStream input = new ANTLRInputStream(sql);
+    SQLLexer lexer = new SQLLexer(input);
+    CommonTokenStream tokens = new CommonTokenStream(lexer);
+    SQLParser parser = new SQLParser(tokens);
+    parser.setBuildParseTree(true);
+    SQLAnalyzer visitor = new SQLAnalyzer();
+    SQLParser.Value_expressionContext context = parser.value_expression();
+    return visitor.visitValue_expression(context);
+  }
+
+  @Test
+  public void testExprs() {
+    for (int i = 0; i < exprs.length; i++) {
+      parseExpr(exprs[i]);
+    }
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/026368be/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalNode.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalNode.java b/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalNode.java
new file mode 100644
index 0000000..3fe75f0
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalNode.java
@@ -0,0 +1,74 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.planner;
+
+import org.apache.hadoop.fs.Path;
+import org.apache.tajo.catalog.CatalogUtil;
+import org.apache.tajo.catalog.Column;
+import org.apache.tajo.catalog.Schema;
+import org.apache.tajo.catalog.proto.CatalogProtos.StoreType;
+import org.apache.tajo.common.TajoDataTypes.Type;
+import org.apache.tajo.engine.planner.logical.GroupbyNode;
+import org.apache.tajo.engine.planner.logical.JoinNode;
+import org.apache.tajo.engine.planner.logical.LogicalNode;
+import org.apache.tajo.engine.planner.logical.ScanNode;
+import org.junit.Test;
+
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+public class TestLogicalNode {
+  public static final void testCloneLogicalNode(LogicalNode n1) throws CloneNotSupportedException {
+    LogicalNode copy = (LogicalNode) n1.clone();
+    assertTrue(n1.deepEquals(copy));
+  }
+
+  @Test
+  public void testEquals() {
+    Schema schema = new Schema();
+    schema.addColumn("id", Type.INT4);
+    schema.addColumn("name", Type.TEXT);
+    schema.addColumn("age", Type.INT2);
+    GroupbyNode groupbyNode = new GroupbyNode(0);
+    groupbyNode.setGroupingColumns(new Column[]{schema.getColumn(1), schema.getColumn(2)});
+    ScanNode scanNode = new ScanNode(0);
+    scanNode.init(CatalogUtil.newTableDesc("in", schema, CatalogUtil.newTableMeta(StoreType.CSV), new Path("in")));
+
+    GroupbyNode groupbyNode2 = new GroupbyNode(0);
+    groupbyNode2.setGroupingColumns(new Column[]{schema.getColumn(1), schema.getColumn(2)});
+    JoinNode joinNode = new JoinNode(0);
+    ScanNode scanNode2 = new ScanNode(0);
+    scanNode2.init(CatalogUtil.newTableDesc("in2", schema, CatalogUtil.newTableMeta(StoreType.CSV), new Path("in2")));
+
+    groupbyNode.setChild(scanNode);
+    groupbyNode2.setChild(joinNode);
+    joinNode.setLeftChild(scanNode);
+    joinNode.setRightChild(scanNode2);
+
+    assertTrue(groupbyNode.equals(groupbyNode2));
+    assertFalse(groupbyNode.deepEquals(groupbyNode2));
+
+    ScanNode scanNode3 = new ScanNode(0);
+    scanNode3.init(CatalogUtil.newTableDesc("in", schema, CatalogUtil.newTableMeta(StoreType.CSV), new Path("in")));
+    groupbyNode2.setChild(scanNode3);
+
+    assertTrue(groupbyNode.equals(groupbyNode2));
+    assertTrue(groupbyNode.deepEquals(groupbyNode2));
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/026368be/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalOptimizer.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalOptimizer.java b/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalOptimizer.java
new file mode 100644
index 0000000..5acd512
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalOptimizer.java
@@ -0,0 +1,263 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.planner;
+
+import org.apache.tajo.LocalTajoTestingUtility;
+import org.apache.tajo.TajoConstants;
+import org.apache.tajo.TajoTestingCluster;
+import org.apache.tajo.algebra.Expr;
+import org.apache.tajo.catalog.*;
+import org.apache.tajo.catalog.proto.CatalogProtos.FunctionType;
+import org.apache.tajo.catalog.proto.CatalogProtos.StoreType;
+import org.apache.tajo.common.TajoDataTypes.Type;
+import org.apache.tajo.engine.function.builtin.SumInt;
+import org.apache.tajo.engine.parser.SQLAnalyzer;
+import org.apache.tajo.engine.planner.logical.*;
+import org.apache.tajo.master.TajoMaster;
+import org.apache.tajo.master.session.Session;
+import org.apache.tajo.util.CommonTestingUtil;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import static org.apache.tajo.TajoConstants.DEFAULT_DATABASE_NAME;
+import static org.apache.tajo.TajoConstants.DEFAULT_TABLESPACE_NAME;
+import static org.junit.Assert.*;
+
+public class TestLogicalOptimizer {
+
+  private static TajoTestingCluster util;
+  private static CatalogService catalog;
+  private static SQLAnalyzer sqlAnalyzer;
+  private static LogicalPlanner planner;
+  private static LogicalOptimizer optimizer;
+  private static Session session = LocalTajoTestingUtility.createDummySession();
+
+  @BeforeClass
+  public static void setUp() throws Exception {
+    util = new TajoTestingCluster();
+    util.startCatalogCluster();
+    catalog = util.getMiniCatalogCluster().getCatalog();
+    catalog.createTablespace(DEFAULT_TABLESPACE_NAME, "hdfs://localhost:1234/warehouse");
+    catalog.createDatabase(DEFAULT_DATABASE_NAME, DEFAULT_TABLESPACE_NAME);
+    for (FunctionDesc funcDesc : TajoMaster.initBuiltinFunctions()) {
+      catalog.createFunction(funcDesc);
+    }
+    
+    Schema schema = new Schema();
+    schema.addColumn("name", Type.TEXT);
+    schema.addColumn("empid", Type.INT4);
+    schema.addColumn("deptname", Type.TEXT);
+
+    Schema schema2 = new Schema();
+    schema2.addColumn("deptname", Type.TEXT);
+    schema2.addColumn("manager", Type.TEXT);
+
+    Schema schema3 = new Schema();
+    schema3.addColumn("deptname", Type.TEXT);
+    schema3.addColumn("score", Type.INT4);
+    schema3.addColumn("phone", Type.INT4);
+
+    TableMeta meta = CatalogUtil.newTableMeta(StoreType.CSV);
+    TableDesc people = new TableDesc(
+        CatalogUtil.buildFQName(TajoConstants.DEFAULT_DATABASE_NAME, "employee"), schema, meta,
+        CommonTestingUtil.getTestDir());
+    catalog.createTable(people);
+
+    TableDesc student =
+        new TableDesc(
+            CatalogUtil.buildFQName(DEFAULT_DATABASE_NAME, "dept"), schema2, StoreType.CSV, new Options(),
+            CommonTestingUtil.getTestDir());
+    catalog.createTable(student);
+
+    TableDesc score =
+        new TableDesc(
+            CatalogUtil.buildFQName(DEFAULT_DATABASE_NAME, "score"), schema3, StoreType.CSV, new Options(),
+            CommonTestingUtil.getTestDir());
+    catalog.createTable(score);
+
+    FunctionDesc funcDesc = new FunctionDesc("sumtest", SumInt.class, FunctionType.GENERAL,
+        CatalogUtil.newSimpleDataType(Type.INT4),
+        CatalogUtil.newSimpleDataTypeArray(Type.INT4));
+
+    catalog.createFunction(funcDesc);
+    sqlAnalyzer = new SQLAnalyzer();
+    planner = new LogicalPlanner(catalog);
+    optimizer = new LogicalOptimizer(util.getConfiguration());
+  }
+
+  @AfterClass
+  public static void tearDown() throws Exception {
+    util.shutdownCatalogCluster();
+  }
+  
+  static String[] QUERIES = {
+    "select name, manager from employee as e, dept as dp where e.deptName = dp.deptName", // 0
+    "select name, empId, deptName from employee where empId > 500", // 1
+    "select name from employee where empId = 100", // 2
+    "select name, max(empId) as final from employee where empId > 50 group by name", // 3
+    "select name, score from employee natural join score", // 4
+    "select name, score from employee join score on employee.deptName = score.deptName", // 5
+  };
+  
+  @Test
+  public final void testProjectionPushWithNaturalJoin() throws PlanningException, CloneNotSupportedException {
+    // two relations
+    Expr expr = sqlAnalyzer.parse(QUERIES[4]);
+    LogicalPlan newPlan = planner.createPlan(session, expr);
+    LogicalNode plan = newPlan.getRootBlock().getRoot();
+    assertEquals(NodeType.ROOT, plan.getType());
+    LogicalRootNode root = (LogicalRootNode) plan;
+    TestLogicalNode.testCloneLogicalNode(root);
+    assertEquals(NodeType.PROJECTION, root.getChild().getType());
+    ProjectionNode projNode = root.getChild();
+    assertEquals(NodeType.JOIN, projNode.getChild().getType());
+    JoinNode joinNode = projNode.getChild();
+    assertEquals(NodeType.SCAN, joinNode.getLeftChild().getType());
+    assertEquals(NodeType.SCAN, joinNode.getRightChild().getType());
+    
+    LogicalNode optimized = optimizer.optimize(newPlan);
+
+    assertEquals(NodeType.ROOT, optimized.getType());
+    root = (LogicalRootNode) optimized;
+    TestLogicalNode.testCloneLogicalNode(root);
+    assertEquals(NodeType.JOIN, root.getChild().getType());
+    joinNode = root.getChild();
+    assertEquals(NodeType.SCAN, joinNode.getLeftChild().getType());
+    assertEquals(NodeType.SCAN, joinNode.getRightChild().getType());
+  }
+  
+  @Test
+  public final void testProjectionPushWithInnerJoin() throws PlanningException {
+    // two relations
+    Expr expr = sqlAnalyzer.parse(QUERIES[5]);
+    LogicalPlan newPlan = planner.createPlan(session, expr);
+    optimizer.optimize(newPlan);
+  }
+  
+  @Test
+  public final void testProjectionPush() throws CloneNotSupportedException, PlanningException {
+    // two relations
+    Expr expr = sqlAnalyzer.parse(QUERIES[2]);
+    LogicalPlan newPlan = planner.createPlan(session, expr);
+    LogicalNode plan = newPlan.getRootBlock().getRoot();
+    
+    assertEquals(NodeType.ROOT, plan.getType());
+    LogicalRootNode root = (LogicalRootNode) plan;
+    TestLogicalNode.testCloneLogicalNode(root);
+    assertEquals(NodeType.PROJECTION, root.getChild().getType());
+    ProjectionNode projNode = root.getChild();
+    assertEquals(NodeType.SELECTION, projNode.getChild().getType());
+    SelectionNode selNode = projNode.getChild();
+    assertEquals(NodeType.SCAN, selNode.getChild().getType());
+
+    LogicalNode optimized = optimizer.optimize(newPlan);
+    assertEquals(NodeType.ROOT, optimized.getType());
+    root = (LogicalRootNode) optimized;
+    TestLogicalNode.testCloneLogicalNode(root);
+    assertEquals(NodeType.SCAN, root.getChild().getType());
+  }
+  
+  @Test
+  public final void testOptimizeWithGroupBy() throws CloneNotSupportedException, PlanningException {
+    Expr expr = sqlAnalyzer.parse(QUERIES[3]);
+    LogicalPlan newPlan = planner.createPlan(session, expr);
+    LogicalNode plan = newPlan.getRootBlock().getRoot();
+        
+    assertEquals(NodeType.ROOT, plan.getType());
+    LogicalRootNode root = (LogicalRootNode) plan;
+    TestLogicalNode.testCloneLogicalNode(root);
+    assertEquals(NodeType.PROJECTION, root.getChild().getType());
+    ProjectionNode projNode = root.getChild();
+    assertEquals(NodeType.GROUP_BY, projNode.getChild().getType());
+    GroupbyNode groupbyNode = projNode.getChild();
+    assertEquals(NodeType.SELECTION, groupbyNode.getChild().getType());
+    SelectionNode selNode = groupbyNode.getChild();
+    assertEquals(NodeType.SCAN, selNode.getChild().getType());
+    
+    LogicalNode optimized = optimizer.optimize(newPlan);
+    assertEquals(NodeType.ROOT, optimized.getType());
+    root = (LogicalRootNode) optimized;
+    TestLogicalNode.testCloneLogicalNode(root);
+    assertEquals(NodeType.GROUP_BY, root.getChild().getType());
+    groupbyNode = root.getChild();
+    assertEquals(NodeType.SCAN, groupbyNode.getChild().getType());
+  }
+
+  @Test
+  public final void testPushable() throws CloneNotSupportedException, PlanningException {
+    // two relations
+    Expr expr = sqlAnalyzer.parse(QUERIES[0]);
+    LogicalPlan newPlan = planner.createPlan(session, expr);
+    LogicalNode plan = newPlan.getRootBlock().getRoot();
+    
+    assertEquals(NodeType.ROOT, plan.getType());
+    LogicalRootNode root = (LogicalRootNode) plan;
+    TestLogicalNode.testCloneLogicalNode(root);
+
+    assertEquals(NodeType.PROJECTION, root.getChild().getType());
+    ProjectionNode projNode = root.getChild();
+
+    assertEquals(NodeType.SELECTION, projNode.getChild().getType());
+    SelectionNode selNode = projNode.getChild();
+    
+    assertEquals(NodeType.JOIN, selNode.getChild().getType());
+    JoinNode joinNode = selNode.getChild();
+    assertFalse(joinNode.hasJoinQual());
+    
+    // Test for Pushable
+    assertTrue(LogicalPlanner.checkIfBeEvaluatedAtJoin(newPlan.getRootBlock(), selNode.getQual(), joinNode, false));
+    
+    // Optimized plan
+    LogicalNode optimized = optimizer.optimize(newPlan);
+    assertEquals(NodeType.ROOT, optimized.getType());
+    root = (LogicalRootNode) optimized;
+    
+    assertEquals(NodeType.JOIN, root.getChild().getType());
+    joinNode = root.getChild();
+    assertTrue(joinNode.hasJoinQual());
+    
+    // Scan Pushable Test
+    expr = sqlAnalyzer.parse(QUERIES[1]);
+    newPlan = planner.createPlan(session, expr);
+    plan = newPlan.getRootBlock().getRoot();
+    
+    assertEquals(NodeType.ROOT, plan.getType());
+    root = (LogicalRootNode) plan;
+    TestLogicalNode.testCloneLogicalNode(root);
+
+    assertEquals(NodeType.PROJECTION, root.getChild().getType());
+    projNode = root.getChild();
+
+    assertEquals(NodeType.SELECTION, projNode.getChild().getType());
+    selNode = projNode.getChild();
+    
+    assertEquals(NodeType.SCAN, selNode.getChild().getType());
+    ScanNode scanNode = selNode.getChild();
+    // Test for Join Node
+    assertTrue(LogicalPlanner.checkIfBeEvaluatedAtRelation(newPlan.getRootBlock(), selNode.getQual(), scanNode));
+  }
+
+  @Test
+  public final void testInsertInto() throws CloneNotSupportedException, PlanningException {
+    Expr expr = sqlAnalyzer.parse(TestLogicalPlanner.insertStatements[0]);
+    LogicalPlan newPlan = planner.createPlan(session, expr);
+    optimizer.optimize(newPlan);
+  }
+}

http://git-wip-us.apache.org/repos/asf/tajo/blob/026368be/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalPlan.java
----------------------------------------------------------------------
diff --git a/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalPlan.java b/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalPlan.java
new file mode 100644
index 0000000..2f1e0f9
--- /dev/null
+++ b/tajo-core/src/test/java/org/apache/tajo/engine/planner/TestLogicalPlan.java
@@ -0,0 +1,125 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.tajo.engine.planner;
+
+import org.apache.tajo.LocalTajoTestingUtility;
+import org.apache.tajo.TajoTestingCluster;
+import org.apache.tajo.benchmark.TPCH;
+import org.apache.tajo.catalog.*;
+import org.apache.tajo.catalog.proto.CatalogProtos;
+import org.apache.tajo.catalog.statistics.TableStats;
+import org.apache.tajo.engine.parser.SQLAnalyzer;
+import org.apache.tajo.engine.planner.graph.SimpleDirectedGraph;
+import org.apache.tajo.master.TajoMaster;
+import org.apache.tajo.util.CommonTestingUtil;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.util.HashSet;
+import java.util.Set;
+
+import static org.apache.tajo.TajoConstants.DEFAULT_DATABASE_NAME;
+import static org.apache.tajo.TajoConstants.DEFAULT_TABLESPACE_NAME;
+import static org.apache.tajo.engine.planner.LogicalPlan.BlockType;
+import static org.junit.Assert.*;
+
+public class TestLogicalPlan {
+  private static TajoTestingCluster util;
+  private static TPCH tpch;
+  private static CatalogService catalog;
+  private static SQLAnalyzer sqlAnalyzer = new SQLAnalyzer();
+  private static LogicalPlanner planner;
+  private static LogicalOptimizer optimizer;
+
+  @BeforeClass
+  public static void setup() throws Exception {
+    util = new TajoTestingCluster();
+    util.startCatalogCluster();
+    catalog = util.getMiniCatalogCluster().getCatalog();
+    catalog.createTablespace(DEFAULT_TABLESPACE_NAME, CommonTestingUtil.getTestDir().toUri().toString());
+    catalog.createDatabase(DEFAULT_DATABASE_NAME, DEFAULT_TABLESPACE_NAME);
+    for (FunctionDesc funcDesc : TajoMaster.initBuiltinFunctions()) {
+      catalog.createFunction(funcDesc);
+    }
+
+    // TPC-H Schema for Complex Queries
+    String [] tpchTables = {
+        "part", "supplier", "partsupp", "nation", "region", "lineitem", "customer", "orders"
+    };
+    int [] tableVolumns = {
+        100, 200, 50, 5, 5, 800, 300, 100
+    };
+    tpch = new TPCH();
+    tpch.loadSchemas();
+    tpch.loadOutSchema();
+
+    for (int i = 0; i < tpchTables.length; i++) {
+      TableMeta m = CatalogUtil.newTableMeta(CatalogProtos.StoreType.CSV);
+      TableStats stats = new TableStats();
+      stats.setNumBytes(tableVolumns[i]);
+      TableDesc d = CatalogUtil.newTableDesc(tpchTables[i], tpch.getSchema(tpchTables[i]), m,
+          CommonTestingUtil.getTestDir());
+      d.setStats(stats);
+      catalog.createTable(d);
+    }
+    planner = new LogicalPlanner(catalog);
+    optimizer = new LogicalOptimizer(util.getConfiguration());
+  }
+
+  public static void tearDown() {
+    util.shutdownCatalogCluster();
+  }
+
+  @Test
+  public final void testQueryBlockGraph() {
+    LogicalPlan plan = new LogicalPlan(LocalTajoTestingUtility.createDummySession().getCurrentDatabase(), planner);
+    LogicalPlan.QueryBlock root = plan.newAndGetBlock(LogicalPlan.ROOT_BLOCK);
+    LogicalPlan.QueryBlock new1 = plan.newQueryBlock();
+    LogicalPlan.QueryBlock new2 = plan.newQueryBlock();
+
+    plan.getQueryBlockGraph().addEdge(new1.getName(), root.getName(),
+        new LogicalPlan.BlockEdge(new1, root, BlockType.TableSubQuery));
+    plan.getQueryBlockGraph().addEdge(new2.getName(), root.getName(),
+        new LogicalPlan.BlockEdge(new2, root, BlockType.TableSubQuery));
+
+    SimpleDirectedGraph<String, LogicalPlan.BlockEdge> graph = plan.getQueryBlockGraph();
+    assertEquals(2, graph.getChildCount(root.getName()));
+
+    assertEquals(root.getName(), graph.getParent(new1.getName(), 0));
+    assertEquals(root.getName(), graph.getParent(new2.getName(), 0));
+
+    assertTrue(graph.isRoot(root.getName()));
+    assertFalse(graph.isRoot(new1.getName()));
+    assertFalse(graph.isRoot(new2.getName()));
+
+    assertFalse(graph.isLeaf(root.getName()));
+    assertTrue(graph.isLeaf(new1.getName()));
+    assertTrue(graph.isLeaf(new2.getName()));
+
+    Set<LogicalPlan.QueryBlock> result = new HashSet<LogicalPlan.QueryBlock>();
+    result.add(new1);
+    result.add(new2);
+
+    Set<LogicalPlan.QueryBlock> childs = new HashSet<LogicalPlan.QueryBlock>(plan.getChildBlocks(root));
+    assertEquals(result, childs);
+
+    assertEquals(root, plan.getParentBlock(new1));
+    assertEquals(root, plan.getParentBlock(new2));
+  }
+}


Mime
View raw message