@alikon The problem start at Joomla 3.7.0 (#12313)

@csthomas maybe it depends from the mysql version on 5.7 works without issue

from the op text it's ambiguos the issue arise on 5.6 or in 5.7 more detail are needed....

I assume this is something like https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/14302701

Yes, this isn't a MySQL version issue; there are Stack Overflow pages with people moaning about MySQL not allowing updates to tables in a where subquery going back to 2006 and beyond. It just isn't something MySQL supports, so I suspect this will happen with all versions.

on Mysql 5.7.23 and on Maria DB 10.3.2 it works
so it is a version issue
but this apart , this is a real issue cause for https://docs.joomla.org/Technical_requirements
we still support 5.1. + on 3.x 😢

Please can you try and execute this in a query window (I believe it is safe, although you might like to check before running it and you will have to change the table prefixes):

DELETE FROM abc_finder_taxonomy WHERE id IN (
SELECT id FROM (
SELECT t.id FROM abc_finder_taxonomy AS t
LEFT JOIN abc_finder_taxonomy_map AS m ON m.node_id=t.id
WHERE t.parent_id > 1 AND m.link_id IS NULL
) temp)

My understanding is that this shouldn't work on any version of MySQL.

I tested above query and I do not get any error on 10.1.36-MariaDB and mysql 5.7 but
I do not have any rows to delete. Maybe if there is some rows then error occurs.

can confirm
@He-Man321 what mysql version are you running exactly ??

I am running 5.7.9 but maybe yours is working because you are using MariaDB and I am just using plain old MySQL?

OK, the plot thickens! I have written this:
CREATE TABLE IF NOT EXISTS Test (
test_id INT AUTO_INCREMENT,
txt VARCHAR(50) NOT NULL,
PRIMARY KEY (test_id)
) ENGINE=INNODB;
DELETE FROM test WHERE test_id IN (
SELECT test_id FROM (
SELECT t.test_id FROM test AS t
WHERE t.test_id > 1
) temp)

And on my 5.7.9 system it fails, so I installed a 5.7.23 system on a test machine and it works fine! So it seems that MySQL does now allow this but it didn't in v5.7.9...

A final update on this...
It appears that somewhere between MySQL 5.7.9 and 5.7.23 they have changed it so that you now can run UPDATE queries with the updated table in a subquery in the WHERE clause!

So I can confirm this issue is resolved by upgrading to MySQL 5.7.23.

I am not sure how many people may be stuck on older versions of MySQL though, so perhaps it might be worth re-structuring the query in the taxonomy.php file so that it will run on all versions?

I can confirm that there is a problem with particular versions of MySQL 5.7 only, see #34763 . Will check if the issue here still persists on such a version (5.7.14) since there have been made changes on com_finder meanwhile.

Am working on a fix.

Hmm, I can't reproduce the issue with MySQL 5.7.14. Have to try later with the 5.7.9 reported here.

Or is there something special to be do to run into the removeOrphanNodes function?

Now it becomes weird: I could reproduce the issue here with MySQL 5.7.9, same version as reported by the issuer, but I could not reproduce it with version 5.9.14 which had the same SQL error on another delete statement with subquery on the same table which I fixed for J4 with #34763 .

So now I can work on a fix.

Or we exclude MySQL 5.7.9 from the list of supported versions, like we do it already with particular buggy PHP versions, because those and MySQL 5.7.9 are normally update by hosting providers or the Linux package providers to newer versions. But I think we should do that only if it is not possible to fix it.

Closing as having a pull request. Please test #34818 . Thanks in advance.