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

CONCAT function inside view casts values incorrectly with Oracle sql_mode

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.5
    • 10.3.0
    • Parser, Views
    • None

    Description

      With Oracle sql_mode enabled, we're seeing CONCAT() function cast strings as numbers (integer and doubles) only inside a view:

      MariaDB [test]> SELECT VERSION();
      +----------------+
      | VERSION()      |
      +----------------+
      | 10.2.5-MariaDB |
      +----------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SELECT @@sql_mode;
      +-------------------------------------------------------------------------------------------+
      | @@sql_mode                                                                                |
      +-------------------------------------------------------------------------------------------+
      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +-------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SELECT CONCAT('FOO','BAR');
      +---------------------+
      | CONCAT('FOO','BAR') |
      +---------------------+
      | FOOBAR              |
      +---------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> CREATE OR REPLACE VIEW t AS SELECT CONCAT('FOO','BAR');
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t;
      +---------------------+
      | CONCAT('FOO','BAR') |
      +---------------------+
      | FOOBAR              |
      +---------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SET sql_mode='Oracle';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT CONCAT('FOO','BAR');
      +---------------------+
      | CONCAT('FOO','BAR') |
      +---------------------+
      | FOOBAR              |
      +---------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> CREATE OR REPLACE VIEW t AS SELECT CONCAT('FOO','BAR');
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t;
      +---------------------+
      | CONCAT('FOO','BAR') |
      +---------------------+
      |                   0 |
      +---------------------+
      1 row in set, 4 warnings (0.00 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +---------+------+------------------------------------------+
      | Level   | Code | Message                                  |
      +---------+------+------------------------------------------+
      | Warning | 1292 | Truncated incorrect INTEGER value: 'FOO' |
      | Warning | 1292 | Truncated incorrect INTEGER value: 'BAR' |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'FOO'  |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'BAR'  |
      +---------+------+------------------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> 
      

      Attachments

        Activity

          People

            bar Alexander Barkov
            wfong Will Fong
            Votes:
            1 Vote for this issue
            Watchers:
            4 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.