Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
  /*
   * Licensed 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 com.facebook.presto.sql;
 
 
 import java.util.Map;
 
 import static com.facebook.presto.SessionTestUtils.TEST_SESSION;
 import static com.facebook.presto.spi.type.BigintType.BIGINT;
 import static com.facebook.presto.spi.type.BooleanType.BOOLEAN;
 import static com.facebook.presto.spi.type.DateType.DATE;
 import static com.facebook.presto.spi.type.DoubleType.DOUBLE;
 import static com.facebook.presto.spi.type.IntervalDayTimeType.INTERVAL_DAY_TIME;
 import static com.facebook.presto.spi.type.TimeType.TIME;
 import static com.facebook.presto.spi.type.TimestampType.TIMESTAMP;
 import static com.facebook.presto.spi.type.VarcharType.VARCHAR;
 import static com.facebook.presto.sql.ExpressionFormatter.formatExpression;
 import static com.facebook.presto.sql.analyzer.ExpressionAnalyzer.getExpressionTypes;
 import static com.facebook.presto.sql.planner.ExpressionInterpreter.expressionInterpreter;
 import static com.facebook.presto.sql.planner.ExpressionInterpreter.expressionOptimizer;
 import static java.nio.charset.StandardCharsets.UTF_8;
 import static java.util.Locale.ENGLISH;
 import static org.testng.Assert.assertEquals;
 
 {
     private static final Map<SymbolTypeSYMBOL_TYPES = ImmutableMap.<SymbolType>builder()
             .put(new Symbol("bound_long"), )
             .put(new Symbol("bound_string"), )
             .put(new Symbol("bound_double"), )
             .put(new Symbol("bound_boolean"), )
             .put(new Symbol("bound_date"), )
             .put(new Symbol("bound_time"), )
             .put(new Symbol("bound_timestamp"), )
             .put(new Symbol("bound_pattern"), )
             .put(new Symbol("bound_null_string"), )
             .put(new Symbol("time"), // for testing reserved identifiers
             .put(new Symbol("unbound_long"), )
             .put(new Symbol("unbound_long2"), )
             .put(new Symbol("unbound_string"), )
             .put(new Symbol("unbound_double"), )
             .put(new Symbol("unbound_boolean"), )
             .put(new Symbol("unbound_date"), )
             .put(new Symbol("unbound_time"), )
             .put(new Symbol("unbound_timestamp"), )
             .put(new Symbol("unbound_interval"), )
             .put(new Symbol("unbound_pattern"), )
             .put(new Symbol("unbound_null_string"), )
             .build();
 
     private static final SqlParser SQL_PARSER = new SqlParser();
     private static final Metadata METADATA = MetadataManager.createTestMetadataManager();
 
     @Test
     public void testAnd()
             throws Exception
     {
         assertOptimizedEquals("true and false""false");
         assertOptimizedEquals("false and true""false");
         assertOptimizedEquals("false and false""false");
 
         assertOptimizedEquals("true and null""null");
        assertOptimizedEquals("false and null""false");
        assertOptimizedEquals("null and true""null");
        assertOptimizedEquals("null and false""false");
        assertOptimizedEquals("null and null""null");
        assertOptimizedEquals("unbound_string='z' and true""unbound_string='z'");
        assertOptimizedEquals("unbound_string='z' and false""false");
        assertOptimizedEquals("true and unbound_string='z'""unbound_string='z'");
        assertOptimizedEquals("false and unbound_string='z'""false");
        assertOptimizedEquals("bound_string='z' and bound_long=1+1""bound_string='z' and bound_long=2");
    }
    @Test
    public void testOr()
            throws Exception
    {
        assertOptimizedEquals("true or true""true");
        assertOptimizedEquals("true or false""true");
        assertOptimizedEquals("false or true""true");
        assertOptimizedEquals("false or false""false");
        assertOptimizedEquals("true or null""true");
        assertOptimizedEquals("null or true""true");
        assertOptimizedEquals("null or null""null");
        assertOptimizedEquals("false or null""null");
        assertOptimizedEquals("null or false""null");
        assertOptimizedEquals("bound_string='z' or true""true");
        assertOptimizedEquals("bound_string='z' or false""bound_string='z'");
        assertOptimizedEquals("true or bound_string='z'""true");
        assertOptimizedEquals("false or bound_string='z'""bound_string='z'");
        assertOptimizedEquals("bound_string='z' or bound_long=1+1""bound_string='z' or bound_long=2");
    }
    @Test
    public void testComparison()
            throws Exception
    {
        assertOptimizedEquals("null = null""null");
        assertOptimizedEquals("'a' = 'b'""false");
        assertOptimizedEquals("'a' = 'a'""true");
        assertOptimizedEquals("'a' = null""null");
        assertOptimizedEquals("null = 'a'""null");
        assertOptimizedEquals("bound_long = 1234""true");
        assertOptimizedEquals("bound_double = 12.34""true");
        assertOptimizedEquals("bound_string = 'hello'""true");
        assertOptimizedEquals("bound_long = unbound_long""1234 = unbound_long");
        assertOptimizedEquals("10151082135029368 = 10151082135029369""false");
    }
    @Test
    public void testIsDistinctFrom()
            throws Exception
    {
        assertOptimizedEquals("null is distinct from null""false");
        assertOptimizedEquals("3 is distinct from 4""true");
        assertOptimizedEquals("3 is distinct from 3""false");
        assertOptimizedEquals("3 is distinct from null""true");
        assertOptimizedEquals("null is distinct from 3""true");
        assertOptimizedEquals("10151082135029368 is distinct from 10151082135029369""true");
    }
    @Test
    public void testIsNull()
            throws Exception
    {
        assertOptimizedEquals("null is null""true");
        assertOptimizedEquals("1 is null""false");
        assertOptimizedEquals("1.0 is null""false");
        assertOptimizedEquals("'a' is null""false");
        assertOptimizedEquals("true is null""false");
        assertOptimizedEquals("null+1 is null""true");
        assertOptimizedEquals("unbound_string is null""unbound_string is null");
        assertOptimizedEquals("unbound_long+(1+1) is null""unbound_long+2 is null");
    }
    @Test
    public void testIsNotNull()
            throws Exception
    {
        assertOptimizedEquals("null is not null""false");
        assertOptimizedEquals("1 is not null""true");
        assertOptimizedEquals("1.0 is not null""true");
        assertOptimizedEquals("'a' is not null""true");
        assertOptimizedEquals("true is not null""true");
        assertOptimizedEquals("null+1 is not null""false");
        assertOptimizedEquals("unbound_string is not null""unbound_string is not null");
        assertOptimizedEquals("unbound_long+(1+1) is not null""unbound_long+2 is not null");
    }
    @Test
    public void testNullIf()
            throws Exception
    {
        assertOptimizedEquals("nullif(true, true)""null");
        assertOptimizedEquals("nullif(true, false)""true");
        assertOptimizedEquals("nullif(null, false)""null");
        assertOptimizedEquals("nullif(true, null)""true");
        assertOptimizedEquals("nullif('a', 'a')""null");
        assertOptimizedEquals("nullif('a', 'b')""'a'");
        assertOptimizedEquals("nullif(null, 'b')""null");
        assertOptimizedEquals("nullif('a', null)""'a'");
        assertOptimizedEquals("nullif(1, 1)""null");
        assertOptimizedEquals("nullif(1, 2)""1");
        assertOptimizedEquals("nullif(1.0, 1)""null");
        assertOptimizedEquals("nullif(1.1, 1)""1.1");
        assertOptimizedEquals("nullif(1.1, 1.1)""null");
        assertOptimizedEquals("nullif(1, 2-1)""null");
        assertOptimizedEquals("nullif(null, null)""null");
        assertOptimizedEquals("nullif(1, null)""1");
        assertOptimizedEquals("nullif(unbound_long, 1)""nullif(unbound_long, 1)");
        assertOptimizedEquals("nullif(unbound_long, unbound_long2)""nullif(unbound_long, unbound_long2)");
        assertOptimizedEquals("nullif(unbound_long, unbound_long2+(1+1))""nullif(unbound_long, unbound_long2+2)");
    }
    @Test
    public void testNegative()
            throws Exception
    {
        assertOptimizedEquals("-(1)""-1");
        assertOptimizedEquals("-(unbound_long+1)""-(unbound_long+1)");
        assertOptimizedEquals("-(1+1)""-2");
        assertOptimizedEquals("-(null)""null");
        assertOptimizedEquals("-(unbound_long+(1+1))""-(unbound_long+2)");
    }
    @Test
    public void testNot()
            throws Exception
    {
        assertOptimizedEquals("not true""false");
        assertOptimizedEquals("not false""true");
        assertOptimizedEquals("not null""null");
        assertOptimizedEquals("not 1=1""false");
        assertOptimizedEquals("not 1!=1""true");
        assertOptimizedEquals("not unbound_long=1""not unbound_long=1");
        assertOptimizedEquals("not unbound_long=(1+1)""not unbound_long=2");
    }
    @Test
    public void testFunctionCall()
            throws Exception
    {
        assertOptimizedEquals("abs(-5)""5");
        assertOptimizedEquals("abs(-10-5)""15");
        assertOptimizedEquals("abs(-bound_long + 1)""1233");
        assertOptimizedEquals("abs(-bound_long)""1234");
        assertOptimizedEquals("abs(unbound_long)""abs(unbound_long)");
        assertOptimizedEquals("abs(unbound_long + 1)""abs(unbound_long + 1)");
    }
    @Test
    public void testNonDeterministicFunctionCall()
            throws Exception
    {
        // optimize should do nothing
        assertOptimizedEquals("random()""random()");
        // evaluate should execute
        Object value = evaluate("random()");
        Assert.assertTrue(value instanceof Double);
        double randomValue = (doublevalue;
        Assert.assertTrue(0 <= randomValue && randomValue < 1);
    }
    @Test
    public void testBetween()
            throws Exception
    {
        assertOptimizedEquals("3 between 2 and 4""true");
        assertOptimizedEquals("2 between 3 and 4""false");
        assertOptimizedEquals("null between 2 and 4""null");
        assertOptimizedEquals("3 between null and 4""null");
        assertOptimizedEquals("3 between 2 and null""null");
        assertOptimizedEquals("'c' between 'b' and 'd'""true");
        assertOptimizedEquals("'b' between 'c' and 'd'""false");
        assertOptimizedEquals("null between 'b' and 'd'""null");
        assertOptimizedEquals("'c' between null and 'd'""null");
        assertOptimizedEquals("'c' between 'b' and null""null");
        assertOptimizedEquals("bound_long between 1000 and 2000""true");
        assertOptimizedEquals("bound_long between 3 and 4""false");
        assertOptimizedEquals("bound_string between 'e' and 'i'""true");
        assertOptimizedEquals("bound_string between 'a' and 'b'""false");
        assertOptimizedEquals("bound_long between unbound_long and 2000 + 1""1234 between unbound_long and 2001");
        assertOptimizedEquals("bound_string between unbound_string and 'bar'""'hello' between unbound_string and 'bar'");
    }
    @Test
    public void testExtract()
    {
        DateTime dateTime = new DateTime(2001, 8, 22, 3, 4, 5, 321, .);
        double seconds = dateTime.getMillis() / 1000.0;
        assertOptimizedEquals("extract (YEAR from from_unixtime(" + seconds + "))""2001");
        assertOptimizedEquals("extract (QUARTER from from_unixtime(" + seconds + "))""3");
        assertOptimizedEquals("extract (MONTH from from_unixtime(" + seconds + "))""8");
        assertOptimizedEquals("extract (WEEK from from_unixtime(" + seconds + "))""34");
        assertOptimizedEquals("extract (DOW from from_unixtime(" + seconds + "))""3");
        assertOptimizedEquals("extract (DOY from from_unixtime(" + seconds + "))""234");
        assertOptimizedEquals("extract (DAY from from_unixtime(" + seconds + "))""22");
        assertOptimizedEquals("extract (HOUR from from_unixtime(" + seconds + "))""3");
        assertOptimizedEquals("extract (MINUTE from from_unixtime(" + seconds + "))""4");
        assertOptimizedEquals("extract (SECOND from from_unixtime(" + seconds + "))""5");
        assertOptimizedEquals("extract (TIMEZONE_HOUR from from_unixtime(" + seconds + ", 7, 9))""7");
        assertOptimizedEquals("extract (TIMEZONE_MINUTE from from_unixtime(" + seconds + ", 7, 9))""9");
        assertOptimizedEquals("extract (YEAR from bound_timestamp)""2001");
        assertOptimizedEquals("extract (QUARTER from bound_timestamp)""3");
        assertOptimizedEquals("extract (MONTH from bound_timestamp)""8");
        assertOptimizedEquals("extract (WEEK from bound_timestamp)""34");
        assertOptimizedEquals("extract (DOW from bound_timestamp)""3");
        assertOptimizedEquals("extract (DOY from bound_timestamp)""234");
        assertOptimizedEquals("extract (DAY from bound_timestamp)""22");
        assertOptimizedEquals("extract (HOUR from bound_timestamp)""3");
        assertOptimizedEquals("extract (MINUTE from bound_timestamp)""4");
        assertOptimizedEquals("extract (SECOND from bound_timestamp)""5");
        // todo reenable when cast as timestamp with time zone is implemented
        // todo add bound timestamp with time zone
        //assertOptimizedEquals("extract (TIMEZONE_HOUR from bound_timestamp)", "0");
        //assertOptimizedEquals("extract (TIMEZONE_MINUTE from bound_timestamp)", "0");
        assertOptimizedEquals("extract (YEAR from unbound_timestamp)""extract (YEAR from unbound_timestamp)");
        assertOptimizedEquals("extract (SECOND from bound_timestamp + INTERVAL '3' SECOND)""8");
    }
    @Test
    public void testIn()
            throws Exception
    {
        assertOptimizedEquals("3 in (2, 4, 3, 5)""true");
        assertOptimizedEquals("3 in (2, 4, 9, 5)""false");
        assertOptimizedEquals("3 in (2, null, 3, 5)""true");
        assertOptimizedEquals("'foo' in ('bar', 'baz', 'foo', 'blah')""true");
        assertOptimizedEquals("'foo' in ('bar', 'baz', 'buz', 'blah')""false");
        assertOptimizedEquals("'foo' in ('bar', null, 'foo', 'blah')""true");
        assertOptimizedEquals("null in (2, null, 3, 5)""null");
        assertOptimizedEquals("3 in (2, null)""null");
        assertOptimizedEquals("bound_long in (2, 1234, 3, 5)""true");
        assertOptimizedEquals("bound_long in (2, 4, 3, 5)""false");
        assertOptimizedEquals("1234 in (2, bound_long, 3, 5)""true");
        assertOptimizedEquals("99 in (2, bound_long, 3, 5)""false");
        assertOptimizedEquals("bound_long in (2, bound_long, 3, 5)""true");
        assertOptimizedEquals("bound_string in ('bar', 'hello', 'foo', 'blah')""true");
        assertOptimizedEquals("bound_string in ('bar', 'baz', 'foo', 'blah')""false");
        assertOptimizedEquals("'hello' in ('bar', bound_string, 'foo', 'blah')""true");
        assertOptimizedEquals("'baz' in ('bar', bound_string, 'foo', 'blah')""false");
        assertOptimizedEquals("bound_long in (2, 1234, unbound_long, 5)""true");
        assertOptimizedEquals("bound_string in ('bar', 'hello', unbound_string, 'blah')""true");
        assertOptimizedEquals("bound_long in (2, 4, unbound_long, unbound_long2, 9)""1234 in (unbound_long, unbound_long2)");
        assertOptimizedEquals("unbound_long in (2, 4, bound_long, unbound_long2, 5)""unbound_long in (2, 4, 1234, unbound_long2, 5)");
    }
    @Test
    public void testCurrentTimestamp()
            throws Exception
    {
        double current = .getStartTime() / 1000.0;
        assertOptimizedEquals("current_timestamp = from_unixtime(" + current + ")""true");
        double future = current + ..toSeconds(1);
        assertOptimizedEquals("current_timestamp > from_unixtime(" + future + ")""false");
    }
    @Test
    public void testCastToString()
            throws Exception
    {
        // long
        assertOptimizedEquals("cast(123 as VARCHAR)""'123'");
        assertOptimizedEquals("cast(-123 as VARCHAR)""'-123'");
        // double
        assertOptimizedEquals("cast(123.0 as VARCHAR)""'123.0'");
        assertOptimizedEquals("cast(-123.0 as VARCHAR)""'-123.0'");
        assertOptimizedEquals("cast(123.456 as VARCHAR)""'123.456'");
        assertOptimizedEquals("cast(-123.456 as VARCHAR)""'-123.456'");
        // boolean
        assertOptimizedEquals("cast(true as VARCHAR)""'true'");
        assertOptimizedEquals("cast(false as VARCHAR)""'false'");
        // string
        assertOptimizedEquals("cast('xyz' as VARCHAR)""'xyz'");
        // null
        assertOptimizedEquals("cast(null as VARCHAR)""null");
    }
    @Test
    public void testCastToBoolean()
            throws Exception
    {
        // long
        assertOptimizedEquals("cast(123 as BOOLEAN)""true");
        assertOptimizedEquals("cast(-123 as BOOLEAN)""true");
        assertOptimizedEquals("cast(0 as BOOLEAN)""false");
        // boolean
        assertOptimizedEquals("cast(true as BOOLEAN)""true");
        assertOptimizedEquals("cast(false as BOOLEAN)""false");
        // string
        assertOptimizedEquals("cast('true' as BOOLEAN)""true");
        assertOptimizedEquals("cast('false' as BOOLEAN)""false");
        assertOptimizedEquals("cast('t' as BOOLEAN)""true");
        assertOptimizedEquals("cast('f' as BOOLEAN)""false");
        assertOptimizedEquals("cast('1' as BOOLEAN)""true");
        assertOptimizedEquals("cast('0' as BOOLEAN)""false");
        // null
        assertOptimizedEquals("cast(null as BOOLEAN)""null");
        // double
        assertOptimizedEquals("cast(123.45 as BOOLEAN)""true");
        assertOptimizedEquals("cast(-123.45 as BOOLEAN)""true");
        assertOptimizedEquals("cast(0.0 as BOOLEAN)""false");
    }
    @Test
    public void testCastToLong()
            throws Exception
    {
        // long
        assertOptimizedEquals("cast(0 as BIGINT)""0");
        assertOptimizedEquals("cast(123 as BIGINT)""123");
        assertOptimizedEquals("cast(-123 as BIGINT)""-123");
        // double
        assertOptimizedEquals("cast(123.0 as BIGINT)""123");
        assertOptimizedEquals("cast(-123.0 as BIGINT)""-123");
        assertOptimizedEquals("cast(123.456 as BIGINT)""123");
        assertOptimizedEquals("cast(-123.456 as BIGINT)""-123");
        // boolean
        assertOptimizedEquals("cast(true as BIGINT)""1");
        assertOptimizedEquals("cast(false as BIGINT)""0");
        // string
        assertOptimizedEquals("cast('123' as BIGINT)""123");
        assertOptimizedEquals("cast('-123' as BIGINT)""-123");
        // null
        assertOptimizedEquals("cast(null as BIGINT)""null");
    }
    @Test
    public void testCastToDouble()
            throws Exception
    {
        // long
        assertOptimizedEquals("cast(0 as DOUBLE)""0.0");
        assertOptimizedEquals("cast(123 as DOUBLE)""123.0");
        assertOptimizedEquals("cast(-123 as DOUBLE)""-123.0");
        // double
        assertOptimizedEquals("cast(123.0 as DOUBLE)""123.0");
        assertOptimizedEquals("cast(-123.0 as DOUBLE)""-123.0");
        assertOptimizedEquals("cast(123.456 as DOUBLE)""123.456");
        assertOptimizedEquals("cast(-123.456 as DOUBLE)""-123.456");
        // string
        assertOptimizedEquals("cast('0' as DOUBLE)""0.0");
        assertOptimizedEquals("cast('123' as DOUBLE)""123.0");
        assertOptimizedEquals("cast('-123' as DOUBLE)""-123.0");
        assertOptimizedEquals("cast('123.0' as DOUBLE)""123.0");
        assertOptimizedEquals("cast('-123.0' as DOUBLE)""-123.0");
        assertOptimizedEquals("cast('123.456' as DOUBLE)""123.456");
        assertOptimizedEquals("cast('-123.456' as DOUBLE)""-123.456");
        // null
        assertOptimizedEquals("cast(null as DOUBLE)""null");
        // boolean
        assertOptimizedEquals("cast(true as DOUBLE)""1.0");
        assertOptimizedEquals("cast(false as DOUBLE)""0.0");
    }
    @Test
    public void testCastOptimization()
            throws Exception
    {
        assertOptimizedEquals("cast(bound_long as VARCHAR)""'1234'");
        assertOptimizedEquals("cast(bound_long + 1 as VARCHAR)""'1235'");
        assertOptimizedEquals("cast(unbound_string as VARCHAR)""cast(unbound_string as VARCHAR)");
    }
    @Test
    public void testTryCast()
    {
        assertOptimizedEquals("try_cast(null as BIGINT)""null");
        assertOptimizedEquals("try_cast(123 as BIGINT)""123");
        assertOptimizedEquals("try_cast('foo' as VARCHAR)""'foo'");
        assertOptimizedEquals("try_cast('foo' as BIGINT)""null");
        assertOptimizedEquals("try_cast(unbound_string as BIGINT)""try_cast(unbound_string as BIGINT)");
    }
    @Test
    public void testReservedWithDoubleQuotes()
            throws Exception
    {
        assertOptimizedEquals("\"time\"""\"time\"");
    }
    @Test
    public void testSearchCase()
            throws Exception
    {
        assertOptimizedEquals("case " +
                "when true then 33 " +
                "end",
                "33");
        assertOptimizedEquals("case " +
                "when false then 1 " +
                "else 33 " +
                "end",
                "33");
        assertOptimizedEquals("case " +
                "when bound_long = 1234 then 33 " +
                "end",
                "33");
        assertOptimizedEquals("case " +
                "when true then bound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case " +
                "when false then 1 " +
                "else bound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case " +
                "when bound_long = 1234 then 33 " +
                "else unbound_long " +
                "end",
                "33");
        assertOptimizedEquals("case " +
                "when true then bound_long " +
                "else unbound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case " +
                "when false then unbound_long " +
                "else bound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case " +
                "when unbound_long = 1234 then 33 " +
                "else 1 " +
                "end",
                "" +
                        "case " +
                        "when unbound_long = 1234 then 33 " +
                        "else 1 " +
                        "end");
    }
    @Test
    public void testSimpleCase()
            throws Exception
    {
        assertOptimizedEquals("case true " +
                "when true then 33 " +
                "end",
                "33");
        assertOptimizedEquals("case true " +
                "when false then 1 " +
                "else 33 end",
                "33");
        assertOptimizedEquals("case bound_long " +
                "when 1234 then 33 " +
                "end",
                "33");
        assertOptimizedEquals("case 1234 " +
                "when bound_long then 33 " +
                "end",
                "33");
        assertOptimizedEquals("case true " +
                "when true then bound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case true " +
                "when false then 1 " +
                "else bound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case bound_long " +
                "when 1234 then 33 " +
                "else unbound_long " +
                "end",
                "33");
        assertOptimizedEquals("case true " +
                "when true then bound_long " +
                "else unbound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case true " +
                "when false then unbound_long " +
                "else bound_long " +
                "end",
                "1234");
        assertOptimizedEquals("case unbound_long " +
                "when 1234 then 33 " +
                "else 1 " +
                "end",
                "" +
                        "case unbound_long " +
                        "when 1234 then 33 " +
                        "else 1 " +
                        "end");
    }
    @Test
    public void testIf()
            throws Exception
    {
        assertOptimizedEquals("IF(2 = 2, 3, 4)""3");
        assertOptimizedEquals("IF(1 = 2, 3, 4)""4");
        assertOptimizedEquals("IF(true, 3, 4)""3");
        assertOptimizedEquals("IF(false, 3, 4)""4");
        assertOptimizedEquals("IF(null, 3, 4)""4");
        assertOptimizedEquals("IF(true, 3, null)""3");
        assertOptimizedEquals("IF(false, 3, null)""null");
        assertOptimizedEquals("IF(true, null, 4)""null");
        assertOptimizedEquals("IF(false, null, 4)""4");
        assertOptimizedEquals("IF(true, null, null)""null");
        assertOptimizedEquals("IF(false, null, null)""null");
        assertOptimizedEquals("IF(true, 3.5, 4.2)""3.5");
        assertOptimizedEquals("IF(false, 3.5, 4.2)""4.2");
        assertOptimizedEquals("IF(true, 'foo', 'bar')""'foo'");
        assertOptimizedEquals("IF(false, 'foo', 'bar')""'bar'");
        // todo optimize case statement
        assertOptimizedEquals("IF(unbound_boolean, 1 + 2, 3 + 4)""CASE WHEN unbound_boolean THEN (1 + 2) ELSE (3 + 4) END");
    }
    @Test
    public void testLike()
            throws Exception
    {
        assertOptimizedEquals("'a' LIKE 'a'""true");
        assertOptimizedEquals("'' LIKE 'a'""false");
        assertOptimizedEquals("'abc' LIKE 'a'""false");
        assertOptimizedEquals("'a' LIKE '_'""true");
        assertOptimizedEquals("'' LIKE '_'""false");
        assertOptimizedEquals("'abc' LIKE '_'""false");
        assertOptimizedEquals("'a' LIKE '%'""true");
        assertOptimizedEquals("'' LIKE '%'""true");
        assertOptimizedEquals("'abc' LIKE '%'""true");
        assertOptimizedEquals("'abc' LIKE '___'""true");
        assertOptimizedEquals("'ab' LIKE '___'""false");
        assertOptimizedEquals("'abcd' LIKE '___'""false");
        assertOptimizedEquals("'abc' LIKE 'abc'""true");
        assertOptimizedEquals("'xyz' LIKE 'abc'""false");
        assertOptimizedEquals("'abc0' LIKE 'abc'""false");
        assertOptimizedEquals("'0abc' LIKE 'abc'""false");
        assertOptimizedEquals("'abc' LIKE 'abc%'""true");
        assertOptimizedEquals("'abc0' LIKE 'abc%'""true");
        assertOptimizedEquals("'0abc' LIKE 'abc%'""false");
        assertOptimizedEquals("'abc' LIKE '%abc'""true");
        assertOptimizedEquals("'0abc' LIKE '%abc'""true");
        assertOptimizedEquals("'abc0' LIKE '%abc'""false");
        assertOptimizedEquals("'abc' LIKE '%abc%'""true");
        assertOptimizedEquals("'0abc' LIKE '%abc%'""true");
        assertOptimizedEquals("'abc0' LIKE '%abc%'""true");
        assertOptimizedEquals("'0abc0' LIKE '%abc%'""true");
        assertOptimizedEquals("'xyzw' LIKE '%abc%'""false");
        assertOptimizedEquals("'abc' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'0abc' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'abc0' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'0abc0' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'ab01c' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'0ab01c' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'ab01c0' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'0ab01c0' LIKE '%ab%c%'""true");
        assertOptimizedEquals("'xyzw' LIKE '%ab%c%'""false");
        // ensure regex chars are escaped
        assertOptimizedEquals("'\' LIKE '\'""true");
        assertOptimizedEquals("'.*' LIKE '.*'""true");
        assertOptimizedEquals("'[' LIKE '['""true");
        assertOptimizedEquals("']' LIKE ']'""true");
        assertOptimizedEquals("'{' LIKE '{'""true");
        assertOptimizedEquals("'}' LIKE '}'""true");
        assertOptimizedEquals("'?' LIKE '?'""true");
        assertOptimizedEquals("'+' LIKE '+'""true");
        assertOptimizedEquals("'(' LIKE '('""true");
        assertOptimizedEquals("')' LIKE ')'""true");
        assertOptimizedEquals("'|' LIKE '|'""true");
        assertOptimizedEquals("'^' LIKE '^'""true");
        assertOptimizedEquals("'$' LIKE '$'""true");
        assertOptimizedEquals("null like '%'""null");
        assertOptimizedEquals("'a' like null""null");
        assertOptimizedEquals("'a' like '%' escape null""null");
        assertOptimizedEquals("'%' like 'z%' escape 'z'""true");
    }
    @Test
    public void testLikeOptimization()
            throws Exception
    {
        assertOptimizedEquals("unbound_string like 'abc'""unbound_string = 'abc'");
        assertOptimizedEquals("bound_string like bound_pattern""true");
        assertOptimizedEquals("'abc' like bound_pattern""false");
        assertOptimizedEquals("unbound_string like bound_pattern""unbound_string like bound_pattern");
        assertOptimizedEquals("unbound_string like unbound_pattern escape unbound_string""unbound_string like unbound_pattern escape unbound_string");
    }
    @Test
    public void testFailedExpressionOptimization()
            throws Exception
    {
        assertOptimizedEquals("if(unbound_boolean, 1, 0 / 0)""CASE WHEN unbound_boolean THEN 1 ELSE 0 / 0 END");
        assertOptimizedEquals("if(unbound_boolean, 0 / 0, 1)""CASE WHEN unbound_boolean THEN 0 / 0 ELSE 1 END");
        assertOptimizedEqualsSelf("case unbound_long when 1 then 1 when 0 / 0 then 2 end");
        assertOptimizedEqualsSelf("case unbound_boolean when true then 1 else 0 / 0 end");
        assertOptimizedEqualsSelf("case unbound_boolean when true then 0 / 0 else 1 end");
        assertOptimizedEqualsSelf("case when unbound_boolean then 1 when 0 / 0 = 0 then 2 end");
        assertOptimizedEqualsSelf("case when unbound_boolean then 1 else 0 / 0  end");
        assertOptimizedEqualsSelf("case when unbound_boolean then 0 / 0 else 1 end");
        assertOptimizedEqualsSelf("coalesce(unbound_boolean, 0 / 0 = 0)");
    }
    @Test(expectedExceptions = PrestoException.class)
    public void testOptimizeDivideByZero()
            throws Exception
    {
        optimize("0 / 0");
    }
    @Test(expectedExceptions = PrestoException.class)
            throws Exception
    {
        optimize("if(false, 1, 0 / 0)");
    }
    @Test(expectedExceptions = PrestoException.class)
            throws Exception
    {
        optimize("case when 0 / 0 = 0 then 1 end");
    }
    @Test(timeOut = 60000)
    public void testLikeInvalidUtf8()
    {
        assertLike(new byte[] {'a''b''c'}, "%b%"true);
        assertLike(new byte[] {'a''b''c', (byte) 0xFF, 'x''y'}, "%b%"true);
    }
    @Test
    public void testLiterals()
    {
        optimize("date '2013-04-03' + unbound_interval");
        optimize("time '03:04:05.321' + unbound_interval");
        optimize("time '03:04:05.321 UTC' + unbound_interval");
        optimize("timestamp '2013-04-03 03:04:05.321' + unbound_interval");
        optimize("timestamp '2013-04-03 03:04:05.321 UTC' + unbound_interval");
        optimize("interval '3' day * unbound_long");
        optimize("interval '3' year * unbound_long");
    }
    private static void assertLike(byte[] valueString patternboolean expected)
    {
        Expression predicate = new LikePredicate(
                rawStringLiteral(Slices.wrappedBuffer(value)),
                new StringLiteral(pattern),
                null);
        assertEquals(evaluate(predicate), expected);
    }
    private static StringLiteral rawStringLiteral(final Slice slice)
    {
        return new StringLiteral(slice.toString())
        {
            @Override
            public Slice getSlice()
            {
                return slice;
            }
        };
    }
    private static void assertOptimizedEquals(@Language("SQL"String actual, @Language("SQL"String expected)
    {
        assertEquals(optimize(actual), optimize(expected));
    }
    private static void assertOptimizedEqualsSelf(@Language("SQL"String expression)
    {
        assertEquals(optimize(expression), .createExpression(expression));
    }
    private static Object optimize(@Language("SQL"String expression)
    {
        assertRoundTrip(expression);
        Expression parsedExpression = FunctionAssertions.createExpression(expression);
        IdentityHashMap<ExpressionTypeexpressionTypes = getExpressionTypes(parsedExpression);
        ExpressionInterpreter interpreter = expressionOptimizer(parsedExpressionexpressionTypes);
        return interpreter.optimize(new SymbolResolver()
        {
            @Override
            public Object getValue(Symbol symbol)
            {
                switch (symbol.getName().toLowerCase()) {
                    case "bound_long":
                        return 1234L;
                    case "bound_string":
                        return Slices.wrappedBuffer("hello".getBytes());
                    case "bound_double":
                        return 12.34;
                    case "bound_date":
                        return new LocalDate(2001, 8, 22).toDateMidnight(.).getMillis();
                    case "bound_time":
                        return new LocalTime(3, 4, 5, 321).toDateTime(new DateTime(0, .)).getMillis();
                    case "bound_timestamp":
                        return new DateTime(2001, 8, 22, 3, 4, 5, 321, .).getMillis();
                    case "bound_pattern":
                        return Slices.wrappedBuffer("%el%".getBytes());
                }
                return new QualifiedNameReference(symbol.toQualifiedName());
            }
        });
    }
    private static Object evaluate(String expression)
    {
        assertRoundTrip(expression);
        Expression parsedExpression = FunctionAssertions.createExpression(expression);
        return evaluate(parsedExpression);
    }
    private static void assertRoundTrip(String expression)
    {
        assertEquals(.createExpression(expression),
                .createExpression(formatExpression(.createExpression(expression))));
    }
    private static Object evaluate(Expression expression)
    {
        IdentityHashMap<ExpressionTypeexpressionTypes = getExpressionTypes(expression);
        ExpressionInterpreter interpreter = expressionInterpreter(expressionexpressionTypes);
        return interpreter.evaluate((RecordCursornull);
    }
New to GrepCode? Check out our FAQ X