Bug: Query Builder breaks with SQL function LENGTH() and column name "row"

[desttools]

Describe the bug
When trying to use the LENGTH() SQL function in the query builder with certain column names, I get mysqli_sql_exception #1064

This errors:
$builder->orderBy('LENGTH(row)', 'DESC');

However it does work when you put backticks around the column name.

The issue seems to not occur on different column names, so presumably certain names are reserved or are being parsed differently.

Maybe it's not a bug, but we may need something in the docs about needing the backquotes for certain column names. It's also possible that this is a MySQL issue, not a CI issue, but I can't tell.

CodeIgniter 4 version
Tested in Version 4.1.1

Affected module(s)
Query Builder

Context

• OS: Windows
• Web server Apache/2.4.41 (Ubuntu)
• PHP 7.4.3
• MySQL 8.0.25-0ubuntu0.20.04.1 - (Ubuntu)

[paulbalandan]

ROW and ROWS became reserved MySQL keywords as of v8.0.2 (covered by your SQL version.). In this case, the MySQL documentation states that you should avoid using the identified reserved keywords as the names of your tables, columns, or any identifiers. If that is not possible, you should escape the names of the reserved keywords using backticks.

For the list of reserved keywords in MySQL 8.0, see here. Those names with (R) are reserved as identifiers so you need to backtick them if you'll use them.

[desttools]

Thanks for looking into that and thanks for sharing that info, John Paul.