Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
5.5.64, 10.3.22
-
CentOS Linux release 7.7.1908
PHP 7.3.16
Description
The statement metadata does not correctly identify the underlying table and column. This is a regression vs e.g. MySQL 5.1.73.
This behavior has been reproduced in several server environments and also with the mariadb client for nodejs.
Impact: Cannot determine application-level metadata and security attributes for the tables and columns queried.
Repro (PHP):
<?php
|
|
|
/*
|
* connect parameters
|
*/
|
$host = 'localhost'; |
$id = 'holson'; |
$pwd = 'xxxxxxxx'; |
$dbname = 'alias'; |
|
|
/*
|
* connect
|
*/
|
$link = @mysqli_connect($host, $id, $pwd); |
if ($link === false) { |
echo 'unable to connect to database - incorrect connect parametsrs?' . PHP_EOL; |
exit(1); |
}
|
mysqli_select_db($link, $dbname); |
|
|
/*
|
* prepare database
|
*/
|
$ddl = "drop table if exists Site"; |
$r = mysqli_query($link, $ddl); |
$ddl = "create table Site (Name varchar(50))"; |
$r = mysqli_query($link, $ddl); |
|
|
/*
|
* execute test case
|
*/
|
$sql=" |
select
|
SiteName
|
from (select Name as SiteName from Site) as s |
";
|
echo 'DDL: ' . $ddl . PHP_EOL; |
echo 'SQL: ' . $sql . PHP_EOL; |
echo 'Expect: orgname => Name, orgtable => Site'. PHP_EOL; |
$stmt = mysqli_prepare($link, $sql); |
$fields = $stmt->result_metadata()->fetch_fields(); |
echo 'Result ( $stmt->result_metadata()->fetch_fields() ): '; |
print_r($fields); |
|
|
?>
|
I am getting orgname => SiteName everywhere – MySQL 5.1.73, 5.5.62, MariaDB 5.5.67, MariaDB 10.3.22.
orgtable is indeed different, orgtable => Site with MySQL, orgtable => s with MariaDB.
Apparently it is related to MariaDB optimizing the query. If you want the old behavior, you can set optimizer_switch=derived_merge=off.