Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28963

Incompatible data type assignment through SP vars is not consistent with columns

    XMLWordPrintable

Details

    Description

      The patch MDEV-28918 disallowed incompatible data type assignment for columns of the GEOMETRY, INET6, UUID types when they are mixed with numeric or temporal values (and the other way around), so such wrong mixes get detected at the statement preparation time:

      CREATE OR REPLACE TABLE t1 (a GEOMETRY);
      INSERT INTO t1 VALUES (1);
      

      ERROR 4078 (HY000): Illegal parameter data types geometry and int for operation 'SET'
      

      CREATE OR REPLACE TABLE t1 (a UUID);
      INSERT INTO t1 VALUES (1);
      

      ERROR 4078 (HY000): Illegal parameter data types uuid and int for operation 'SET'
      

      CREATE OR REPLACE TABLE t1 (a INET6);
      INSERT INTO t1 VALUES (1);
      

      ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation 'SET'
      

      But wrong data type assignment is still possible through SP. Wrong types mixes do not detected at the preparation time:

      CREATE OR REPLACE FUNCTION f1(a GEOMETRY) RETURNS INT RETURN NULL;
      SELECT f1(1);
      

      ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field
      

      CREATE OR REPLACE PROCEDURE p1(a GEOMETRY) BEGIN END;
      CALL p1(1);
      

      ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field
      

      CREATE OR REPLACE FUNCTION f1(a UUID) RETURNS INT RETURN NULL;
      SELECT f1(1);
      

      ERROR 1292 (22007): Incorrect uuid value: '1' for column ``.``.`a` at row 1
      

      CREATE OR REPLACE PROCEDURE p1(a UUID) BEGIN END;
      CALL p1(1);
      

      ERROR 1292 (22007): Incorrect uuid value: '1' for column ``.``.`a` at row 0
      

      Notice, an error does happen, but during the assignment statement execution time, which leads to a different error.

      For consistency, all these cases when an assignment happens:

      • Stored routine local variables
      • Stored routine IN parameters
      • Stored routine OUT parameters
      • Stored function RETURN values
      • Cursor IN parameters
      • FETCH

      should detect wrong data types mixes and raise the "Illegal parameter data types" error.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.