Bug #44159 mysqli_fetch_fields returns wrong orgtable and orgname for a particular query
Submitted: 8 Apr 2009 16:55 Modified: 9 Apr 2009 7:27
Reporter: Filipe Martins Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.33/5.0/6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqli_fetch_fields view orgtable

[8 Apr 2009 16:55] Filipe Martins
Description:
When I run 'mysqli_fetch_fields' on a result from selecting a particular view", "orgtable" reports the name of the same view! But this only seams to happen on this particular database. I couldn't make it happen in any other.

My setup:

Windows XP Pro
PHP 5.2.9-1
PHP MySQLi 5.0.51a
MySQL  5.1.33-community

I tested the same database on different machines/servers with the following results:

LINUX
   5.1.33-log        WRONG
   5.0.51            CORRECT (got the name of the underlying table)

WINDOWS
   5.1.33-community  WRONG
   5.0.77-community  WRONG

So it seams that this problem was introduced after MySQL version 5.0.51 and happens both on Windows and Linux.

Of course this could be a bug in the PHP client libs instead, so I ask others to test with different environments and different client APIs.

How to repeat:
Just run "mysqli_fetch_fields.php" and see if "orgtable" reads "vw_downls_fichs". It shouldn't because that's the name of the view we're querying.

Suggested fix:
"orgtable" and "orgname" should be the names in the original tables that the view is reading from.
[8 Apr 2009 16:57] Filipe Martins
Database where the problem shows itself

Attachment: mysqli_fetch_fields.sql (application/octet-stream, text), 351.99 KiB.

[8 Apr 2009 16:58] Filipe Martins
Script accessing the database

Attachment: mysqli_fetch_fields.php (application/octet-stream, text), 941 bytes.

[8 Apr 2009 17:00] Filipe Martins
You must load the SQL on a database, for example:
   mysql -u root -p test < mysqli_fetch_fields.sql (loading to the test database)

Then, you must edit the script and insert the info for connecting to the database.
[8 Apr 2009 18:33] MySQL Verification Team
Thank you for the bug report. Could you please print here the actual result you got and the expected one. Thanks in advance.
[8 Apr 2009 18:52] Filipe Martins
It prints:

Querying vw_downls_fichs...
NAME: IdLing ORGNAME: IdLing ORGTABLE: vw_downls_fichs
NAME: IdPag ORGNAME: IdPag ORGTABLE: vw_downls_fichs
NAME: IdDownlFich ORGNAME: IdDownlFich ORGTABLE: vw_downls_fichs
NAME: Nome ORGNAME: Nome ORGTABLE: vw_downls_fichs
NAME: Descr ORGNAME: Descr ORGTABLE: vw_downls_fichs
NAME: DataUltAct ORGNAME: DataUltAct ORGTABLE: vw_downls_fichs
NAME: DataIns ORGNAME: DataIns ORGTABLE: vw_downls_fichs
NAME: NomeFich ORGNAME: NomeFich ORGTABLE: vw_downls_fichs

But should instead print:

NAME: IdLing ORGNAME: IdLing ORGTABLE: downls_fichs_descrs
NAME: IdPag ORGNAME: IdPag ORGTABLE: downls_fichs
NAME: IdDownlFich ORGNAME: IdDownlFich ORGTABLE: downls_fichs_descrs
NAME: Nome ORGNAME: Nome ORGTABLE: downls_fichs_descrs
NAME: Descr ORGNAME: Descr ORGTABLE: downls_fichs_descrs
NAME: DataUltAct ORGNAME: DataUltAct ORGTABLE: downls_fichs
NAME: DataIns ORGNAME: DataIns ORGTABLE: downls_fichs
NAME: NomeFich ORGNAME: NomeFich ORGTABLE: downls_fichs_descrs

(taken from running the script against the Linux MySQL 5.0.51 server I mentioned in the report)

I'll be glad if I can help you more understanding this issue.
Thanks
[8 Apr 2009 21:53] MySQL Verification Team
So far I was able to verify the behavior reported against 5.1 source server and 5.0.51b. I wasn't able to find in our Documentation changes about.

c:\build>bug44159.exe
Server: 5.1.35-Win X64 revno: 2852-log Client: 5.1.35

NAME:IdLing: ORGNAME: IdLing ORGTABLE: vw_downls_fichs
NAME:IdPag: ORGNAME: IdPag ORGTABLE: vw_downls_fichs
NAME:IdDownlFich: ORGNAME: IdDownlFich ORGTABLE: vw_downls_fichs
NAME:Nome: ORGNAME: Nome ORGTABLE: vw_downls_fichs
NAME:Descr: ORGNAME: Descr ORGTABLE: vw_downls_fichs
NAME:DataUltAct: ORGNAME: DataUltAct ORGTABLE: vw_downls_fichs
NAME:DataIns: ORGNAME: DataIns ORGTABLE: vw_downls_fichs
NAME:NomeFich: ORGNAME: NomeFich ORGTABLE: vw_downls_fichs

D:\test-bug>bug44159
Server: 5.0.51b-community Client: 5.0.51b

NAME:IdLing: ORGNAME: IdLing ORGTABLE: downls_fichs_descrs
NAME:IdPag: ORGNAME: IdPag ORGTABLE: downls_fichs
NAME:IdDownlFich: ORGNAME: IdDownlFich ORGTABLE: downls_fichs_descrs
NAME:Nome: ORGNAME: Nome ORGTABLE: downls_fichs_descrs
NAME:Descr: ORGNAME: Descr ORGTABLE: downls_fichs_descrs
NAME:DataUltAct: ORGNAME: DataUltAct ORGTABLE: downls_fichs
NAME:DataIns: ORGNAME: DataIns ORGTABLE: downls_fichs
NAME:NomeFich: ORGNAME: NomeFich ORGTABLE: downls_fichs_descrs

c:\build>bug44159.exe
Server: 5.0.51b-community Client: 5.1.35

NAME:IdLing: ORGNAME: IdLing ORGTABLE: downls_fichs_descrs
NAME:IdPag: ORGNAME: IdPag ORGTABLE: downls_fichs
NAME:IdDownlFich: ORGNAME: IdDownlFich ORGTABLE: downls_fichs_descrs
NAME:Nome: ORGNAME: Nome ORGTABLE: downls_fichs_descrs
NAME:Descr: ORGNAME: Descr ORGTABLE: downls_fichs_descrs
NAME:DataUltAct: ORGNAME: DataUltAct ORGTABLE: downls_fichs
NAME:DataIns: ORGNAME: DataIns ORGTABLE: downls_fichs
NAME:NomeFich: ORGNAME: NomeFich ORGTABLE: downls_fichs_descrs
[8 Apr 2009 22:00] MySQL Verification Team
C API Test case

Attachment: test-case-bug44159.txt (text/plain), 1.53 KiB.

[8 Apr 2009 23:22] MySQL Verification Team
Thank you for the feedback. Behavior changed since 5.1.28:

c:\build>bug44159.exe
Server: 5.1.28-rc-community Client: 5.1.35

NAME:IdLing: ORGNAME: IdLing ORGTABLE: vw_downls_fichs
NAME:IdPag: ORGNAME: IdPag ORGTABLE: vw_downls_fichs
NAME:IdDownlFich: ORGNAME: IdDownlFich ORGTABLE: vw_downls_fichs
NAME:Nome: ORGNAME: Nome ORGTABLE: vw_downls_fichs
NAME:Descr: ORGNAME: Descr ORGTABLE: vw_downls_fichs
NAME:DataUltAct: ORGNAME: DataUltAct ORGTABLE: vw_downls_fichs
NAME:DataIns: ORGNAME: DataIns ORGTABLE: vw_downls_fichs
NAME:NomeFich: ORGNAME: NomeFich ORGTABLE: vw_downls_fichs

c:\build>bug44159.exe
Server: 5.1.26-rc-community Client: 5.1.35

NAME:IdLing: ORGNAME: IdLing ORGTABLE: downls_fichs_descrs
NAME:IdPag: ORGNAME: IdPag ORGTABLE: downls_fichs
NAME:IdDownlFich: ORGNAME: IdDownlFich ORGTABLE: downls_fichs_descrs
NAME:Nome: ORGNAME: Nome ORGTABLE: downls_fichs_descrs
NAME:Descr: ORGNAME: Descr ORGTABLE: downls_fichs_descrs
NAME:DataUltAct: ORGNAME: DataUltAct ORGTABLE: downls_fichs
NAME:DataIns: ORGNAME: DataIns ORGTABLE: downls_fichs
NAME:NomeFich: ORGNAME: NomeFich ORGTABLE: downls_fichs_descrs
[9 Apr 2009 7:27] Filipe Martins
Thanks for proving I'm not mad, Miguel :-)
I observed this "odd" behavior against a Windows MySQL 5.0.77-community server, so the change ocurred earlier.