MySQL Bugs: #41788: mysql_fetch_field returns org

Bug #41788	mysql_fetch_field returns org_table == table by a view
Submitted:	29 Dec 2008 19:47	Modified:	17 Jun 2010 22:41
Reporter:	Anton Shchirov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.1.30, 5.0, 5.1, 6.0 bzr	OS:	Any (MS Windows, Mac)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	mysql_fetch_field, org_table, VIEW

Description:
When query select data from view and for this view defined alias, then in MYSQL_FIELD org_table == table == view alias.

Fror table mysql_fetch_field works fine.

How to repeat:
CREATE TABLE table1 (ID INTEGER);
CREATE VIEW view1 AS SELECT ID FROM table1;

void test_fields(char* sql) {
  printf("SQL: %s\n", sql);
  if (mysql_stmt_prepare(stmt, sql, strlen(sql)))
    printf("Prepare error: %s", mysql_stmt_error(stmt));
  MYSQL_RES* res;
  res = mysql_stmt_result_metadata(stmt);
  if (res) {
    int cnt = mysql_num_fields(res);
    for (int i = 0; i <= cnt; i++) {
    MYSQL_FIELD* fld;
    fld = mysql_fetch_field(res);
    printf("FLD: %s, TBL: %s, Alias: %s\n", fld->name, fld->org_table, fld->table);
    }
  mysql_free_result(res);
  }
}

int main(int argc, char* argv[]) {
  MYSQL* conn;
  conn = mysql_init(NULL);
  if (!mysql_real_connect(conn, "host", "user", "pass", "db", 0, NULL, 0))
	printf("Connect error: %s", mysql_error(conn));
  MYSQL_STMT* stmt;
  stmt = mysql_stmt_init(conn);
  char sql1[] = "SELECT * FROM view1";
  char sql2[] = "SELECT * FROM view1 vw";
  test_fields(sql1);
  printf("---------------");
  test_fields(sql2);
  mysql_close(conn);
  return 0;
}

===================
Output:
SQL: SELECT * FROM view1
FLD: ID, TBL: view1, Alias: view1
---------------
SQL: SELECT * FROM view1 vw
FLD: ID, TBL: vw, Alias: vw

Thank you for the report.

Verified as described.

A bit modified test case:

#include "mysql.h"
#include <stdio.h>
#include <strings.h>

void test_fields(char* sql,  MYSQL* conn, MYSQL_STMT* stmt) {
        printf("SQL: %s\n", sql);
    if (mysql_stmt_prepare(stmt, sql, strlen(sql)))
    printf("Prepare error: %s", mysql_stmt_error(stmt));
        MYSQL_RES* res;
    res = mysql_stmt_result_metadata(stmt);
        if (res) {
            int cnt = mysql_num_fields(res);
                for (int i = 0; i < cnt; i++) {
                        MYSQL_FIELD* fld;
                    fld = mysql_fetch_field(res);
                    printf("FLD: %s, TBL: %s, Alias: %s\n", fld->name, fld->org_table, fld->table);
                }
            mysql_free_result(res);
         }
}

int main()
{
        MYSQL_RES* res;
        MYSQL* conn;
        MYSQL_STMT* stmt;

        conn = mysql_init(NULL);

  conn = mysql_init(NULL);
  conn = mysql_real_connect(conn, "127.0.0.1", "root", "", "test", 4040, NULL, CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS);

  char *query = "drop table if exists table1; drop view if exists view1; CREATE TABLE table1 (ID INTEGER); CREATE VIEW view1 AS SELECT ID FROM table1;";
  mysql_real_query(conn, query, strlen(query));

  while (0 == mysql_next_result(conn)) {
          res = mysql_store_result(conn);
          mysql_free_result(res);
  }

   stmt = mysql_stmt_init(conn);
   char sql1[] = "SELECT * FROM view1";
   char sql2[] = "SELECT * FROM view1 vw";
   char sql3[] = "SELECT * FROM table1";
   char sql4[] = "SELECT * FROM table1 tt";
   test_fields(sql1, conn, stmt);
   printf("---------------\n");
   test_fields(sql2, conn, stmt);
   printf("---------------\n");
   test_fields(sql3, conn, stmt);
   printf("---------------\n");
   test_fields(sql4, conn, stmt);
   mysql_close(conn);

  return 0;
}

Bug #41899 was marked as duplicate of this one.

Sveta.

I do not agree with triage here and I'll change it in light of below statements.

This represents a problem for any connector, where the client wants to create SQL based on a result set. Because it can't get the *original* table name (in this case a view) back from the metadata which is the entire reason you have the metadata for original table names.
It would also break any connector (client) that does updatable result sets, when used against a view that has been aliased. I think that even our clients, like OO.org base, will have problems if you execute a query with view and alias.

Thus I'm changing the triage to D2/W2/I2 and requesting escalation.

Expected result:
SQL: SELECT * FROM view1
FLD: ID, TBL: view1, Alias: view1
---------------
SQL: SELECT * FROM view1 vw
FLD: ID, TBL: view1, Alias: vw
---------------
SQL: SELECT * FROM table1
FLD: ID, TBL: table1, Alias: table1
---------------
SQL: SELECT * FROM table1 tt
FLD: ID, TBL: table1, Alias: tt

Actual result:

SQL: SELECT * FROM view1
FLD: ID, TBL: view1, Alias: view1
---------------
SQL: SELECT * FROM view1 vw
FLD: ID, TBL: vw, Alias: vw
---------------
SQL: SELECT * FROM table1
FLD: ID, TBL: table1, Alias: table1
---------------
SQL: SELECT * FROM table1 tt
FLD: ID, TBL: table1, Alias: tt

To make it shorter.

Expected:

SQL: SELECT * FROM view1 vw
FLD: ID, TBL: view1, Alias: vw

Actual:

SQL: SELECT * FROM view1 vw
FLD: ID, TBL: vw, Alias: vw

The problem doesn't appear to be in the C API, but the metadata being reported by the prepared statement protocol.

Using the non-prepared-statement protocol, the view alias is reported as the table name, and the underlying table name as the orig_table. I believe that is the desired behavior.

Fixing the category.

(And a side note: test cases that print stuff are much less useful than test cases that test their output against what is expected to happen.)

Same result without prepared statements:

SQL: SELECT * FROM view1 vw
Name: ID, Org_table: vw, Table: vw

instead of

SQL: SELECT * FROM view1 vw
Name: ID, Org_table: view1, Table: vw

Modified test would be attached.

test case without prepared statements

Attachment: bug41788_2.cpp (text/plain), 1.44 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102913

3385 Sergey Glukhov	2010-03-10
      Bug#41788 mysql_fetch_field returns org_table == table by a view
      The problem is that Item_direct_view_ref which is inherited
      from Item_ident updates orig_table_name and table_name with
      the same values. The fix is introduction of new constructor
      into Item_ident and up which updates orig_table_name and
      table_name separately.
     @ mysql-test/r/show_check.result
        test case
     @ mysql-test/t/show_check.test
        test case
     @ sql/item.cc
        new constructor which updates
        orig_table_name and table_name separately.
     @ sql/item.h
        new constructor which updates
        orig_table_name and table_name separately.
     @ sql/table.cc
        new constructor which updates
        orig_table_name and table_name
        separately.

Review sent over e-mail.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103008

3392 Sergey Glukhov	2010-03-11
      Bug#41788 mysql_fetch_field returns org_table == table by a view
      The problem is that Item_direct_view_ref which is inherited
      from Item_ident updates orig_table_name and table_name with
      the same values. The fix is introduction of new constructor
      into Item_ident and up which updates orig_table_name and
      table_name separately.
     @ mysql-test/r/show_check.result
        test case
     @ mysql-test/t/show_check.test
        test case
     @ sql/item.cc
        new constructor which updates
        orig_table_name and table_name
        separately.
     @ sql/item.h
        new constructor which updates
        orig_table_name and table_name
        separately.
     @ sql/table.cc
        used new constructor

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103009

3392 Sergey Glukhov	2010-03-11
      Bug#41788 mysql_fetch_field returns org_table == table by a view
      The problem is that Item_direct_view_ref which is inherited
      from Item_ident updates orig_table_name and table_name with
      the same values. The fix is introduction of new constructor
      into Item_ident and up which updates orig_table_name and
      table_name separately.
     @ mysql-test/r/show_check.result
        test case
     @ mysql-test/t/show_check.test
        test case
     @ sql/item.cc
        new constructor which updates
        orig_table_name and table_name
        separately.
     @ sql/item.h
        new constructor which updates
        orig_table_name and table_name
        separately.
     @ sql/table.cc
        used new constructor

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103048

3395 Sergey Glukhov	2010-03-12
      Bug#41788 mysql_fetch_field returns org_table == table by a view
      The problem is that Item_direct_view_ref which is inherited
      from Item_ident updates orig_table_name and table_name with
      the same values. The fix is introduction of new constructor
      into Item_ident and up which updates orig_table_name and
      table_name separately.
     @ mysql-test/r/metadata.result
        test case
     @ mysql-test/t/metadata.test
        test case
     @ sql/item.cc
        new constructor which updates
        orig_table_name and table_name
        separately.
     @ sql/item.h
        new constructor which updates
        orig_table_name and table_name
        separately.
     @ sql/table.cc
        used new constructor

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:sergey.glukhov@sun.com-20100312063316-sflh6tc1ldvwdxgn) (merge vers: 5.1.46) (pib:16)

Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

For a query that selected from a view and used an alias for the view,
the metadata used the alias name rather than the view name in the
MYSQL_FIELD.table member.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)