Error with SQL DELETE statement with MariaDB

Question

I have a multi-database application that has a query that is giving me execution problems with MariaDB 10.6.12 (Windows).

The same query works perfectly when run against MYSQL 8.0.27

However, when it is run on MariaDB, I got Error: 1064-42000

Error: 1064-42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UT where not UT.ld_tag in (select distinct(B.ld_tag) from ld_tag B, ld_docum...'

Here is the full statement formatted

DELETE FROM ld_uniquetag UT
WHERE NOT UT.ld_tag IN (SELECT DISTINCT( B.ld_tag )
 FROM ld_tag B,
 ld_document C
 WHERE UT.ld_tenantid = B.ld_tenantid
 AND UT.ld_tag = B.ld_tag
 AND C.ld_id = B.ld_docid
 AND C.ld_deleted = 0)
 AND NOT UT.ld_tag IN (SELECT DISTINCT( D.ld_tag )
 FROM ld_foldertag D,
 ld_folder E
 WHERE UT.ld_tenantid = D.ld_tenantid
 AND UT.ld_tag = D.ld_tag
 AND E.ld_id = D.ld_folderid
 AND E.ld_deleted = 0)

Is there anyone who can help me to reformulate it and make it more portable?

• Rick James: Suggest reformulating to wither use NOT EXISTS or multi-table Delete with LEFT JOIN and IS NULL`.

Answer 1

The syntax for MariaDB does not allow an alias in a DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 
 FROM tbl_name [PARTITION (partition_list)]
...

Also note the following

• DISTINCT in an IN or EXISTS subquery makes no sense and should be removed, you are only looking for the first matching row anyway.
• Also DISTINCT is not a function, so don't use (). It operates over the whole resultset, not just the column you put in ().
• Comma-joins are long deprecated, you should use explicit JOIN syntax.
• Aliases like A B C aren't very useful for comprehending the query logic.

DELETE FROM ld_uniquetag
WHERE ld_uniquetag.ld_tag NOT IN (
 SELECT t.ld_tag
 FROM ld_tag t
 JOIN ld_document d ON d.ld_id = t.ld_docid
 WHERE ld_uniquetag.ld_tenantid = t.ld_tenantid
 AND ld_uniquetag.ld_tag = t.ld_tag
 AND d.ld_deleted = 0
)
 AND ld_uniquetag.ld_tag NOT IN (
 SELECT ft.ld_tag
 FROM ld_foldertag ft
 JOIN ld_folder f ON f.ld_id = ft.ld_folderid
 WHERE ld_uniquetag.ld_tenantid = ft.ld_tenantid
 AND ld_uniquetag.ld_tag = ft.ld_tag
 AND f.ld_deleted = 0
);

Answer 2

In MariaDB, you can use table aliases only if you use the multi-table DELETE syntax.

https://mariadb.com/kb/en/delete/ shows the syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 
 FROM tbl_name [PARTITION (partition_list)]
 [FOR PORTION OF period FROM expr1 TO expr2]
 [WHERE where_condition]
 ...

Notice no opportunity to put an alias after the table reference.

Whereas in MySQL's syntax https://dev.mysql.com/doc/refman/8.0/en/delete.html

Single-Table Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
 [PARTITION (partition_name [, partition_name] ...)]
 [WHERE where_condition]

You may optionally add a table alias. This was not an option in MySQL 5.7, and it is mentioned in What is New in MySQL 8.0 as a feature added in 8.0.16.

This goes to a broader point: MariaDB and MySQL are not compatible products. MariaDB started in 2010 as a fork of MySQL, and the two products have continued to change since then. They now have a lot of cases like the one above where they are incompatible, and this list is growing over time. You should think of them as totally distinct flavors of SQL database now, and not assume that they are drop-in replacements for each other.