"Unable to find field with index" error on INSERT in procedure following IF (SELECT ...)

[arvidfm]

Trying to run an INSERT statement in a procedure within an IF that runs a subquery fails with ERROR 1105 (HY000): unable to find field with index ... in row of ... columns:

CREATE PROCEDURE create_system_user()
BEGIN
    IF (SELECT COUNT(*) FROM `user_test` where `user_test`.`username` = 'system') = 0 THEN
        INSERT INTO `user_test` (`username`) VALUES ('system');
    END IF;
END;

The error seems to happen even if the subquery is for a different table than the insert.

Complete MWE:

DROP TABLE IF EXISTS user_test;
DROP PROCEDURE IF EXISTS create_system_user;

CREATE TABLE user_test(
    id int primary key auto_increment,
    username VARCHAR(30)
);

DELIMITER //

CREATE PROCEDURE create_system_user()
BEGIN
    IF (SELECT COUNT(*) FROM `user_test` where `user_test`.`username` = 'system') = 0 THEN
        INSERT INTO `user_test` (`username`) VALUES ('system');
    END IF;
END; //

DELIMITER ;

CALL create_system_user();

DROP TABLE user_test;
DROP PROCEDURE create_system_user;

[nicktobey]

I can reproduce this.

It looks like the reason that the error happens even if the subquery is for a different table is because the error occurs while evaluating the conditional, regardless of what's inside the THEN block.

[nicktobey]

This seems straightforward: we have an analysis pass called assignExecIndexes that visits each each expression and assigns it an index based on the current scope. This pass isn't being run on if-conditions in procedures.

I say "seems", because updating the pass to also apply to if-conditions fixes this behavior, but breaks some other tests. So the actual fix might be a bit more nuanced.