php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #81245 Phantom rowset after error in multi-query
Submitted: 2021-07-11 12:27 UTC Modified: 2021-07-11 16:28 UTC
From: tropicano at ukr dot net Assigned:
Status: Closed Package: PDO MySQL
PHP Version: 7.4.21 OS: Windows 10, Linux
Private report: No CVE-ID: None
 [2021-07-11 12:27 UTC] tropicano at ukr dot net
Description:
------------
Phantom rowset after error in multi-query:

SELECT 123;
SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);
SELECT 456

Test script:
---------------
<php
    $db = new PDO("mysql:host=$host", $user, $password, [PDO::MYSQL_ATTR_MULTI_STATEMENTS => TRUE, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => FALSE, PDO::ATTR_PERSISTENT => FALSE, PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT]);

    $db->exec('SET profiling_history_size=100, profiling=1');

    $sql = "SELECT 123;     SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);     SELECT 456";
    $qr = $db->query($sql);

    echo "Query #1 (with phantom rowset #2)\n--------------------------------------------------\n$sql\n";

    if ($qr)
    {
        // first rowset for 'SELECT 123'
        echo "----------------------------rowset # 1-----------------------------------------------------------------------------------\n";
        echo 'fetch(PDO::FETCH_NUM): '; var_dump($qr->fetch(PDO::FETCH_NUM));
        print_r($qr->errorInfo());

        // second rowset for 'SELECT IF(1, (SELECT 1 UNION SELECT 2), 1)'
        echo "----------------------------rowset # 2 (phantom with no error and empty results!!!)--------------------------------------\n";
        echo 'nextRowset(): '; var_dump($qr->nextRowset());
        echo 'fetch(PDO::FETCH_NUM): '; var_dump($qr->fetch(PDO::FETCH_NUM));
        print_r($qr->errorInfo());

        // third rowset for 'SELECT 456'
        echo "----------rowset # 3 (WOW! I see lost error for second rowset which should be after the first call nextRowset())---------\n";
        echo 'nextRowset(): '; var_dump($qr->nextRowset());
        echo 'fetch(PDO::FETCH_NUM): '; var_dump($qr->fetch(PDO::FETCH_NUM));
        print_r($qr->errorInfo());
        echo "-------------------------------------------------------------------------------------------------------------------------\n";
    }
    else
        print_r($db->errorInfo());    

    $sql = "SELECT 123;     SELECT ERROR;       SELECT 456";
    $qr = $db->query($sql);       
    
    echo "\nQuery #2 (with handling AS EXPECTED)\n--------------------------------------------------\n$sql\n";

    //$qr = $db->query("SELECT 123;
    //                  SELECT (SELECT 1 UNION SELECT 2);
    //                  SELECT 456");        

    if ($qr)
    {
        // first rowset for 'SELECT 123'
        echo "----------------------------rowset # 1-----------------------------------------------------------------------------------\n";
        echo 'fetch(PDO::FETCH_NUM): '; var_dump($qr->fetch(PDO::FETCH_NUM));
        print_r($qr->errorInfo());

        // second rowset for 'SELECT ERROR'
        echo "----------------------------rowset # 2 (with error info AS EXPECTED)-----------------------------------------------------------\n";
        echo 'nextRowset(): '; var_dump($qr->nextRowset());
        echo 'fetch(PDO::FETCH_NUM): '; var_dump($qr->fetch(PDO::FETCH_NUM));
        print_r($qr->errorInfo());
        echo "-------------------------------------------------------------------------------------------------------------------------\n";
    }
    else
        print_r($db->errorInfo()); 

    echo "\nProfiling queries:\n-------------------------------------------------------\n";
        
    $qr = $db->query('SHOW PROFILES');
    while ($profile = $qr->fetch(PDO::FETCH_OBJ))
        print_r($profile);

Expected result:
----------------
Query #1
--------------------------------------------------------
SELECT 123;     SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);     SELECT 456
----------------------------rowset # 1-----------------------------------------------------------------------------------
fetch(PDO::FETCH_NUM): array(1) {
  [0]=>
  string(3) "123"
}
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
----------------------------rowset # 2 --------------------------------------
nextRowset(): bool(false)
fetch(PDO::FETCH_NUM): bool(false)
Array
(
    [0] => 00000
    [1] => 1242
    [2] => Subquery returns more than 1 row
)
----------rowset # 3 ---------------
nextRowset(): bool(false)
fetch(PDO::FETCH_NUM): bool(false)
Array
(
    [0] => 00000
    [1] => 1242
    [2] => Subquery returns more than 1 row
)
-------------------------------------------------------------------------------------------------------------------------------

Query #2 (with handling as expected)
--------------------------------------------------------
SELECT 123;     SELECT ERROR;       SELECT 456
----------------------------rowset # 1-----------------------------------------------------------------------------------------
fetch(PDO::FETCH_NUM): array(1) {
  [0]=>
  string(3) "123"
}
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
----------------------------rowset # 2 (with error info as expected)-----------------------------------------------------------
nextRowset(): bool(false)
fetch(PDO::FETCH_NUM): bool(false)
Array
(
    [0] => 00000
    [1] => 1054
    [2] => Unknown column 'ERROR' in 'field list'
)
-------------------------------------------------------------------------------------------------------------------------------

Profiling queries:
-------------------------------------------------------------
stdClass Object
(
    [Query_ID] => 1
    [Duration] => 0.00045425
    [Query] => SELECT 123;     SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);     SELECT 456
)
stdClass Object
(
    [Query_ID] => 2
    [Duration] => 0.00063900
    [Query] => SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);     SELECT 456
)
stdClass Object
(
    [Query_ID] => 3
    [Duration] => 0.00035700
    [Query] => SELECT 123;     SELECT ERROR;       SELECT 456
)
stdClass Object
(
    [Query_ID] => 4
    [Duration] => 0.00016400
    [Query] => SELECT ERROR;       SELECT 456
)

Actual result:
--------------
Query #1 (with phantom rowset #2)
--------------------------------------------------------
SELECT 123;     SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);     SELECT 456
----------------------------rowset # 1-----------------------------------------------------------------------------------
fetch(PDO::FETCH_NUM): array(1) {
  [0]=>
  string(3) "123"
}
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
----------------------------rowset # 2 (phantom with no error and empty results!!!)--------------------------------------
nextRowset(): bool(true)
fetch(PDO::FETCH_NUM): bool(false)
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
----------rowset # 3 (WOW! I see lost error for second rowset which should be after the first call nextRowset())---------
nextRowset(): bool(false)
fetch(PDO::FETCH_NUM): bool(false)
Array
(
    [0] => 00000
    [1] => 1242
    [2] => Subquery returns more than 1 row
)
-------------------------------------------------------------------------------------------------------------------------

Query #2 (with handling as expected)
--------------------------------------------------------
SELECT 123;     SELECT ERROR;       SELECT 456
----------------------------rowset # 1-----------------------------------------------------------------------------------
fetch(PDO::FETCH_NUM): array(1) {
  [0]=>
  string(3) "123"
}
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
----------------------------rowset # 2 (with error info as expected)-----------------------------------------------------
nextRowset(): bool(false)
fetch(PDO::FETCH_NUM): bool(false)
Array
(
    [0] => 00000
    [1] => 1054
    [2] => Unknown column 'ERROR' in 'field list'
)
-------------------------------------------------------------------------------------------------------------------------

Profiling queries:
-------------------------------------------------------------
stdClass Object
(
    [Query_ID] => 1
    [Duration] => 0.00045425
    [Query] => SELECT 123;     SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);     SELECT 456
)
stdClass Object
(
    [Query_ID] => 2
    [Duration] => 0.00063900
    [Query] => SELECT IF(1, (SELECT 1 UNION SELECT 2), 0);     SELECT 456
)
stdClass Object
(
    [Query_ID] => 3
    [Duration] => 0.00035700
    [Query] => SELECT 123;     SELECT ERROR;       SELECT 456
)
stdClass Object
(
    [Query_ID] => 4
    [Duration] => 0.00016400
    [Query] => SELECT ERROR;       SELECT 456
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-07-11 16:28 UTC] tropicano at ukr dot net
-Status: Open +Status: Closed
 [2021-07-11 16:28 UTC] tropicano at ukr dot net
I think is MySQL side problem.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Dec 26 14:01:30 2024 UTC