I have tested this item ✅ successfully on 4c65881

Was not easy to test because

1. Had to apply PR #24747 to be able to have installation on Postgresql for the test.
2. Patchtester does not work with Postgresql
3. Had to apply changes of this PR manually because branch or this PR needs a rebase to current 4.0-dev.
   But at the end test has shown that this PR here corrects error as described.

   ---

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

I have tested this item ✅ successfully on 4c65881

Was not easy to test because

1. Had to apply PR #24747 to be able to have installation on Postgresql for the test.
2. Patchtester does not work with Postgresql
3. Had to apply changes of this PR manually because branch or this PR needs a rebase to current 4.0-dev.
   But at the end test has shown that this PR here corrects error as described.

   ---

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

I have tested this item ✅ successfully on 4c65881

@richard67 , I have modified patchtester to work with pgsql in J4:
com_patchtester.zip

I make a PR for the next release:
joomla-extensions/patchtester#220

Status "Ready To Commit".

This is the exact same line as in staging. Why is this an issue in J4 and not in staging :/

don't forget we use PDO postgresql in 4

This https://stackoverflow.com/questions/31740105/select-with-inner-join-using-postgresql-and-pdo suggests that PDO supports ON as a keyword. And using the second param of the join function allows specifying an ON condition. So we need to dig a bit further into this and figure out exactly where we are failing here because it must be something more specific than just an ON statement https://github.com/joomla-framework/database/blob/2.0-dev/src/DatabaseQuery.php#L1168

so let me clarify a bit:

the query in staging 3.x is quite different from what we have here in 4.x

this is 3.x

UPDATE tre_content
INNER JOIN (
     SELECT * FROM (
         SELECT (
             SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS new_ordering,`id` AS `pk__0`
FROM tre_content
WHERE catid = 2 AND state >= 0 AND `ordering` >= 0
ORDER BY `ordering`
) w) AS sq ON `id` = sq.`pk__0`
SET `ordering` = sq.new_ordering
WHERE catid = 2 AND state >= 0 AND `ordering` >= 0

this is 4.x before patch

UPDATE quattro_content
SET "ordering" = sq.new_ordering
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY "ordering") AS new_ordering,"id" AS "pk__0"
	FROM quattro_content
	WHERE catid = 2 AND state >= 0 AND "ordering" >= 0) AS sq ON "id" = sq."pk__0"
WHERE catid = 2 AND state >= 0 AND "ordering" >= 0"

this is 4.x with patch

UPDATE quattro_content
SET "ordering" = sq.new_ordering
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY "ordering") AS new_ordering,"id" AS "pk__0"
FROM quattro_content
WHERE catid = 2 AND state >= 0 AND "ordering" >= 0) AS sq 
WHERE catid = 2 AND state >= 0 AND "ordering" >= 0 AND "id" = sq."pk__0"

so the query in 4.x is using and update with join on a temporay table (look at the FROM clause) and doesn't like the ON clause instead need the AND clause

That seems very weird. How come in 4.x we don't have an inner join in the query at all. Given that's the command we're modifying in the code. Are you sure that's the right query?

pretty sure i've made my homework, but another couple of eyes checking it's always better

@alikon Last bigger change on that query was PR #13505 . This is shown in the history and blame for that file on both the staging and the 4.0-dev branch. And when comparing the sql statement related code on function reorder between 4.0-dev and staging, I also see no difference, only differences to other stuff. When you checked 3.x, did you maybe check something older than 3.7?

from 3.x to 4.x something has changed ? .... maybe the framework ... don't know,
btw, i've already seen some strange behavior with pdo pgsql

@alikon Hmm, I just see that with our PR applied, the variable $subquery will not be used anymore in that function. So either it has to be removed, or there is really something wrong.

it is used here https://github.com/joomla/joomla-cms/pull/24148/files#diff-f70cde2615a113ab8ff64af30ef43f5fR1603

@alikon Stupid me ;-)

Query in 4.0-dev without this PR, MySQL:

UPDATE j4ux0_content
INNER JOIN (
SELECT * FROM (
SELECT (SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS new_ordering,id AS pk__0
FROM j4ux0_content
WHERE catid = 2 AND state >= 0 AND ordering >= 0
ORDER BY ordering
) w) AS sq ON id = sq.pk__0
SET ordering = sq.new_ordering
WHERE catid = 2 AND state >= 0 AND ordering >= 0

Query in 4.0-dev with this PR, MySQL:

UPDATE j4ux0_content
INNER JOIN (
SELECT * FROM (
SELECT (SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS new_ordering,id AS pk__0
FROM j4ux0_content
WHERE catid = 2 AND state >= 0 AND ordering >= 0
ORDER BY ordering
) w) AS sq
SET ordering = sq.new_ordering
WHERE catid = 2 AND state >= 0 AND ordering >= 0 AND id = sq.pk__0

I will later post the same for PDO PgSQL.

Output produced with error_log($query->dump()); before the $this->_db->setQuery($query);.

Query in 4.0-dev without this PR, PDO PgSQL:

UPDATE j4ux0_content
SET "ordering" = sq.new_ordering
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY "ordering") AS new_ordering,"id" AS "pk__0"
FROM j4ux0_content
WHERE catid = 2 AND state >= 0 AND "ordering" >= 0) AS sq ON "id" = sq."pk__0"
WHERE catid = 2 AND state >= 0 AND "ordering" >= 0

=> It seems the join is changed to something else for PDO PgSQL, and without a join, any "ON" is a syntax error.

So the question is what removes that join. Is there something wrong with the db driver? This has to be fixed, and then this PR here might be obsolete.

Also the syntax UPDATE table SET column=value FROM ( ... ) AS x ON y ... seems wrong to me. I've never heard about UPDATE ... FROM ..., I only know INSERT INTO ... SELECT FROM ....

Maybe "PDO" just needs to be renamed to "PDU", "Please Don't Use". ;-)

I've just checked that the queries with MySQL (PDO) are the same as with MySQLi. So the problem with the join being changed to something else appears only with PostgreSQL (PDO).

https://www.postgresql.org/docs/current/sql-update.html
RTC.

You are right @richard67, there is not from with MySQL:
https://dev.mysql.com/doc/refman/8.0/en/update.html

Slightly reluctantly merging this. Still seems odd to me. But if testing proves it ok. Will do for now