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

CAST(char_col AS DOUBLE) prints redundant spaces in a warning

Details

    Description

      CREATE OR REPLACE TABLE t1 (a CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);
      		 
      INSERT INTO t1 VALUES ('1x'), ('x');
      		 
      SELECT a,CAST(a AS DOUBLE) FROM t1 ORDER BY a;


      +------+-------------------+
      		 
      | a    | CAST(a AS DOUBLE) |
      		 
      +------+-------------------+
      		 
      | 1x   |                 1 |
      		 
      | x    |                 0 |
      		 
      +------+-------------------+


      SHOW WARNINGS;


      +---------+------+----------------------------------------------------------+
      		 
      | Level   | Code | Message                                                  |
      		 
      +---------+------+----------------------------------------------------------+
      		 
      | Warning | 1292 | Truncated incorrect DOUBLE value: '1x                  ' |
      		 
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'x                   ' |
      		 
      +---------+------+----------------------------------------------------------+

      Notice extra trailing spaces in the warning text. There should not be trailing spaces:

      • The column values are just '1x' and 'x' because sql_mode=PAD_CHAR_TO_FULL_LENGTH is not set.
      • Moreover, the number of trailing spaces is more than the column character length (which is 5).

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19123 Change default charset from latin1 to utf8mb4

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description {code:sql}
            CREATE OR REPLACE TABLE t1 (a CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);
            INSERT INTO t1 VALUES ('1x'), ('x');
            SELECT a,CAST(a AS DOUBLE) FROM t1 ORDER BY a;
            {code}
            {noformat}
            +------+-------------------+
            | a | CAST(a AS DOUBLE) |
            +------+-------------------+
            | 1x | 1 |
            | x | 0 |
            +------+-------------------+
            {noformat}
            {code}
            SHOW WARNINGS;
            {code}
            {noformat}
            +---------+------+----------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect DOUBLE value: '1x ' |
            | Warning | 1292 | Truncated incorrect DOUBLE value: 'x ' |
            +---------+------+----------------------------------------------------------+
            {noformat}

            Notice extra trailing spaces in the warning text. There should not be trailing spaces. The column values are just '1x' and 'x' because *{{sql_mode=PAD_CHAR_TO_FULL_LENGTH}}* is not set.
            		{code:sql}
            CREATE OR REPLACE TABLE t1 (a CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);
            INSERT INTO t1 VALUES ('1x'), ('x');
            SELECT a,CAST(a AS DOUBLE) FROM t1 ORDER BY a;
            {code}
            {noformat}
            +------+-------------------+
            | a | CAST(a AS DOUBLE) |
            +------+-------------------+
            | 1x | 1 |
            | x | 0 |
            +------+-------------------+
            {noformat}
            {code}
            SHOW WARNINGS;
            {code}
            {noformat}
            +---------+------+----------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect DOUBLE value: '1x ' |
            | Warning | 1292 | Truncated incorrect DOUBLE value: 'x ' |
            +---------+------+----------------------------------------------------------+
            {noformat}

            Notice extra trailing spaces in the warning text. There should not be trailing spaces:
            - The column values are just '1x' and 'x' because *{{sql_mode=PAD_CHAR_TO_FULL_LENGTH}}* is not set.
            - Moreover, the number of trailing spaces is more than the column character length (which is 5).
            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Assignee Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            serg Sergei Golubchik made changes -
            Priority Minor [ 4 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2024-06-04 14:24:19.0 2024-06-04 14:24:19.168
            bar Alexander Barkov made changes -
            Fix Version/s 10.5.26 [ 29832 ]
            Fix Version/s 10.6.19 [ 29833 ]
            Fix Version/s 10.11.9 [ 29834 ]
            Fix Version/s 11.1.6 [ 29835 ]
            Fix Version/s 11.2.5 [ 29836 ]
            Fix Version/s 11.4.3 [ 29837 ]
            Fix Version/s 11.5.2 [ 29838 ]
            Fix Version/s 11.6.0 [ 29839 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -

            People

              bar Alexander Barkov
              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.