[#45848] - [5.4] Fix missing names quotes and wrong casing of column alias in libraries/src/Table/Nested.php
- Pending
- Medium
- Build: 5.3-dev
- # 45848
- Diff
- richard67:5.3-dev-fix-nested-table-2025-08-05
User tests: Successful: Unsuccessful:
Pull Request for Issue #45839 .
Summary of Changes
This pull request adds missing names quotes to the column alias "newPublished" of the subquery used in the "recursiveUpdatePublishedColumn" in the "Joomla\CMS\Table\Nested" table class and corrects casing for the later use of that column alias so that the query works without any error on any database environment, regardless of case-sensitivity of column names.
Testing Instructions
On MySQL or MariaDB it might need a special environment to be able to reproduce the issue, e.g. a database server where the "information_schema" database uses a case-sensitive collation.
You could do that by changing the collation of the "information_schema" database and convert all its tables to the same collation as it has now but without the "_ci" at the end so that it is case-insensitive.
But you should make a backup so you can later restore the original state as it is not recommended to change the character set and collation of the "information_schema" database.
Then you can try to reproduce the issue.
On PostgreSQL there was no error because the 2 mistakes fixed by this PR compensate each other:
- PostgreSQL converts all names to lowercase if they are not quoted, what was the case here with the missing names quotes at the first place.
- But when checking for names, PostgreSQL is still case-sensitive.
- Because of the 2nd mistake (lowercase spelling of the quoted name a few lines below) it works.
- If this PR would fix only one of the 2 mistakes, it would fail on PostgreSQL.
So on PostgreSQL you can only check if everything works as before.
Actual result BEFORE applying this Pull Request
See issue #45839 .
Expected result AFTER applying this Pull Request
No such error.
Link to documentations
Please select:
-
Documentation link for docs.joomla.org:
-
No documentation changes for docs.joomla.org needed
-
Pull Request link for manual.joomla.org:
-
No documentation changes for manual.joomla.org needed
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | Libraries |
| Title |
|
||||||
P.S.: The connection collation has absolutely nothing to do with it.
This pull request has been automatically rebased to 5.4-dev.
| Title |
|
||||||
| Labels |
Added:
bug
PR-5.4-dev
|
||
I've meanwhile found some information in MySQL documentation which could be helpful to identify an environment where the issue could be reproduced, or at least explain why it cannot be reproduced for most people:
- https://dev.mysql.com/doc/refman/8.4/en/charset-collation-information-schema.html explains that it depends on the database server's OS and the "lower_case_table_names" system variable.
- https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_lower_case_table_names explains that system variable and under which conditions it is set to which value.
I have tzo check that deeper to develop a scenario for testing this PR.
@catwired As you had the original issue: Could you report back which OS your database server is running on, and could you check and report back the value of the "lower_case_table_names" system variable? The latter can be obtained e.g. in phpMyAdmin with the SQL statement SELECT @@GLOBAL.lower_case_table_names;;
I have not managed to duplicate the error even after creating a database from the command line with case-sensitive collation:
CREATE DATABASE joomla53 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;When Joomla 5.3.3-dev is installed, the database connection_collation is automatically set to be case-insensitive:
I am guessing the server variable collation_connection should somehow be set in the command line session before creating the database, just to make the operations involving column aliases case-sensitive.