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

Wrong "Truncated incorrect DECIMAL value" warning/error in recent releases

Details

    Description

      The below worked fine so far, as expected, but starts to throw unexpected

      ERROR 1292 (22007) at line 15: Truncated incorrect DECIMAL value: '#'
      

      in most recent releases. I looked over the 10.3.37 (lowest version release I can reproduce this on) release notes and changelog page, but could not find any change that seemed to be related to this change in behavior ...

      drop table if exists t1;
      drop table if exists t2;
       
      create table t1(c1 varchar(1));
      create table t2(c1 varchar(1));
       
      set session sql_mode = STRICT_TRANS_TABLES;
       
      insert into t1(c1) values('#');
       
      insert into t2(c1)
        select if(c1 = '#', c1 = 0, c1) as c1
        from t1;
      
      

      Attachments

        Issue Links

          Activity

            serg Sergei Golubchik added a comment - caused by https://github.com/MariaDB/server/commit/8c389393695
            danblack Daniel Black added a comment - For review: https://github.com/MariaDB/server/pull/2422
            danblack Daniel Black added a comment - - edited

            While definitely a regression, isn't the current behaviour correct in emitting an error on the if function returning two different types? So is this actually a bug?

            Taking a CREATE TABLE .. SELECT case

            create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1
             
            : query 'create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1' failed: 1292: Truncated incorrect DECIMAL value: '#'
            

            This produces the same error even before MDEV-29540.

            danblack Daniel Black added a comment - - edited While definitely a regression, isn't the current behaviour correct in emitting an error on the if function returning two different types? So is this actually a bug? Taking a CREATE TABLE .. SELECT case create table t2 as select if (c1 = '#' , c1 = 0, c1) as c1 from t1   : query 'create table t2 as select if(c1 = ' # ', c1 = 0, c1) as c1 from t1' failed: 1292: Truncated incorrect DECIMAL value: '#' This produces the same error even before MDEV-29540 .

            No, the current behavior is wrong. There are few considerations here:

            • sql_mode is STRICT_TRANS_TABLES but the test has no transactional tables, so CREATE ... SELECT should only produce a warning
            • but the original meaning of the STRICT_TRANS_TABLES was kind of "be strict if the statement can be completely rolled back", and unlike INSERT ... SELECT a CREATE ... SELECT can be "completely rolled back" even for non-transactional tables. In that logic, CREATE ... SELECT behaves correctly and INSERT ... SELECT did behave correctly before 8c389393695.
            • still currently we mainly interpret STRICT_TRANS_TABLES as "modifying transactional tables" and here the warning happens not on INSERT as such, but in the comparison. In that logic, this warning should be never treated as an error in any sql_mode and any table, transactional or not.

            This should be eventually solved in MDEV-19362 (see also the discussion in comments). For now let's just fix the regression and restore the old behavior.

            serg Sergei Golubchik added a comment - No, the current behavior is wrong. There are few considerations here: sql_mode is STRICT_TRANS_TABLES but the test has no transactional tables, so CREATE ... SELECT should only produce a warning but the original meaning of the STRICT_TRANS_TABLES was kind of "be strict if the statement can be completely rolled back", and unlike INSERT ... SELECT a CREATE ... SELECT can be "completely rolled back" even for non-transactional tables. In that logic, CREATE ... SELECT behaves correctly and INSERT ... SELECT did behave correctly before 8c389393695 . still currently we mainly interpret STRICT_TRANS_TABLES as "modifying transactional tables" and here the warning happens not on INSERT as such, but in the comparison. In that logic, this warning should be never treated as an error in any sql_mode and any table, transactional or not. This should be eventually solved in MDEV-19362 (see also the discussion in comments). For now let's just fix the regression and restore the old behavior.

            People

              danblack Daniel Black
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.