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

unexpected CAST result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.0.17
    • N/A
    • OTHER
    • Linux
    • 10.1.8-3, 10.1.8-4, 10.1.9-3

    Description

      SET @aaa = COLUMN_CREATE('price', '');
      SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL) aaa;

      aaa     
      --------
      0       

      SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL)  + 1 aaa

      aaa     
      --------
      1       

      DELIMITER $$
       
      CREATE FUNCTION `test`(
          par_aaa DECIMAL(10,2)
         )
          RETURNS DECIMAL(10,2)
          DETERMINISTIC    
          BEGIN
      	RETURN 0;
          END$$
       
      DELIMITER ;

      SELECT test(COLUMN_GET(@aaa, 'price' AS DECIMAL)) bbb

      Error CODE: 1918
      Encountered illegal VALUE '' WHEN converting TO DECIMAL

      SELECT test('') bbb

      Error CODE: 1366
      Incorrect DECIMAL VALUE: '' FOR COLUMN 'par_aaa' AT ROW 441

      SELECT test(CAST('' AS DECIMAL(10,2))) bbb

      Error CODE: 1292
      Truncated incorrect DECIMAL VALUE: ''

      SELECT test(''+0) bbb

      bbb     
      --------
      0.00   

      More expected that result '' + 0 and CAST('' AS DECIMAL(10,2)) would be same.
      But we saw error when do CAST, and automatic convertion '' as 0 when try select or make math operation.

      SELECT @@sql_mode

      NO_BACKSLASH_ESCAPES,STRICT_ALL_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

      Attachments

        1. my.cnf
          6 kB
          Mohit Chandel

        Issue Links

          Activity

            People

              bar Alexander Barkov
              mikhail Mikhail Gavrilov
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.