Bug: Call to a member function getResult() on true on a correct query

[pippuccio76]

PHP Version

8.1, 8.3

CodeIgniter4 Version

4.6.0

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

apache

Database

8.0.41-0ubuntu0.24.04.1 via estensione PHP MySQLi

What happened?

Hi , when i try to launch query that does work on mysql client software (tested on adminer.php and Mysql Workbench) with 0 rows returned

Steps to Reproduce

            WITH tratta_di_riferimento as(
                SELECT 
                  mezzi_tratte.id_tratte_libere
                FROM 
                  tratte_libere as tratte 
                  JOIN tratte_libere_mezzi as mezzi_tratte ON tratte.id = mezzi_tratte.id_tratte_libere 
                  JOIN mezzi ON mezzi_tratte.id_mezzi = mezzi.id 
                WHERE 
                  TRUNCATE (
                    6363 * sqrt(
                      POW(
                        RADIANS("43.72283849999999") - RADIANS(tratte.latitudine_partenza), 
                        2
                      ) + POW(
                        RADIANS("10.4017581") - RADIANS(tratte.longitudine_partenza), 
                        2
                      )
                    ), 
                    3
                  ) < (tratte.raggio_ricerca_km + 80)
                  AND TRUNCATE (
                    6363 * sqrt(
                      POW(
                        RADIANS("41.8967068") - RADIANS(tratte.latitudine_arrivo), 
                        2
                      ) + POW(
                        RADIANS("12.4822025") - RADIANS(tratte.longitudine_arrivo), 
                        2
                      )
                    ), 
                    3
                  ) < (tratte.raggio_ricerca_km + 80)
                  AND mezzi.passeggeri >= "1" 
                  AND mezzi.bagagli >= "0" 
                  AND mezzi.deleted_at IS NULL 
                  AND mezzi_tratte.deleted_at IS NULL 
                  AND tratte.deleted_at IS NULL 
                ORDER BY 
                  mezzi_tratte.costo
                            LIMIT 1 
                            ),/*end tratta di riferimento [PRENDE L'ID DELLA TRATTA CON COSTO +BASSO]*/
              result_row as(
                SELECT 
                  mezzi_tratte.id, 
                  mezzi_tratte.id_tratte_libere, 
                  mezzi_tratte.id_mezzi, 
                  mezzi_tratte.costo as costo_tratta, 
                  mezzi.id as id_tabella_mezzi, 
                  mezzi.immagine, 
                  mezzi.nome, 
                  mezzi.descrizione as mezzi_descrizione, 
                  mezzi.passeggeri, 
                  mezzi.bagagli,                  
                  mezzi.costo_km_extra,
                  tratte.latitudine_partenza,
                  tratte.longitudine_partenza,
                  tratte.latitudine_arrivo,
                  tratte.longitudine_arrivo,
                  tratte.raggio_ricerca_km
                FROM 
                  tratte_libere as tratte 
                    JOIN tratta_di_riferimento on tratte.id = tratta_di_riferimento.id_tratte_libere
                  JOIN tratte_libere_mezzi as mezzi_tratte ON tratte.id = mezzi_tratte.id_tratte_libere 
                  JOIN mezzi ON mezzi_tratte.id_mezzi = mezzi.id 
                WHERE 
                  TRUNCATE (
                    6363 * sqrt(
                      POW(
                        RADIANS("43.72283849999999") - RADIANS(tratte.latitudine_partenza), 
                        2
                      ) + POW(
                        RADIANS("10.4017581") - RADIANS(tratte.longitudine_partenza), 
                        2
                      )
                    ), 
                    3
                  ) < (tratte.raggio_ricerca_km + 80)
                  AND TRUNCATE (
                    6363 * sqrt(
                      POW(
                        RADIANS("41.8967068") - RADIANS(tratte.latitudine_arrivo), 
                        2
                      ) + POW(
                        RADIANS("12.4822025") - RADIANS(tratte.longitudine_arrivo), 
                        2
                      )
                    ), 
                    3
                  ) < (tratte.raggio_ricerca_km + 80)
                  AND mezzi.passeggeri >= "1" 
                  AND mezzi.bagagli >= "0" 
                  AND mezzi.deleted_at IS NULL 
                  AND mezzi_tratte.deleted_at IS NULL 
                  AND tratte.deleted_at IS NULL 
                ORDER BY 
                  mezzi_tratte.costo),/*END RESULT ROW*/
                    result_row2 as(
                    select result_row.*, 
                    TRUNCATE (
                                        6363 * sqrt(
                                          POW(
                                            RADIANS("43.72283849999999") - RADIANS(latitudine_partenza), 
                                            2
                                          ) + POW(
                                            RADIANS("10.4017581") - RADIANS(longitudine_partenza), 
                                            2
                                          )
                                        ), 
                                        3
                                      ) +
                                   TRUNCATE (
                                        6363 * sqrt(
                                          POW(
                                            RADIANS("41.8967068") - RADIANS(latitudine_arrivo), 
                                            2
                                          ) + POW(
                                            RADIANS("12.4822025") - RADIANS(longitudine_arrivo), 
                                            2
                                          )
                                        ), 
                                        3
                                      )-raggio_ricerca_km as km_differenza_totale
                                      
                    from result_row)/*end result_row2*/
          
                SELECT  *,km_differenza_totale*costo_km_extra +costo_tratta as costo from result_row2;

Expected Output

0 rows returned

Anything else?

No response

[michalsn]

Important context (https://forum.codeigniter.com/showthread.php?tid=92467), the returned error:

Call to a member function getResult() on true

It seems that #8599 made changes to the isWriteType() method, which added support for WITH, but relies on the occurrence of RETURNING, which is not supported by all DB drivers - this causes false recognition of the query type.

In other words, if there is WITH without RETURNING the query is recognized as write type and returns bool value.

---

As a temporary workaround, you can use:

$db = db_connect();
$resultID = $db->simpleQuery($sql);
$result = new \CodeIgniter\Database\MySQLi\Result($db->connID, $resultID);
dd($result->getResult());

[ping-yee]

I wasn't able to reproduce the bug based on this:

In other words, if there is WITH without RETURNING the query is recognized as write type and returns bool value.

Step to reproduce

CREATE TABLE IF NOT EXISTS test_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col INT
);

INSERT INTO test_data (col) VALUES (1);

In Home.php

<?php

namespace App\Controllers;

use App\Models\UserModel;

class Home extends BaseController
{
    public function index()
    {
        $db = \Config\Database::connect();

        $sql = "
            WITH cte1 AS (
                SELECT col FROM test_data
            ),
            cte2 AS (
                SELECT * FROM cte1 WHERE col = 1
            )
            SELECT * FROM cte2
        ";

        $result = $db->query($sql);

        $data = $result->getResult();

        echo '<pre>';
        print_r($data);
        echo '</pre>';
    }
}

The output

Array
(
    [0] => stdClass Object
        (
            [col] => 1
        )
)

[michalsn]

@ping-yee Yes, you're right. IDK what I was thinking...

The (?!.*\sRETURNING\s) is a negative check ensuring "RETURNING" does not appear later in the query.

The real issue is the context of keywords (statement vs. function). Here the query uses TRUNCATE followed by a space within a CTE. It's a mathematical function, not a TRUNCATE TABLE statement, but the regex can’t differentiate.

[michalsn]

Do we have any regex guru here?

The (WITH\s.+(\s|[)]) group looks too permissive. The .+ stops at any space or parenthesis, potentially within a CTE’s subquery, rather than capturing the entire WITH clause up to the main query keyword.

Shouldn't we use (WITH\s+.*\)\s*)? It would greedily match any characters up to the last closing parenthesis ). This ensures the entire WITH clause, including all CTEs, is consumed.

[michalsn]

Okay, at this point any changes to the regex may not make sense, because it works fine. And what I'm thinking of (if correct) won't change anything anyway.

@pippuccio76 Please remove the space after TRUNCATE to make the regex recognize it as a function. So it should be TRUNCATE( instead of TRUNCATE (.