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

Unexpected 'Unknown .. error from engine' with SELECT NULL UNION SELECT NULL

Details

    Description

      This query:

      CREATE OR REPLACE TABLE t1 ENGINE=INNODB SELECT NULL UNION SELECT NULL;
      

      returns an unexpected error:

      ERROR 1005 (HY000): Can't create table `test`.`t1` (errno: 168 "Unknown (generic) error from engine")
      

      Note, with ENGINE=MyISAM it works fine.

      Attachments

        Issue Links

          Activity

            The reason for this error is that starting with some commit in 10.3 or bb-10.2-ext, the field->type() == MYSQL_TYPE_NULL is being passed to InnoDB, instead of the former MYSQL_TYPE_STRING. get_innobase_type_from_mysql_type() is not ready to handle that.

            If this bug is addressed by adjusting get_innobase_type_from_mysql_type(), then extreme care must be taken to ensure that INFORMATION_SCHEMA.INNODB_SYS_COLUMNS will be reporting the same prtype and mtype as before. Otherwise, InnoDB data compatibility with older versions may be broken.

            marko Marko Mäkelä added a comment - The reason for this error is that starting with some commit in 10.3 or bb-10.2-ext, the field->type() == MYSQL_TYPE_NULL is being passed to InnoDB, instead of the former MYSQL_TYPE_STRING . get_innobase_type_from_mysql_type() is not ready to handle that. If this bug is addressed by adjusting get_innobase_type_from_mysql_type() , then extreme care must be taken to ensure that INFORMATION_SCHEMA.INNODB_SYS_COLUMNS will be reporting the same prtype and mtype as before. Otherwise, InnoDB data compatibility with older versions may be broken.

            I am receiving this same error in MariaDB-10.4.7. It works in MySQL 5.6 and is preventing me from migrating from MySQL to MariaDB.

            DROP TABLE IF EXISTS temp_t1;
            CREATE TEMPORARY TABLE `temp_t1` (  
              `id` INT(11) NOT NULL,
              PRIMARY KEY (`id`)
            );
             
            CREATE TEMPORARY TABLE temp_main 
              SELECT NULL AS field1 FROM temp_t1
            UNION ALL
              SELECT NULL AS field1 FROM temp_t1
            

            dukethrash Deryl Spielman added a comment - I am receiving this same error in MariaDB-10.4.7. It works in MySQL 5.6 and is preventing me from migrating from MySQL to MariaDB. DROP TABLE IF EXISTS temp_t1; CREATE TEMPORARY TABLE `temp_t1` ( `id` INT (11) NOT NULL , PRIMARY KEY (`id`) );   CREATE TEMPORARY TABLE temp_main SELECT NULL AS field1 FROM temp_t1 UNION ALL SELECT NULL AS field1 FROM temp_t1

            I'm getting the same error as Deryl Spielman. Any ideas?

            aciokler Abraham Ciokler added a comment - I'm getting the same error as Deryl Spielman. Any ideas?

            The testcase by dukethrash can indeed be reduced as follows (i.e. similar or indentical to the original description):

            CREATE TABLE t ENGINE=InnoDB AS SELECT NULL AS a FROM (SELECT 1) AS b UNION ALL SELECT NULL AS c FROM (SELECT 1) AS d;
            

            Which leads to:

            10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

            ERROR 1005 (HY000): Can't create table `test`.`t` (errno: 168 "Unknown (generic) error from engine")
            

            On InnoDB only. MyISAM, Aria and Memory do not result in the same issue. Spider gives an interesting issue for which I will create a seperate bug.
            No issue is recorded in the error log, which is odd when error 168 is observed.

            Bug confirmed not present in:
            MariaDB: 10.3.37 (dbg), 10.3.37 (opt), 10.4.27 (dbg), 10.4.27 (opt), 10.5.16 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.3 (dbg), 10.9.3 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (dbg), 10.11.0 (opt)

            Bug (or feature/syntax) confirmed not present in:
            MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

            Roel Roel Van de Paar added a comment - The testcase by dukethrash can indeed be reduced as follows (i.e. similar or indentical to the original description): CREATE TABLE t ENGINE=InnoDB AS SELECT NULL AS a FROM ( SELECT 1) AS b UNION ALL SELECT NULL AS c FROM ( SELECT 1) AS d; Which leads to: 10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug) ERROR 1005 (HY000): Can't create table `test`.`t` (errno: 168 "Unknown (generic) error from engine") On InnoDB only. MyISAM, Aria and Memory do not result in the same issue. Spider gives an interesting issue for which I will create a seperate bug. No issue is recorded in the error log, which is odd when error 168 is observed. Bug confirmed not present in: MariaDB: 10.3.37 (dbg), 10.3.37 (opt), 10.4.27 (dbg), 10.4.27 (opt), 10.5.16 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.3 (dbg), 10.9.3 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (dbg), 10.11.0 (opt) Bug (or feature/syntax) confirmed not present in: MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              6 Vote for this issue
              Watchers:
              9 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.