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

User variables lose coercibility information, leading to unexpected collation errors

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Won't Fix
    • Fix Version/s: N/A
    • Component/s: Variables
    • Labels:
      None

      Description

      User variables seem to have a COERCIBILITY() of 2, the same as table columns, even when assigned values with higher COERCIBILITY. This can lead to unexpected errors:

      CREATE TEMPORARY TABLE t (v varchar(100) COLLATE latin1_swedish_ci, w varchar(100) COLLATE latin1_general_ci);
      INSERT INTO t (v,w) VALUES ('foo','bar');
      SELECT v,w INTO @v,@w FROM t;
      SELECT v='foo',v='bar',w='foo',w='bar' FROM t;  # comparison of table columns with literals:  OK because columns have lower COERCIBILITY than literals
      SELECT @v='foo',@v='bar',@w='foo',@w='bar';     # comparison of user variables with literals:  OK because user variables have lower COERCIBILITY than literals
      SET @f := 'foo', @b := 'bar';
      SELECT v=@f,v=@b,w=@f,w=@b FROM t;              # comparison of table columns with user variables; FAILS WITH ERROR:  Illegal mix of collations
      SELECT @v=@f,@v=@b,@w=@f,@w=@b;                 # comparison of user variables with each other; FAILS WITH ERROR:  Illegal mix of collations
      DROP TEMPORARY TABLE t;
      

      Or perhaps more succinctly, the following look like they ought to be equivalent, but they aren't:

      1. SELECT col='value' FROM table;
      2. SET @v := 'value'; SELECT col=@v FROM table;

      I would like to suggest that they SHOULD BE... and that the way to make this work correctly would be for user variables to retain/remember the COERCIBILITY of whatever value they're assigned.
      i.e. in the following:

      SET @a := (SELECT column FROM table), @b := 'literal value';
      SELECT COERCIBILITY(@a), COERCIBILITY(@b);

      Currently the returned coercibility values are (2,2)... but I am suggesting that they should be (2,4) instead, which are the coercibilities of the values that have been assigned to @a and @b respectively.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            Dean T Dean Trower
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.