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

Autodiscover + CONNECT + OR REPLACE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.12
    • Fix Version/s: 10.1
    • Component/s: None
    • Labels:

      Description

      Sorry, I understand that this test case is complex, but I wasn't able to simplify it:

      MariaDB [test]> DROP PROCEDURE IF EXISTS materialize_sql;
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [test]> DELIMITER ||
      MariaDB [test]> CREATE PROCEDURE `materialize_sql`(IN p_sql TEXT)
          -> MODIFIES SQL DATA
          -> BEGIN
          -> SET @v_materialized_sql := CONCAT_WS('',
          ->   'CREATE OR REPLACE TABLE `_xy`'
          -> , ' ENGINE = CONNECT'
          -> , ' TABLE_TYPE = MYSQL'
          -> , ' SRCDEF = ''', REPLACE(p_sql, '''', ''''''), ''''
          -> , ' CONNECTION = ''mysql://root:root@localhost/'''
          -> );
          -> PREPARE stmt_materialized_sql FROM @v_materialized_sql;
          -> EXECUTE stmt_materialized_sql;
          -> DEALLOCATE PREPARE stmt_materialized_sql;
          -> SET @v_materialized_sql := NULL;
          -> SELECT * FROM _xy;
          -> END ||
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [test]> DELIMITER ;
      MariaDB [test]> CALL materialize_sql('SELECT 1 AS a');
      +---+
      | a |
      +---+
      | 1 |
      +---+
      1 row in set (0.14 sec)
       
      Query OK, 0 rows affected (0.14 sec)
       
      MariaDB [test]> CALL materialize_sql('SELECT 1 AS z');
      ERROR 1054 (42S22): Unknown column 'test._xy.a' in 'field list'

      Just to be clear, the procedure creates a CONNECT table based on a query, so that the same resultset can be read with a SELECT. This allows, for example, to loop the results of SHOW MASTER STATUS with a cursor, use subqueries, etc. That's why I set the bug to "major" despite it being so exotic.

      The problem seems to occur on the second call, when the underlying table is replaced with another table which has different columns. Note that the table is correctly replaced. But the error message still references to a column which doesn't exist anymore.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              f_razzoli Federico Razzoli
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.