Bug: Incorrect processing of the function in OrderBy

[avegacms]

PHP Version

8.2

CodeIgniter4 Version

4.5.5

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

apache

Database

PostgreSQL 15.8

What happened?

when writing a query (in PostgreSQL), it became necessary to sort with the TO_CHAR function, but we get an error at the output: ASC is applied twice

Steps to Reproduce

$this->builder()->select(
                    [
                        'COUNT(lottery_coupons.id) AS regCoupons',
                        "TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD HH24:00:00') AS hourInterval"
                    ]
                )->where(['lottery_coupons.is_reg' => 1])
                    ->groupStart()
                    ->where("lottery_coupons.updated_at::time BETWEEN '09:00:00'::time AND '20:00:00'::time",escape: false)
                    ->groupEnd()
                    ->groupBy("hourInterval")
                    ->orderBy("hourInterval", 'ASC');

                $result = $this->asArray()->findAll();

Expected Output

SELECT COUNT(lottery_coupons.id) AS regCoupons, TO_CHAR(lottery_coupons.updated_at, '%Y-%m-%d %H:00:00') AS hourInterval FROM "lottery_coupons" WHERE "lottery_coupons"."is_reg" = 1 AND ( "lottery_coupons"."updated_at" BETWEEN TO_TIMESTAMP(TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD 09:00:00'), 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP(TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD 20:00:00'), 'YYYY-MM-DD HH24:MI:SS') ) GROUP BY TO_CHAR(lottery_coupons.updated_at, "%Y-%m-%d %H:00:00") ORDER BY TO_CHAR(lottery_coupons.updated_at ASC, 'YYYY-MM-DD HH24:00:00') ASC

Anything else?

No response

[kenjis]

but we get an error at the output: ASC is applied twice

What do you mean? What is the exact error message?

[avegacms]

but we get an error at the output: ASC is applied twice

What do you mean? What is the exact error message?

The following error is written in the logs and an extra ASC is added

Problem in this code vendor/codeigniter4/framework/system/Database/BaseBuilder.php line 1486

[kenjis]

The first parameter contains the name of the column you would like to order by. https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#orderby

Do you think the following is the name of the column? "TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD HH24:00:00')"

[avegacms]

The first parameter contains the name of the column you would like to order by. https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#orderby

Do you think the following is the name of the column? "TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD HH24:00:00')"

es, I had to use the TO_CHAR function(lottery_coupons.updated_at, 'YYYY-MM-DD HH24:00:00') instead of an alias, because the error "pg_query(): Query failed: ERROR: column" hourInterval" does not exist LINE 8: ORDER BY "hourInterval" ASC"

- log - query

[kenjis]

Could you paste text instead of screenshots? Screenshots are difficult to read.

[avegacms]

Could you paste text instead of screenshots? Screenshots are difficult to read. Logs:

CRITICAL - 2024-10-03 11:05:19 --> [Caused by] ErrorException: pg_query(): Query failed: ERROR:  column "hourInterval" does not exist
LINE 8: ORDER BY "hourInterval" ASC
                 ^
in SYSTEMPATH/Database/Postgre/Connection.php on line 204.
 1 [internal function]: CodeIgniter\Debug\Exceptions->errorHandler(2, 'pg_query(): Query failed: ERROR:  column "hourInterval" does not exist
LINE 8: ORDER BY "hourInterval" ASC
                 ^', '/shared/httpd/lotteryapi/vendor/codeigniter4/framework/system/Database/Postgre/Connection.php', 204)
 2 SYSTEMPATH/Database/Postgre/Connection.php(204): pg_query(Object(PgSql\Connection), 'SELECT COUNT(lottery_coupons.id) AS regCoupons, TO_CHAR(lottery_coupons.updated_at, \'YYYY-MM-DD HH24:00:00\') AS hourInterval
FROM "lottery_coupons"
WHERE "lottery_coupons"."is_reg" = 1
AND   (
lottery_coupons.updated_at::time BETWEEN \'09:00:00\'::time AND \'20:00:00\'::time
 )
GROUP BY "hourInterval"
ORDER BY "hourInterval" ASC')
 3 SYSTEMPATH/Database/BaseConnection.php(734): CodeIgniter\Database\Postgre\Connection->execute('SELECT COUNT(lottery_coupons.id) AS regCoupons, TO_CHAR(lottery_coupons.updated_at, \'YYYY-MM-DD HH24:00:00\') AS hourInterval
FROM "lottery_coupons"
WHERE "lottery_coupons"."is_reg" = 1
AND   (
lottery_coupons.updated_at::time BETWEEN \'09:00:00\'::time AND \'20:00:00\'::time
 )
GROUP BY "hourInterval"
ORDER BY "hourInterval" ASC')
 4 SYSTEMPATH/Database/BaseConnection.php(648): CodeIgniter\Database\BaseConnection->simpleQuery('SELECT COUNT(lottery_coupons.id) AS regCoupons, TO_CHAR(lottery_coupons.updated_at, \'YYYY-MM-DD HH24:00:00\') AS hourInterval
FROM "lottery_coupons"
WHERE "lottery_coupons"."is_reg" = 1
AND   (
lottery_coupons.updated_at::time BETWEEN \'09:00:00\'::time AND \'20:00:00\'::time
 )
GROUP BY "hourInterval"
ORDER BY "hourInterval" ASC')
 5 SYSTEMPATH/Database/BaseBuilder.php(1644): CodeIgniter\Database\BaseConnection->query('SELECT COUNT(lottery_coupons.id) AS regCoupons, TO_CHAR(lottery_coupons.updated_at, \'YYYY-MM-DD HH24:00:00\') AS hourInterval
FROM "lottery_coupons"
WHERE "lottery_coupons"."is_reg" = :lottery_coupons.is_reg:
AND   (
lottery_coupons.updated_at::time BETWEEN \'09:00:00\'::time AND \'20:00:00\'::time
 )
GROUP BY "hourInterval"
ORDER BY "hourInterval" ASC', [...], false)

query

$this->builder()->select(
                    [
                        'COUNT(lottery_coupons.id) AS regCoupons',
                        "TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD HH24:00:00') AS hourInterval"
                    ]
                )->where(['lottery_coupons.is_reg' => 1])
                    ->groupStart()
                    ->where("lottery_coupons.updated_at::time BETWEEN '09:00:00'::time AND '20:00:00'::time",escape: false)
                    ->groupEnd()
                    ->groupBy("hourInterval")
                    ->orderBy("hourInterval", 'ASC');

                $result = $this->asArray()->findAll();

[kenjis]

In my understanding, when using Query Builder, if you do not specify simple column names, try to set $escape to false (and surely escape by yourself).

If an alias cannot be used in group by, it is an issue (or specification) in PostgreSQL. Not in the framework.

[avegacms]

In my understanding, when using Query Builder, if you do not specify simple column names, try to set $escape to false (and surely escape by yourself).

If an alias cannot be used in group by, it is an issue (or specification) in PostgreSQL. Not in the framework.

Thank you for the answer. In this form, the query works.

$this->builder()->select(
                    [
                        'COUNT(lottery_coupons.id) AS regCoupons',
                        "TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD HH24:00:00') AS hourInterval"
                    ]
                )->where(['lottery_coupons.is_reg' => 1])
                    ->groupStart()
                    ->where("lottery_coupons.updated_at::time BETWEEN '09:00:00'::time AND '20:00:00'::time",escape: false)
                    ->groupEnd()
                    ->groupBy('hourInterval', escape: false)
                    ->orderBy('hourInterval', 'ASC', false);

                $result = $this->asArray()->findAll();

But it is very strange that for some reason you need to disable escape for the groupBy and OrderBy methods - it seems to me that CI - PostgreSQL has a problem with escaping them

[kenjis]

As you say, it is a bit strange. To be honest, I don't know why the following query causes the error column "hourInterval" does not exist.

SELECT COUNT(lottery_coupons.id) AS regCoupons, TO_CHAR(lottery_coupons.updated_at, 'YYYY-MM-DD HH24:00:00') AS hourInterval
FROM "lottery_coupons"
WHERE "lottery_coupons"."is_reg" = 1
AND   (
lottery_coupons.updated_at::time BETWEEN '09:00:00'::time AND '20:00:00'::time
 )
GROUP BY "hourInterval"
ORDER BY "hourInterval" ASC

If you find the cause, feel free to send a PR to fix the issue. https://github.com/codeigniter4/CodeIgniter4/blob/develop/contributing/pull_request.md

[pm-michael]

In Postgres, hourInterval is a distinct column from "hourInterval".

I think this should work:

$this->builder()->select(
                    [
                        'COUNT(lottery_coupons.id) AS "regCoupons"',
                        'TO_CHAR(lottery_coupons.updated_at, \'YYYY-MM-DD HH24:00:00\') AS "hourInterval"'
                    ]
                )->where(['lottery_coupons.is_reg' => 1])
                    ->groupStart()
                    ->where("lottery_coupons.updated_at::time BETWEEN '09:00:00'::time AND '20:00:00'::time",escape: false)
                    ->groupEnd()
                    ->groupBy("hourInterval")
                    ->orderBy("hourInterval", 'ASC');

                $result = $this->asArray()->findAll();

[tscontenna]

In Postgres, non-quoted identifiers are automatically converted lowercase internally. You can see this behavior with this :

# SELECT  1  AS  "A", 2 AS  B, 3 AS c;
 A | b | c        -- <--- b is not "B"
---+---+---
 1 | 2 | 3
(1 row)


SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY "A" ;  -- success
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY "a" ;  -- ERROR:  column "a" does not exist
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY  a  ; -- ERROR:  column "a" does not exist
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY  A  ; -- ERROR:  column "a" does not exist

SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY "B" ; -- ERROR:  column "B" does not exist
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY "b" ; -- success
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY  B  ; -- success
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY  b  ; -- success

SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY "C" ; -- ERROR:  column "C" does not exist
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY "c" ; -- success
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY  C  ; -- success
SELECT 1 AS "A", 2 AS  B, 3  AS c   ORDER BY  c  ; -- success

So I recommend using snake-case (like hour_interval ) instead of camel-case as any identifiers (table or column name, alias, etc.).