Bug #81245 [Opn->Csd]: Phantom rowset after error in multi-query
| From: | tropicano at ukr dot net | Date: | Sun, 11 Jul 2021 16:28:38 +0000 |
| Subject: | Bug #81245 [Opn->Csd]: Phantom rowset after error in multi-query | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-234956@lists.php.net to get a copy of this message | ||
Edit report at https://bugs.php.net/bug.php?id=81245&edit=1
ID: 81245
User updated by: tropicano at ukr dot net
Reported by: tropicano at ukr dot net
Summary: Phantom rowset after error in multi-query
-Status: Open
+Status: Closed
Type: Bug
Package: PDO MySQL
Operating System: Windows 10, Linux
PHP Version: 7.4.21
Block user comment: N
Private report: N
New Comment:
I think is MySQL side problem.
Previous Comments:
------------------------------------------------------------------------
[2021-07-11 12:27:00] 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
)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=81245&edit=1