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

routine_definition in information_schema.routines loses tablename if it starts with an _ and is not backticked

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3(EOL), 10.4(EOL), 10.5
    • 10.3.29, 10.4.19, 10.5.10
    • Information Schema
    • None
    • I'm using MariaDB 10.3.20 but I'm guessing it also affects the latest version...

    Description

      -- sample to reproduce problem
      		 
      CREATE PROCEDURE test()
      		 
      BEGIN
      		 
        SELECT NULL FROM _table;
      		 
      END;
      		 
       
      		 
      SHOW CREATE PROCEDURE test;
      		 
      -- `Create Procedure` contains correct table name
      		 
       
      		 
      SELECT routine_definition FROM information_schema.routines WHERE routine_schema=DATABASE() AND specific_name='test';
      		 
      -- `routine_definition` contains " SELECT NULL FROM ;" (no table name!)

      I can also confirm this happens for triggers:

      CREATE TRIGGER test AFTER INSERT ON anytable FOR EACH ROW
      		 
      BEGIN
      		 
      SET @x=(SELECT NULL FROM _table);
      		 
      END;
      		 
       
      		 
      SHOW TRIGGERS;
      		 
      -- `statement` is missing _table
      		 
       
      		 
      SELECT action_statement FROM information_schema.triggers WHERE trigger_name='test';
      		 
      -- `ACTION_STATEMENT` is missing _table
      		 
       
      		 
      SHOW CREATE trigger test;
      		 
      -- SQL Original Statement is correct

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          AzzaAzza69 Azza created issue -
          AzzaAzza69 Azza made changes -
          Field Original Value New Value
          Comment [ I can also confirm this happens for triggers:

          CREATE TRIGGER test AFTER INSERT ON anytable FOR EACH ROW
          BEGIN
            SET @x=(SELECT NULL FROM _table);
          END;

          SHOW TRIGGERS;
           -- `statement` is missing _table

          SELECT action_statement FROM information_schema.triggers WHERE trigger_name='test';
          -- `ACTION_STATEMENT` is missing _table

          SHOW CREATE trigger test;
          -- SQL Original Statement is correct
          ]
          AzzaAzza69 Azza made changes -
          Description -- sample to reproduce problem
          CREATE PROCEDURE test()
          BEGIN
            SELECT NULL FROM _table;
          END;

          SHOW CREATE PROCEDURE test;
          -- `Create Procedure` contains correct table name

          SELECT routine_definition FROM information_schema.routines WHERE routine_schema=DATABASE() AND specific_name='test';
          -- `routine_definition` contains " SELECT NULL FROM ;" (no table name!)
          		-- sample to reproduce problem
          CREATE PROCEDURE test()
          BEGIN
            SELECT NULL FROM _table;
          END;

          SHOW CREATE PROCEDURE test;
          -- `Create Procedure` contains correct table name

          SELECT routine_definition FROM information_schema.routines WHERE routine_schema=DATABASE() AND specific_name='test';
          -- `routine_definition` contains " SELECT NULL FROM ;" (no table name!)

          ====

          I can also confirm this happens for triggers:

          CREATE TRIGGER test AFTER INSERT ON anytable FOR EACH ROW
          BEGIN
          SET @x=(SELECT NULL FROM _table);
          END;

          SHOW TRIGGERS;
          – `statement` is missing _table

          SELECT action_statement FROM information_schema.triggers WHERE trigger_name='test';
          – `ACTION_STATEMENT` is missing _table

          SHOW CREATE trigger test;
          – SQL Original Statement is correct
          alice Alice Sherepa made changes -
          Affects Version/s 10.3 [ 22126 ]
          Affects Version/s 10.4 [ 22408 ]
          Affects Version/s 10.5 [ 23123 ]
          alice Alice Sherepa made changes -
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 10.5 [ 23123 ]
          alice Alice Sherepa added a comment -

          Thanks! I repeated as described on 10.3-10.5, while on 5.5-10.2 tablename is shown.

          alice Alice Sherepa added a comment - Thanks! I repeated as described on 10.3-10.5, while on 5.5-10.2 tablename is shown.
          alice Alice Sherepa made changes -
          Assignee Oleksandr Byelkin [ sanja ]
          alice Alice Sherepa made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          serg Sergei Golubchik made changes -
          Description -- sample to reproduce problem
          CREATE PROCEDURE test()
          BEGIN
            SELECT NULL FROM _table;
          END;

          SHOW CREATE PROCEDURE test;
          -- `Create Procedure` contains correct table name

          SELECT routine_definition FROM information_schema.routines WHERE routine_schema=DATABASE() AND specific_name='test';
          -- `routine_definition` contains " SELECT NULL FROM ;" (no table name!)

          ====

          I can also confirm this happens for triggers:

          CREATE TRIGGER test AFTER INSERT ON anytable FOR EACH ROW
          BEGIN
          SET @x=(SELECT NULL FROM _table);
          END;

          SHOW TRIGGERS;
          – `statement` is missing _table

          SELECT action_statement FROM information_schema.triggers WHERE trigger_name='test';
          – `ACTION_STATEMENT` is missing _table

          SHOW CREATE trigger test;
          – SQL Original Statement is correct           		{code:sql}
          -- sample to reproduce problem
          CREATE PROCEDURE test()
          BEGIN
            SELECT NULL FROM _table;
          END;

          SHOW CREATE PROCEDURE test;
          -- `Create Procedure` contains correct table name

          SELECT routine_definition FROM information_schema.routines WHERE routine_schema=DATABASE() AND specific_name='test';
          -- `routine_definition` contains " SELECT NULL FROM ;" (no table name!)
          {code}

          I can also confirm this happens for triggers:
          {code:sql}
          CREATE TRIGGER test AFTER INSERT ON anytable FOR EACH ROW
          BEGIN
          SET @x=(SELECT NULL FROM _table);
          END;

          SHOW TRIGGERS;
          -- `statement` is missing _table

          SELECT action_statement FROM information_schema.triggers WHERE trigger_name='test';
          -- `ACTION_STATEMENT` is missing _table

          SHOW CREATE trigger test;
          -- SQL Original Statement is correct
          {code}
          serg Sergei Golubchik made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
          serg Sergei Golubchik made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          serg Sergei Golubchik made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Summary routine_definition in information_schema.routines loses tablename if it starts with an _ and is not backticked. routine_definition in information_schema.routines loses tablename if it starts with an _ and is not backticked
          serg Sergei Golubchik made changes -
          Fix Version/s 10.3.29 [ 25206 ]
          Fix Version/s 10.4.19 [ 25205 ]
          Fix Version/s 10.5.10 [ 25204 ]
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 10.5 [ 23123 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 121340 ] MariaDB v4 [ 159185 ]

          People

            serg Sergei Golubchik
            AzzaAzza69 Azza
            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.