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

unexpected CAST result

    XMLWordPrintable

    Details

    • Sprint:
      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

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              mikhail Mikhail Gavrilov
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated: