UPDATE ... JOIN fails for tables containing capital letters

[arvidfm]

The below query fails with the error message The target table targettable_test of the UPDATE is not updatable:

UPDATE targetTable_test
    JOIN sourceTable_test
    SET
        targetTable_test.value = `sourceTable_test`.value
    WHERE `sourceTable_test`.`id` = targetTable_test.source_id;

It works fine if you avoid uppercase letters in the table name, or if you use an alias in the query that doesn't contain uppercase letters, so it seems to be an issue with case insensitivity handling. We have quite a few tables that use capital letters (mostly for legacy reasons), and having to ensure that every update query involving them uses an alias would be a bit too error prone.

MWE confirmed to work with Percona Server 8.3:

DROP TABLE IF EXISTS targetTable_test, sourceTable_test;

CREATE TABLE targetTable_test (
    source_id int PRIMARY KEY,
    value int
);
CREATE TABLE sourceTable_test (
    id int PRIMARY KEY,
    value int
);

UPDATE targetTable_test
    JOIN sourceTable_test
    SET
        targetTable_test.value = `sourceTable_test`.value
    WHERE `sourceTable_test`.`id` = targetTable_test.source_id;


DROP TABLE targetTable_test, sourceTable_test;

[timsehn]

@max-hoffman is also going to grab this one.

[fulghum]

This fix has been released in Dolt 1.39.4. Thanks for reporting this one! 🙏

[arvidfm]

Hmm this still seems to fail for me if I use an ON clause instead of a WHERE:

UPDATE targetTable_test
    JOIN sourceTable_test
    ON `sourceTable_test`.`id` = targetTable_test.source_id
    SET
        targetTable_test.value = `sourceTable_test`.value;

Error: The target table targettable_test of the UPDATE is not updatable

The culprit appears to be this function not changing to lower case for the map keys: https://github.com/dolthub/go-mysql-server/blob/6fcdd8a577b67ce92440ff6ed4d9fbac2c3c80af/sql/planbuilder/dml.go#L608-L618 (the original fix seems to have fixed the issue in another near-identical function in another file)

Should I open a separate ticket for this one?

[max-hoffman]

@arvidfm thanks for pointing this out. I added the fix here dolthub/go-mysql-server#2540. We are happy to usher PRs through in the future if you send them our way.

[arvidfm]

@max-hoffman Amazing, thank you for the quick response!