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?

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.