cassandra-trunk/test/unit/org/apache/cassandra/cql3/functions/CastFctsTest.java
/*
 * 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.cassandra.cql3.functions;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.util.Date;

import org.apache.cassandra.cql3.CQLTester;
import org.apache.cassandra.serializers.SimpleDateSerializer;
import org.apache.cassandra.service.StorageService;
import org.apache.cassandra.utils.TimeUUID;

import org.junit.Test;

public class CastFctsTest extends CQLTester
{
    @Test
    public void testInvalidQueries() throws Throwable
    {
        createTable("CREATE TABLE %s (a int primary key, b text, c double)");

        assertInvalidMessage("a cannot be cast to boolean", "SELECT CAST(a AS boolean) FROM %s");
    }

    @Test
    public void testNumericCastsInSelectionClause() throws Throwable
    {
        createTable("CREATE TABLE %s (a tinyint primary key,"
                                   + " b smallint,"
                                   + " c int,"
                                   + " d bigint,"
                                   + " e float,"
                                   + " f double,"
                                   + " g decimal,"
                                   + " h varint,"
                                   + " i int)");

        execute("INSERT INTO %s (a, b, c, d, e, f, g, h) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                (byte) 1, (short) 2, 3, 4L, 5.2F, 6.3, BigDecimal.valueOf(6.3), BigInteger.valueOf(4));

        assertColumnNames(execute("SELECT CAST(b AS int), CAST(c AS int), CAST(d AS double) FROM %s"),
                          "cast(b as int)",
                          "c",
                          "cast(d as double)");

        assertRows(execute("SELECT CAST(a AS tinyint), " +
                "CAST(b AS tinyint), " +
                "CAST(c AS tinyint), " +
                "CAST(d AS tinyint), " +
                "CAST(e AS tinyint), " +
                "CAST(f AS tinyint), " +
                "CAST(g AS tinyint), " +
                "CAST(h AS tinyint), " +
                "CAST(i AS tinyint) FROM %s"),
                   row((byte) 1, (byte) 2, (byte) 3, (byte) 4L, (byte) 5, (byte) 6, (byte) 6, (byte) 4, null));

        assertRows(execute("SELECT CAST(a AS smallint), " +
                "CAST(b AS smallint), " +
                "CAST(c AS smallint), " +
                "CAST(d AS smallint), " +
                "CAST(e AS smallint), " +
                "CAST(f AS smallint), " +
                "CAST(g AS smallint), " +
                "CAST(h AS smallint), " +
                "CAST(i AS smallint) FROM %s"),
                   row((short) 1, (short) 2, (short) 3, (short) 4L, (short) 5, (short) 6, (short) 6, (short) 4, null));

        assertRows(execute("SELECT CAST(a AS int), " +
                "CAST(b AS int), " +
                "CAST(c AS int), " +
                "CAST(d AS int), " +
                "CAST(e AS int), " +
                "CAST(f AS int), " +
                "CAST(g AS int), " +
                "CAST(h AS int), " +
                "CAST(i AS int) FROM %s"),
                   row(1, 2, 3, 4, 5, 6, 6, 4, null));

        assertRows(execute("SELECT CAST(a AS bigint), " +
                "CAST(b AS bigint), " +
                "CAST(c AS bigint), " +
                "CAST(d AS bigint), " +
                "CAST(e AS bigint), " +
                "CAST(f AS bigint), " +
                "CAST(g AS bigint), " +
                "CAST(h AS bigint), " +
                "CAST(i AS bigint) FROM %s"),
                   row(1L, 2L, 3L, 4L, 5L, 6L, 6L, 4L, null));

        assertRows(execute("SELECT CAST(a AS float), " +
                "CAST(b AS float), " +
                "CAST(c AS float), " +
                "CAST(d AS float), " +
                "CAST(e AS float), " +
                "CAST(f AS float), " +
                "CAST(g AS float), " +
                "CAST(h AS float), " +
                "CAST(i AS float) FROM %s"),
                   row(1.0F, 2.0F, 3.0F, 4.0F, 5.2F, 6.3F, 6.3F, 4.0F, null));

        assertRows(execute("SELECT CAST(a AS double), " +
                "CAST(b AS double), " +
                "CAST(c AS double), " +
                "CAST(d AS double), " +
                "CAST(e AS double), " +
                "CAST(f AS double), " +
                "CAST(g AS double), " +
                "CAST(h AS double), " +
                "CAST(i AS double) FROM %s"),
                   row(1.0, 2.0, 3.0, 4.0, (double) 5.2F, 6.3, 6.3, 4.0, null));

        assertRows(execute("SELECT CAST(a AS decimal), " +
                "CAST(b AS decimal), " +
                "CAST(c AS decimal), " +
                "CAST(d AS decimal), " +
                "CAST(e AS decimal), " +
                "CAST(f AS decimal), " +
                "CAST(g AS decimal), " +
                "CAST(h AS decimal), " +
                "CAST(i AS decimal) FROM %s"),
                   row(BigDecimal.valueOf(1),
                       BigDecimal.valueOf(2),
                       BigDecimal.valueOf(3),
                       BigDecimal.valueOf(4),
                       new BigDecimal("5.2"),
                       BigDecimal.valueOf(6.3),
                       BigDecimal.valueOf(6.3),
                       BigDecimal.valueOf(4),
                       null));

        assertRows(execute("SELECT CAST(a AS ascii), " +
                "CAST(b AS ascii), " +
                "CAST(c AS ascii), " +
                "CAST(d AS ascii), " +
                "CAST(e AS ascii), " +
                "CAST(f AS ascii), " +
                "CAST(g AS ascii), " +
                "CAST(h AS ascii), " +
                "CAST(i AS ascii) FROM %s"),
                   row("1",
                       "2",
                       "3",
                       "4",
                       "5.2",
                       "6.3",
                       "6.3",
                       "4",
                       null));

        assertRows(execute("SELECT CAST(a AS text), " +
                "CAST(b AS text), " +
                "CAST(c AS text), " +
                "CAST(d AS text), " +
                "CAST(e AS text), " +
                "CAST(f AS text), " +
                "CAST(g AS text), " +
                "CAST(h AS text), " +
                "CAST(i AS text) FROM %s"),
                   row("1",
                       "2",
                       "3",
                       "4",
                       "5.2",
                       "6.3",
                       "6.3",
                       "4",
                       null));
    }

    @Test
    public void testNoLossOfPrecisionForCastToDecimal() throws Throwable
    {
        createTable("CREATE TABLE %s (k int PRIMARY KEY, bigint_clmn bigint, varint_clmn varint)");
        execute("INSERT INTO %s(k, bigint_clmn, varint_clmn) VALUES(2, 9223372036854775807, 1234567890123456789)");

        assertRows(execute("SELECT CAST(bigint_clmn AS decimal), CAST(varint_clmn AS decimal) FROM %s"),
                   row(BigDecimal.valueOf(9223372036854775807L), BigDecimal.valueOf(1234567890123456789L)));
    }

    @Test
    public void testTimeCastsInSelectionClause() throws Throwable
    {
        createTable("CREATE TABLE %s (a timeuuid primary key, b timestamp, c date, d time)");

        final String yearMonthDay = "2015-05-21";
        final LocalDate localDate = LocalDate.of(2015, 5, 21);
        ZonedDateTime date = localDate.atStartOfDay(ZoneOffset.UTC);

        ZonedDateTime dateTime = ZonedDateTime.of(localDate, LocalTime.of(11,3,2), ZoneOffset.UTC);

        long timeInMillis = dateTime.toInstant().toEpochMilli();

        execute("INSERT INTO %s (a, b, c, d) VALUES (?, '" + yearMonthDay + " 11:03:02+00', '2015-05-21', '11:03:02')",
                TimeUUID.Generator.atUnixMillis(timeInMillis));

        assertRows(execute("SELECT CAST(a AS timestamp), " +
                           "CAST(b AS timestamp), " +
                           "CAST(c AS timestamp) FROM %s"),
                   row(Date.from(dateTime.toInstant()), Date.from(dateTime.toInstant()), Date.from(date.toInstant())));

        int timeInMillisToDay = SimpleDateSerializer.timeInMillisToDay(date.toInstant().toEpochMilli());
        assertRows(execute("SELECT CAST(a AS date), " +
                           "CAST(b AS date), " +
                           "CAST(c AS date) FROM %s"),
                   row(timeInMillisToDay, timeInMillisToDay, timeInMillisToDay));

        assertRows(execute("SELECT CAST(b AS text), " +
                           "CAST(c AS text), " +
                           "CAST(d AS text) FROM %s"),
                   row(yearMonthDay + "T11:03:02.000Z", yearMonthDay, "11:03:02.000000000"));
    }

    @Test
    public void testOtherTypeCastsInSelectionClause() throws Throwable
    {
        createTable("CREATE TABLE %s (a ascii primary key,"
                                   + " b inet,"
                                   + " c boolean)");

        execute("INSERT INTO %s (a, b, c) VALUES (?, '127.0.0.1', ?)",
                "test", true);

        assertRows(execute("SELECT CAST(a AS text), " +
                "CAST(b AS text), " +
                "CAST(c AS text) FROM %s"),
                   row("test", "127.0.0.1", "true"));
    }

    @Test
    public void testCastsWithReverseOrder() throws Throwable
    {
        createTable("CREATE TABLE %s (a int,"
                                   + " b smallint,"
                                   + " c double,"
                                   + " primary key (a, b)) WITH CLUSTERING ORDER BY (b DESC);");

        execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)",
                1, (short) 2, 6.3);

        assertRows(execute("SELECT CAST(a AS tinyint), " +
                "CAST(b AS tinyint), " +
                "CAST(c AS tinyint) FROM %s"),
                   row((byte) 1, (byte) 2, (byte) 6));

        assertRows(execute("SELECT CAST(CAST(a AS tinyint) AS smallint), " +
                "CAST(CAST(b AS tinyint) AS smallint), " +
                "CAST(CAST(c AS tinyint) AS smallint) FROM %s"),
                   row((short) 1, (short) 2, (short) 6));

        assertRows(execute("SELECT CAST(CAST(CAST(a AS tinyint) AS double) AS text), " +
                "CAST(CAST(CAST(b AS tinyint) AS double) AS text), " +
                "CAST(CAST(CAST(c AS tinyint) AS double) AS text) FROM %s"),
                   row("1.0", "2.0", "6.0"));

        String f = createFunction(KEYSPACE, "int",
                                  "CREATE FUNCTION %s(val int) " +
                                          "RETURNS NULL ON NULL INPUT " +
                                          "RETURNS double " +
                                          "LANGUAGE java " +
                                          "AS 'return (double)val;'");

        assertRows(execute("SELECT " + f + "(CAST(b AS int)) FROM %s"),
                   row((double) 2));

        assertRows(execute("SELECT CAST(" + f + "(CAST(b AS int)) AS text) FROM %s"),
                   row("2.0"));
    }

    @Test
    public void testCounterCastsInSelectionClause() throws Throwable
    {
        createTable("CREATE TABLE %s (a int primary key, b counter)");

        execute("UPDATE %s SET b = b + 2 WHERE a = 1");

        assertRows(execute("SELECT CAST(b AS tinyint), " +
                "CAST(b AS smallint), " +
                "CAST(b AS int), " +
                "CAST(b AS bigint), " +
                "CAST(b AS float), " +
                "CAST(b AS double), " +
                "CAST(b AS decimal), " +
                "CAST(b AS ascii), " +
                "CAST(b AS text) FROM %s"),
                   row((byte) 2, (short) 2, 2, 2L, 2.0F, 2.0, BigDecimal.valueOf(2), "2", "2"));
    }

    /**
     * Verifies that the {@code CAST} function can be used in the values of {@code INSERT INTO} statements.
     */
    @Test
    public void testCastsInInsertIntoValues() throws Throwable
    {
        createTable("CREATE TABLE %s (k int PRIMARY KEY, v int)");

        // Simple cast
        execute("INSERT INTO %s (k, v) VALUES (1, CAST(1.3 AS int))");
        assertRows(execute("SELECT v FROM %s"), row(1));

        // Nested casts
        execute("INSERT INTO %s (k, v) VALUES (1, CAST(CAST(CAST(2.3 AS int) AS float) AS int))");
        assertRows(execute("SELECT v FROM %s"), row(2));

        // Cast of placeholder with type hint
        execute("INSERT INTO %s (k, v) VALUES (1, CAST((float) ? AS int))", 3.4f);
        assertRows(execute("SELECT v FROM %s"), row(3));

        // Cast of placeholder without type hint
        assertInvalidRequestMessage("Ambiguous call to function system.cast_as_int",
                                    "INSERT INTO %s (k, v) VALUES (1, CAST(? AS int))", 3.4f);

        // Type hint of cast
        execute("INSERT INTO %s (k, v) VALUES (1, (int) CAST(4.9 AS int))");
        assertRows(execute("SELECT v FROM %s"), row(4));

        // Function of cast
        execute(String.format("INSERT INTO %%s (k, v) VALUES (1, %s(CAST(5 AS float)))", floatToInt()));
        assertRows(execute("SELECT v FROM %s"), row(5));

        // Cast of function
        execute(String.format("INSERT INTO %%s (k, v) VALUES (1, CAST(%s(6) AS int))", intToFloat()));
        assertRows(execute("SELECT v FROM %s"), row(6));
    }

    /**
     * Verifies that the {@code CAST} function can be used in the values of {@code UPDATE} statements.
     */
    @Test
    public void testCastsInUpdateValues() throws Throwable
    {
        createTable("CREATE TABLE %s (k int PRIMARY KEY, v int)");

        // Simple cast
        execute("UPDATE %s SET v = CAST(1.3 AS int) WHERE k = 1");
        assertRows(execute("SELECT v FROM %s"), row(1));

        // Nested casts
        execute("UPDATE %s SET v = CAST(CAST(CAST(2.3 AS int) AS float) AS int) WHERE k = 1");
        assertRows(execute("SELECT v FROM %s"), row(2));

        // Cast of placeholder with type hint
        execute("UPDATE %s SET v = CAST((float) ? AS int) WHERE k = 1", 3.4f);
        assertRows(execute("SELECT v FROM %s"), row(3));

        // Cast of placeholder without type hint
        assertInvalidRequestMessage("Ambiguous call to function system.cast_as_int",
                                    "UPDATE %s SET v = CAST(? AS int) WHERE k = 1", 3.4f);

        // Type hint of cast
        execute("UPDATE %s SET v = (int) CAST(4.9 AS int) WHERE k = 1");
        assertRows(execute("SELECT v FROM %s"), row(4));

        // Function of cast
        execute(String.format("UPDATE %%s SET v = %s(CAST(5 AS float)) WHERE k = 1", floatToInt()));
        assertRows(execute("SELECT v FROM %s"), row(5));

        // Cast of function
        execute(String.format("UPDATE %%s SET v = CAST(%s(6) AS int) WHERE k = 1", intToFloat()));
        assertRows(execute("SELECT v FROM %s"), row(6));
    }

    /**
     * Verifies that the {@code CAST} function can be used in the {@code WHERE} clause of {@code UPDATE} statements.
     */
    @Test
    public void testCastsInUpdateWhereClause() throws Throwable
    {
        createTable("CREATE TABLE %s (k int PRIMARY KEY, v int)");

        for (int i = 1; i <= 6; i++)
        {
            execute("INSERT INTO %s (k) VALUES (?)", i);
        }

        // Simple cast
        execute("UPDATE %s SET v = ? WHERE k = CAST(1.3 AS int)", 1);
        assertRows(execute("SELECT v FROM %s WHERE k = ?", 1), row(1));

        // Nested casts
        execute("UPDATE %s SET v = ? WHERE k = CAST(CAST(CAST(2.3 AS int) AS float) AS int)", 2);
        assertRows(execute("SELECT v FROM %s WHERE k = ?", 2), row(2));

        // Cast of placeholder with type hint
        execute("UPDATE %s SET v = ? WHERE k = CAST((float) ? AS int)", 3, 3.4f);
        assertRows(execute("SELECT v FROM %s WHERE k = ?", 3), row(3));

        // Cast of placeholder without type hint
        assertInvalidRequestMessage("Ambiguous call to function system.cast_as_int",
                                    "UPDATE %s SET v = ? WHERE k = CAST(? AS int)", 3, 3.4f);

        // Type hint of cast
        execute("UPDATE %s SET v = ? WHERE k = (int) CAST(4.9 AS int)", 4);
        assertRows(execute("SELECT v FROM %s WHERE k = ?", 4), row(4));

        // Function of cast
        execute(String.format("UPDATE %%s SET v = ? WHERE k = %s(CAST(5 AS float))", floatToInt()), 5);
        assertRows(execute("SELECT v FROM %s WHERE k = ?", 5), row(5));

        // Cast of function
        execute(String.format("UPDATE %%s SET v = ? WHERE k = CAST(%s(6) AS int)", intToFloat()), 6);
        assertRows(execute("SELECT v FROM %s WHERE k = ?", 6), row(6));
    }

    /**
     * Verifies that the {@code CAST} function can be used in the {@code WHERE} clause of {@code SELECT} statements.
     */
    @Test
    public void testCastsInSelectWhereClause() throws Throwable
    {
        createTable("CREATE TABLE %s (k int PRIMARY KEY)");

        for (int i = 1; i <= 6; i++)
        {
            execute("INSERT INTO %s (k) VALUES (?)", i);
        }

        // Simple cast
        assertRows(execute("SELECT k FROM %s WHERE k = CAST(1.3 AS int)"), row(1));

        // Nested casts
        assertRows(execute("SELECT k FROM %s WHERE k = CAST(CAST(CAST(2.3 AS int) AS float) AS int)"), row(2));

        // Cast of placeholder with type hint
        assertRows(execute("SELECT k FROM %s WHERE k = CAST((float) ? AS int)", 3.4f), row(3));

        // Cast of placeholder without type hint
        assertInvalidRequestMessage("Ambiguous call to function system.cast_as_int",
                                    "SELECT k FROM %s WHERE k = CAST(? AS int)", 3.4f);

        // Type hint of cast
        assertRows(execute("SELECT k FROM %s WHERE k = (int) CAST(4.9 AS int)"), row(4));

        // Function of cast
        assertRows(execute(String.format("SELECT k FROM %%s WHERE k = %s(CAST(5 AS float))", floatToInt())), row(5));

        // Cast of function
        assertRows(execute(String.format("SELECT k FROM %%s WHERE k = CAST(%s(6) AS int)", intToFloat())), row(6));
    }

    /**
     * Verifies that the {@code CAST} function can be used in the {@code WHERE} clause of {@code DELETE} statements.
     */
    @Test
    public void testCastsInDeleteWhereClause() throws Throwable
    {
        createTable("CREATE TABLE %s (k int PRIMARY KEY)");

        for (int i = 1; i <= 6; i++)
        {
            execute("INSERT INTO %s (k) VALUES (?)", i);
        }

        // Simple cast
        execute("DELETE FROM %s WHERE k = CAST(1.3 AS int)");
        assertEmpty(execute("SELECT * FROM %s WHERE k = ?", 1));

        // Nested casts
        execute("DELETE FROM %s WHERE k = CAST(CAST(CAST(2.3 AS int) AS float) AS int)");
        assertEmpty(execute("SELECT * FROM %s WHERE k = ?", 2));

        // Cast of placeholder with type hint
        execute("DELETE FROM %s WHERE k = CAST((float) ? AS int)", 3.4f);
        assertEmpty(execute("SELECT * FROM %s WHERE k = ?", 3));

        // Cast of placeholder without type hint
        assertInvalidRequestMessage("Ambiguous call to function system.cast_as_int",
                                    "DELETE FROM %s WHERE k = CAST(? AS int)", 3.4f);

        // Type hint of cast
        execute("DELETE FROM %s WHERE k = (int) CAST(4.9 AS int)");
        assertEmpty(execute("SELECT * FROM %s WHERE k = ?", 4));

        // Function of cast
        execute(String.format("DELETE FROM %%s WHERE k = %s(CAST(5 AS float))", floatToInt()));
        assertEmpty(execute("SELECT * FROM %s WHERE k = ?", 5));

        // Cast of function
        execute(String.format("DELETE FROM %%s WHERE k = CAST(%s(6) AS int)", intToFloat()));
        assertEmpty(execute("SELECT * FROM %s WHERE k = ?", 6));
    }

    /**
     * Creates a CQL function that casts an {@code int} argument into a {@code float}.
     *
     * @return the name of the created function
     */
    private String floatToInt() throws Throwable
    {
        return createFunction(KEYSPACE,
                              "int, int",
                              "CREATE FUNCTION IF NOT EXISTS %s (x float) " +
                              "CALLED ON NULL INPUT " +
                              "RETURNS int " +
                              "LANGUAGE java " +
                              "AS 'return Float.valueOf(x).intValue();'");
    }

    /**
     * Creates a CQL function that casts a {@code float} argument into an {@code int}.
     *
     * @return the name of the created function
     */
    private String intToFloat() throws Throwable
    {
        return createFunction(KEYSPACE,
                              "int, int",
                              "CREATE FUNCTION IF NOT EXISTS %s (x int) " +
                              "CALLED ON NULL INPUT " +
                              "RETURNS float " +
                              "LANGUAGE java " +
                              "AS 'return (float) x;'");
    }

    /**
     * Verifies that the {@code CAST} function can be used in the {@code WHERE} clause of {@code CREATE MATERIALIZED
     * VIEW} statements.
     */
    @Test
    public void testCastsInCreateViewWhereClause() throws Throwable
    {
        createTable("CREATE TABLE %s (k int PRIMARY KEY, v int)");

        String viewName = keyspace() + ".mv_with_cast";
        execute(String.format("CREATE MATERIALIZED VIEW %s AS SELECT * FROM %%s" +
                              "   WHERE k < CAST(3.14 AS int) AND v IS NOT NULL" +
                              "   PRIMARY KEY (v, k)", viewName));

        // start storage service so MV writes are applied
        StorageService.instance.initServer();

        execute("INSERT INTO %s (k, v) VALUES (1, 10)");
        execute("INSERT INTO %s (k, v) VALUES (2, 20)");
        execute("INSERT INTO %s (k, v) VALUES (3, 30)");

        assertRows(execute(String.format("SELECT * FROM %s", viewName)), row(10, 1), row(20, 2));

        execute("DROP MATERIALIZED VIEW " + viewName);
    }
}
