Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
pippuccio76 opened this issue Feb 17, 2025 · 5 comments
Closed

Comments

@pippuccio76
Copy link

pippuccio76 commented Feb 17, 2025

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

@pippuccio76 pippuccio76 added the bug Verified issues on the current code behavior or pull requests that will fix them label Feb 17, 2025
@michalsn
Copy link
Member

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
Copy link
Contributor

ping-yee commented Feb 24, 2025

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
Copy link
Member

@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
Copy link
Member

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 michalsn removed the bug Verified issues on the current code behavior or pull requests that will fix them label Feb 25, 2025
@michalsn
Copy link
Member

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 (.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants