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

subquery on information_schema fails with error message

    XMLWordPrintable

Details

    Description

      In 10.5.8, do this:

      CREATE OR REPLACE TABLE _test1 (num INT);
      CREATE OR REPLACE TABLE _test2 (num INT);
      INSERT INTO `_test1` SET `num` = (SELECT `table_rows` FROM `information_schema`.`tables` WHERE `table_name`='_test2');
      

      The last statement returns "SQL Error (1406): Data too long for column 'TABLE_SCHEMA' at row 1".
      It did work fine in 10.4.8.
      On 10.4.17, the same sequence returns either the same error or "ERROR 1242 (21000): Subquery returns more than 1 row" (which is wrong, the subquery on its own returns exactly one row). Which of these error messages i get seems to depend on the user.

      I got the error message about "more than 1 row" on 10.5.8 when trying

      INSERT INTO `_test1` SET `num` = (SELECT `version` FROM `information_schema`.`tables` WHERE `table_name`='_test2');
      

      Other similar queries also fail:

      INSERT INTO `_test1` SET `num` = (SELECT `maxlen` FROM `information_schema`.`character_sets` WHERE `default_collate_name`='ascii_general_ci');
      INSERT INTO `_test1` SET `num` = (SELECT COUNT(*) FROM `information_schema`.`system_variables`);
      

      It seems like this occurs with any non-empty table from information_schema, both Memory and Aria. I never saw that from any other database.

      Attachments

        Activity

          People

            serg Sergei Golubchik
            sweiser Sebastian Weiser
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.