Invalid SQL created by removeOrphanNodes function

[He-Man321]

Steps to reproduce the issue

1. Log in to /Administrator as Admin
2. Go to the Articles and create a new one. Give it a title and Click Save & Close.
3. Open the same article again and click Save & Close again.

Expected result

No error.

Actual result

Error
Save failed with the following error: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'abc_finder_taxonomy' for update in FROM clause

System information (as much as possible)

Tested in joomla 3.8.8 and 3.8.12 running on Windows 2012R2 with MySQL 5.7.9 and PHP 7.2.2.

Additional comments

This only happens when the Smart Search plug-in is enabled.

I have established that the cause of this is the removeOrphanNodes() function in administrator\components\com_finder\helpers\indexer\taxonomy.php. Back in 3.4.5 this function deleted from the table without using a subquery but at some point after that (and before 3.7.5 I am told) it was changed to use the subquery. However, https://dev.mysql.com/doc/refman/5.6/en/update.html confirms that in MySQL "You cannot update a table and select from the same table in a subquery". This causes the query to fail on a MySQL system.
I have discussed this in more detail on the forum here: https://forum.joomla.org/viewtopic.php?t=965486

[csthomas]

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

[alikon]

@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....

[csthomas]

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

[He-Man321]

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.

[alikon]

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 😢

[He-Man321]

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.

[csthomas]

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.

[alikon]

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

[He-Man321]

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

[He-Man321]

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...

[He-Man321]

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?

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/22231.}

[richard67]

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.

[richard67]

Am working on a fix.

[richard67]

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

[richard67]

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

[richard67]

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.

[richard67]

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