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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: tropicano at ukr dot net
New email:
PHP Version: OS:

 

 [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: Fri Dec 27 04:01:29 2024 UTC