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

Session charset applied to COLUMN_GET() first arg in 10.1

Details

    • 10.1.22

    Description

      I added this as a comment to MDEV-9429 and then realized it wasn't going to be seen and was in fact different.

      The gist of it

      SELECT 2 * COLUMN_GET(
      		 
          COLUMN_GET(
      		 
              COLUMN_CREATE('a', COLUMN_CREATE('b', 123)),
      		 
              'a' AS char
      		 
          ), 
          'b' AS char
      		 
      )

      returns 246 in 10.0 and -64 in 10.1.

      I'm guessing this was added to the docs as resolution for MDEV-9429:

      If you are trying to get a nested dynamic column as a string use 'as BINARY' as the last argument of COLUMN_GET (otherwise problems with character set conversion and illegal symbols are possible)...

      but the wording doesn't exactly apply. I am not trying to get a nested column as a string. I have a previously functioning ORM that generates Maria-specific SQL, which wrote an SQL expression involving a nested dynamic column's value. The integer value of the expression is a function of server version.

      I don't understand how the server works but it appears the server was changed so that in

      • column_get(column_get(…, 'foo' as char), …)

      Maria 10.1 modifies the value of the 1st param of the outer column_get() by converting from the client charset (utf8) to binary? It's not clear to me:

      • why it should introduce this conversion when the context doesn't call for it (friendly context-appropriate conversion is apparent elsewhere in mysql, e.g. 2 * "2" gives 4)
      • how this change is an improvement
      • why give a spurious integer answer (and a warning) instead of no answer or an error

      1. Before upgrade

      In good-old 10.0, char was understood when it was inside a 

      MariaDB [(none)]> show variables like 'version';
      		 
      +---------------+--------------------------------+
      		 
      | Variable_name | Value                          |
      		 
      +---------------+--------------------------------+
      		 
      | version       | 10.0.27-MariaDB-1~trusty-wsrep |
      		 
      +---------------+--------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]>  SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2);
      		 
      +-----------------------------------------------------------------------------------------------------------------+
      		 
      | (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2) |
      		 
      +-----------------------------------------------------------------------------------------------------------------+
      		 
      |                                                                                                             246 |
      		 
      +-----------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2);
      		 
      +-------------------------------------------------------------------------------------------------------------------+
      		 
      | (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2) |
      		 
      +-------------------------------------------------------------------------------------------------------------------+
      		 
      |                                                                                                               246 |
      		 
      +-------------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)

      2. After upgrade

      Now we need to do more work in our ORM

      MariaDB [(none)]> show variables like 'version';
      		 
      +---------------+-----------------+
      		 
      | Variable_name | Value           |
      		 
      +---------------+-----------------+
      		 
      | version       | 10.1.20-MariaDB |
      		 
      +---------------+-----------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2);
      		 
      +-----------------------------------------------------------------------------------------------------------------+
      		 
      | (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS char), 'int' AS char) * 2) |
      		 
      +-----------------------------------------------------------------------------------------------------------------+
      		 
      |                                                                                                             -64 |
      		 
      +-----------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set, 1 warning (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SHOW WARNINGS;
      		 
      +---------+------+---------------------------------------+
      		 
      | Level   | Code | Message                               |
      		 
      +---------+------+---------------------------------------+
      		 
      | Warning | 1300 | Invalid utf8 character string: '\xF6' |
      		 
      +---------+------+---------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SELECT (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2);
      		 
      +-------------------------------------------------------------------------------------------------------------------+
      		 
      | (COLUMN_GET(COLUMN_GET(COLUMN_CREATE('child', COLUMN_CREATE('int', 123)), 'child' AS binary), 'int' AS char) * 2) |
      		 
      +-------------------------------------------------------------------------------------------------------------------+
      		 
      |                                                                                                               246 |
      		 
      +-------------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            thefsb Tom
            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.