dolt: Syntax Error Occurs When Using AS Clause with ON DUPLICATE KEY UPDATE

Using version: v0.18.0

When executing the following query, an error ( syntax error at position 134 near 'AS') occurs. )

INSERT INTO test_campaigns (email, end_time, name, registered_time) VALUES (?,?,?,?) AS new ON DUPLICATE KEY UPDATE email = new.email, end_time = new.end_time

What could be the cause of this?

Starting with MySQL 8.0.19, it is possible to use an alias for the row, with, optionally, one or more of its columns to be inserted, following the VALUES or SET clause, and preceded by the AS keyword. Using the row alias new, the statement shown previously using VALUES() to access the new column values can be written in the form shown here:

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

If possible, I would be grateful if you could support version 8.0.20 and later. Thank you for your consideration.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 15 (9 by maintainers)

Most upvoted comments

@arvidfm You’re absolutely right! I was mistaken.

I got column aliases working last night too. I’m adding tests and will make the final PR shortly.

Update: I have a draft PR that gets the simplest case working (row alias, no column alias): https://github.com/dolthub/go-mysql-server/pull/2534

So with this PR, both @arvidfm and @koh789’s sample queries should work.

I haven’t fully tested it, and it does have some caveats:

  • It doesn’t currently recognize column aliases (ie. VALUES (1, 'test1'), (2, 'test2'), (3, 'test3') AS tbl_new(new_id, new_name))
  • If there’s a column name ambiguity between the row alias and another table, the engine is supposed to favor the insert source. So for instance, in ON DUPLICATE KEY UPDATE tbl.name = name, the unqualified name is supposed to resolve to tbl.name, not tbl_new.name. Currently with my draft PR, this returns an ambiguous column error.

I’m currently working on both of these issues.

I apologize for the confusing error message: when we added support to the parser, we added a new error message that was supposed to be more clear:

error: insert row aliases are not currently supported; use the VALUES() function instead

If you’re not seeing that error message, that’s a bug.

Last time, we held off on implementing this because the name resolution rules were deceptively tricky, and we could point people to the old syntax as a workaround. But given that the old syntax is deprecated and people are migrating, we need to be able to handle the new synatx. I’m going to take another stab at it today.