[jira] [Created] (IGNITE-19503) Sql. UUID. Some expressions and statements with types from incompatible families are not rejected.

[Maksim Zhuravkov (Jira)]

Maksim Zhuravkov created IGNITE-19503:
-----------------------------------------

             Summary: Sql. UUID. Some expressions and statements with types 
from incompatible families are not rejected.
                 Key: IGNITE-19503
                 URL: https://issues.apache.org/jira/browse/IGNITE-19503
             Project: Ignite
          Issue Type: Bug
          Components: sql
    Affects Versions: 3.0.0-beta1
            Reporter: Maksim Zhuravkov


Type compatibility checks for UUID in some expression/statement are not 
performed at validation stage:

INTEGER vs UUID:

{code:java}
 @Test
    public void testInsert() {
        sql("CREATE TABLE t1 (key INTEGER PRIMARY KEY , int_col INTEGER)");
        // /  class java.util.UUID cannot be cast to class java.lang.Integer
        sql("INSERT INTO t1 VALUES(1, 
'46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID)");
    }

    @Test
    public void testUpdate() {

        sql("CREATE TABLE t1 (key INTEGER PRIMARY KEY , int_col INTEGER)");
        sql("INSERT INTO t1 VALUES(1, 1)");
        //  class java.util.UUID cannot be cast to class java.lang.Integer
        sql("UPDATE t1 SET int_col 
='46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID");
    }
{code}


INTEGER vs VARBINARY 

{code:java}
@Test
    public void testInsert2() {
        sql("CREATE TABLE t1 (key INTEGER PRIMARY KEY , int_col INTEGER)");
        // Column 33: Cannot assign to target field 'INT_COL' of type INTEGER 
from source field 'EXPR$0' of type BINARY(3)
        sql("INSERT INTO t1 VALUES(1, x'010203')");
    }

    @Test
    public void testUpdate2() {

        sql("CREATE TABLE t1 (key INTEGER PRIMARY KEY , int_col INTEGER)");
//        sql("INSERT INTO t1 VALUES(1, 1)");
        // olumn 33: Cannot assign to target field 'INT_COL' of type INTEGER 
from source field 'EXPR$0' of type BINARY(3)
        sql("UPDATE t1 SET int_col = x'010203'");
    }
{code}

Expressions:

{code:java}
@Test
    public void testExprs() {
        //sql("SELECT 1 in ('46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID)");

        // Binary comparison operations are rejected by validator: OK
        // From line 1, column 1 to line 1, column 55: Invalid types for 
comparison: INTEGER NOT NULL = UUID NOT NULL
        sql("SELECT 1 = '46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID");

        // CASE: OK
        // Illegal mixing of types in CASE or COALESCE statement
        sql("SELECT CASE int_col WHEN 1 THEN 
'46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID ELSE '2' END FROM t1");

        // Arithmetic
        /*
        java.lang.IllegalArgumentException: Cannot infer return type for +; 
operand types: [INTEGER, UUID]

        at 
org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:541)
        at 
org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:504)
        at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:605)
        at 
org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
         */
        sql("SELECT 1 + '46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID");

        // Runtime: Cannot convert 46138242-b771-4d8b-ad26-2b3fcee5f11d to int
        sql("SELECT 1 IN ('46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID)");

        // Line 8, Column 281: Cannot cast "java.util.UUID" to 
"org.apache.calcite.avatica.util.ByteString"
        //Caused by: org.codehaus.commons.compiler.CompileException: Line 8, 
Column 281: Cannot cast "java.util.UUID" to 
"org.apache.calcite.avatica.util.ByteString"
        //at 
org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:13014)
        sql("SELECT x'010203' IN 
('46138242-b771-4d8b-ad26-2b3fcee5f11d'::UUID)");
    }
{code}

This happens because ANY type is used as a type family for UUID/a custom data 
type.
This can be fixed for all custom data types by adding a special checks for ANY 
type.
When fixing INSERT statement we should remember that calcite's DEFAULT 
expression has ANY type as well.





--
This message was sent by Atlassian Jira
(v8.20.10#820010)