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

No error when inserting DEFAULT(non_virtual_column) into a virtual column

Details

    Description

      I create a table with a virtual column:

      SET sql_mode=STRICT_ALL_TABLES;
      		 
      CREATE OR REPLACE TABLE t1 (
      		 
       a INT NOT NULL DEFAULT 10,
      		 
       b INT AS (a+1) VIRTUAL
      		 
      ) ENGINE=MyISAM;

      Now I insert an explicit value to the virtual column, and it returns an error as expected:

      INSERT INTO t1 (b) VALUES (10);


      ERROR 1906 (HY000): The value specified for generated column 'b' in table 't1' has been ignored

      Now I insert a value into the virtual column, but using DEFAULT(a) instead of an explicit value:

      INSERT INTO t1 (b) VALUES (DEFAULT(a));


      Query OK, 1 row affected (0.001 sec)

      It inserted the record without the error. Looks wrong. Expect to get the same error.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21995 Server crashes in Item_field::real_type_handler with table value constructor

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22560 Crash on a table value constructor with an SP variable

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22591 Debug build crashes on EXECUTE IMMEDIATE '... WHERE ?' USING IGNORE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT))

          • Major - Major loss of function.
          • Closed

          Activity

            bar Alexander Barkov added a comment - - edited

            Additionally, starting from 10.2, the following statement erroneously returns the error:

            EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING DEFAULT;


            ERROR 1906 (HY000): The value specified for generated column 'b' in table 't1' has been ignored

            but if I use DEFAULT directly (instead of a binding it):

            INSERT INTO t1 (b) VALUES(DEFAULT);

            it works without errors.

            The variant which binds DEFAULT as a parameter should also work without errors.

            bar Alexander Barkov added a comment - - edited Additionally, starting from 10.2, the following statement erroneously returns the error: EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING DEFAULT ; ERROR 1906 (HY000): The value specified for generated column 'b' in table 't1' has been ignored but if I use DEFAULT directly (instead of a binding it): INSERT INTO t1 (b) VALUES ( DEFAULT ); it works without errors. The variant which binds DEFAULT as a parameter should also work without errors.

            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.