VALUES not accessible by variable name in ON DUPLICATE KEY UPDATE

[oscarbatori]

It seems that although we support ON DUPLICATE KEY UPDATE, we do not provide access to the VALUES by variable which is normally how that statement is used:

query = b"INSERT INTO characters (name, adjective, id, date_of_death) VALUES ('Anna', 'tragic', 1, '1877-01-01 00:00:00'), ('V...ULL) ON DUPLICATE KEY UPDATE name = VALUES(name), adjective = VALUES(adjective), date_of_death = VALUES(date_of_death)"
raw = False, buffered = False, raw_as_string = False

    def cmd_query(self, query, raw=None, buffered=False, raw_as_string=False):
        """Send a query to the MySQL server"""
        self.handle_unread_result()
        if raw is None:
            raw = self._raw
        try:
            if not isinstance(query, bytes):
                query = query.encode('utf-8')
>           self._cmysql.query(query,
                               raw=raw, buffered=buffered,
                               raw_as_string=raw_as_string)
E                              _mysql_connector.MySQLInterfaceError: unknown error: unsupported syntax: values(name)

../../../../../anaconda3/envs/doltpy-dev/lib/python3.8/site-packages/mysql/connector/connection_cext.py:487: MySQLInterfaceError

[oscarbatori]

@Hydrocharged got a ETA on this one?

[Hydrocharged]

What would you say the priority is for it?

[oscarbatori]

It's bug in my view, since it renders the most common use-case for the feature not really usable... unless I am missing some way of accessing the values tuple by name?

[timsehn]

Daylon, just fix this.

…

On Fri, Jan 22, 2021 at 8:55 AM Oscar Batori ***@***.***> wrote: It's bug in my view, since it renders the most common use-case for the feature not really usable... unless I am missing some way of accessing the values tuple by name? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1225 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJAR3B7UMYXKGZCLG6DDADS3GUY7ANCNFSM4WGJDFUQ> .

[Hydrocharged]

@oscarbatori dolthub/go-mysql-server#273
VALUES() has been recently deprecated, although we don't support the updated syntax either (#4479), so I went ahead and implemented this as it's the simpler of the two to do, just so you have something to work with in the meantime.

[oscarbatori]

@Hydrocharged so to be clear we do support the VALUES() syntax that is deprecated?

I agree with the linked issue that this is recently deprecated enough such that we should support it. In particular we are using it via clients that generate SQL in the legacy syntax.

[Hydrocharged]

Yes, the referenced PR is adding the functionality.