Autodiscover + CONNECT + OR REPLACE

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'