In can confirm that it's a bug specific for 5.3-dev which was introduced with 5.3.0-alpha2. 5.3.0-alpha1 and 5.2-dev are ok.

By review I can confirm that this PR is right, the table alias is not needed in the DELETE statement, and whereIn can be used here.

I think the bug is severe enough to set the "Release Blocker" label.

Test OK article can be saved now.

I have tested this item ✅ successfully on 287e679

Test OK article can be saved now.

Unfortunately I cannot reproduce the issue, not with MySQL 8.0 nor PostgreSQL nor MariaDB 10.11.

As the error is in the removeOrphanNodes method of the Taxonomy, it might need some orphan node for the error happening. @Hackwar Do you know how to produce some taxonomy orphan nodes in com_finder?

The simplest solution would be to delete a row from the #__finder_links table and then to run the maintenance task from the Index view. The link entry should of course have a taxonomy related to it and be the only entry which has this taxonomy. For example an article in a category and this would be the only article in that category.

Meanwhile I've tested the DELETE statement as it is without this PR for some not existing IDs in phpMyAdmin and for Postgres in phpPgAdmin:

DELETE FROM `#__finder_taxonomy` AS `t` WHERE `t`.`id` IN (97,98,99);

(in phpPgAdmin with PostgreSQL of course with the right name quotes).

Results:

• On MySQL 8 and PostgreSQL the statement works.
• On MariaDB 10.11.7 and MariaDB 10.6.15 I get an SQL syntax error.

The fixed statement from this PR works on all.

I still cannot reproduce the issue on MariaDB on a "normal" Joomla 5.3-alpha3.

The reason for that is that the first query in the removeOrphanNodes method will never return any records because of the AND m.link_id IS NULL condition in the WHERE clause.

I don't know yet since which Joomla version, but from memory I would say it's 4.0.0, there is a NOT NULL constraint on the link_id column of the #__finder_taxonomy_map.

So the only explanation which I have right now for the issue is that the site has a long update history and some old records in the #__finder_taxonomy_map table which have a NULL value for the link_id.

But anyway, regarding the SQL syntax the fix from this PR is right.

@Hackwar Am I on the right track, or am I missing something?

the DELETE syntax doesn't need the alias nor having it is wrong strictly sql speaking
btw, if there are some strange behaviours with some "strange" MariaDB versions this PR is correct anyway

I have tested this item ✅ successfully on 287e679

I was able to reproduce the issue with MariaDB 10.11.7, but only with some kind of a hack.

I have created a new category and a new article so that article was the only one in that category.

Then I have investigated the record in the #__finder_taxonomy_map table which belongs to that article and have deleted that record, so that the following SQL statement returned one record as result:

SELECT `t`.`id`
  FROM `#__finder_taxonomy` AS `t`
  LEFT JOIN `#__finder_taxonomy_map` AS `m` ON `m`.`node_id` = `t`.`id`
 WHERE `t`.`parent_id` > 1
   AND `t`.`lft` + 1 = `t`.`rgt`
   AND `m`.`link_id` IS NULL;

Then I have tried to change and save any other article.

Without this PR it failed as described.

With this PR it succeeded. After that, the above query did not return any result, and Smart Search indexing and index optimization still work.

RTC