Bug #20191 getTableName gives wrong or inconsistent result when using VIEWs
Submitted: 1 Jun 2006 10:19 Modified: 14 Dec 2006 20:10
Reporter: Wai Wong Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.0.23-BK OS:Any (all)
Assigned to: Georgi Kodinov CPU Architecture:Any

[1 Jun 2006 10:19] Wai Wong
Description:
When using getTableName on a view, sometimes it will return the view's name, but sometimes it will return to base table's name.  This makes it impossible to match the fields as the values depends on the result, not the structure.  We cannot find a way to work around this at all.  So we are forced to give up using views.

Please see the following piece of jsp code to reproduce.  The result is attached below:

when limit is 1, getTableName gives: t1 

when limit is 0, getTableName gives: v1 

when result not found, getTableName gives: v1 

when result not found, getTableName gives: t1 

Note that "group by" is essential in this case

How to repeat:
<%@ page language="java" import="java.util.*" %>
<%@ page language="java" import="java.sql.*" %>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/tic?useUnicode=true&autoReconnect=true&characterEncoding=utf8", "root", "");
Statement stmt = conn.createStatement();
try {
    stmt.executeUpdate("drop table if exists t1;");
    stmt.executeUpdate("create table t1 (id int(10))");
    stmt.executeUpdate("insert into t1 values (1);");
    stmt.executeUpdate("drop view if exists v1");
    stmt.executeUpdate("CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW v1 AS select t1.id as id from t1");
    ResultSet rs = stmt.executeQuery("select * from v1 group by id limit 1");
    out.println("when limit is 1, getTableName gives: ");
    ResultSetMetaData rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    rs = stmt.executeQuery("select * from v1 group by id limit 0");
    out.println("when limit is 0, getTableName gives: ");
    rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    rs = stmt.executeQuery("select * from v1 where id=1000 group by id ");
    out.println("when result not found, getTableName gives: ");
    rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    rs = stmt.executeQuery("select * from v1 where id=1 group by id ");
    out.println("when result not found, getTableName gives: ");
    rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    out.println("Note that \"group by\" is essential in this case");
    rs.close();
} catch (Exception e) {
    System.err.println("Error");
} finally {
    stmt.close();
    conn.close();
}
%>
[1 Jun 2006 10:23] Wai Wong
Sorry, some typos in the code.  Here is the updated one:

<%@ page language="java" import="java.util.*" %>
<%@ page language="java" import="java.sql.*" %>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/tic?useUnicode=true&autoReconnect=true&characterEncoding=utf8", "root", "");
Statement stmt = conn.createStatement();
try {
    stmt.executeUpdate("drop table if exists t1;");
    stmt.executeUpdate("create table t1 (id int(10))");
    stmt.executeUpdate("insert into t1 values (1);");
    stmt.executeUpdate("drop view if exists v1");
    stmt.executeUpdate("CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW v1 AS select t1.id as id from t1");
    ResultSet rs = stmt.executeQuery("select * from v1 group by id limit 1");
    out.println("when limit is 1, getTableName gives: ");
    ResultSetMetaData rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    rs = stmt.executeQuery("select * from v1 group by id limit 0");
    out.println("when limit is 0, getTableName gives: ");
    rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    rs = stmt.executeQuery("select * from v1 where id=1000 group by id ");
    out.println("when result not found, getTableName gives: ");
    rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    rs = stmt.executeQuery("select * from v1 where id=1 group by id ");
    out.println("when result found, getTableName gives: ");
    rsmd = rs.getMetaData();
    out.println(rsmd.getTableName(1));
    out.println("<P>");
    out.println("Note that \"group by\" is essential in this case");
    rs.close();
} catch (Exception e) {
    System.err.println("Error");
} finally {
    stmt.close();
    conn.close();
}
%>

And the result is

when limit is 1, getTableName gives: t1 

when limit is 0, getTableName gives: v1 

when result not found, getTableName gives: v1 

when result found, getTableName gives: t1 

Note that "group by" is essential in this case
[1 Jun 2006 13:27] Mark Matthews
Changed category, as the JDBC driver is only reporting metadata given to it by the server, so it's the server that's being inconsitent in this case.
[1 Jun 2006 19:33] Valeriy Kravchuk
Thank you for a problem report. As this is a server-related problem, please, specify the exact MySQL server version used.
[2 Jun 2006 2:53] Wai Wong
From 5.0.18 till 5.0.22 (latest)
[20 Jun 2006 13:57] Valeriy Kravchuk
At the server level it is all obvious:

mysql> create table t1 (id int(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.01 sec)

mysql> drop view if exists v1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
    -> SQL SECURITY DEFINER VIEW v1 AS select t1.id as id from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from v1 group by id limit 1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | t1    | system | NULL          | NULL | NULL    | NULL | 1 |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)

So, it looks like driver just use something similar to explain to get a name of table.

mysql> explain select * from v1 group by id limit 0;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL
 | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

And, in case of empty result set, driver use view name.

mysql> explain select * from v1 where id=1000 group by id;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL
 | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
1 row in set (0.00 sec)

Same result above.

mysql> explain select * from v1 where id=1 group by id;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | t1    | system | NULL          | NULL | NULL    | NULL | 1 |       |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Wai:

Can you, please, describe what is a desired behaviour, and why do you think so (quotes from the manual/standards are welcomed).

Mark:

Can you, please, explain me how driver gets table name from server? What exect SQL statements are executed (I can figure that out myself, but just want to check the intended logic) and why do you think the above is not a driver bug?
[20 Jun 2006 15:35] Mark Matthews
Valeriy,

ResultSetMetadata (and thus rsmd.getTableName()) is just the name for what we call MYSQL_FIELD in the c-library. There is no "explain" or anything involved. The driver is using whatever the server is returning as field-level metadata.

Here's some debugging from inside the JDBC driver. For the cases where this appears to return the table name "t1" (which is probably incorrect, if selecting from the view, the view name should be returned as the table):

-------------------------------------------------
com.mysql.jdbc.Field@12884e0
  catalog: test
  table name: t1
  original table name: t1
  column name: id
  original column name: id
  MySQL data type: 3

Data as received from server:

03 64 65 66 04 74 65 73     . d e f . t e s 
74 02 74 31 02 74 31 02     t . t 1 . t 1 . 
69 64 02 69 64 0c 3f 00     i d . i d . ? . 
0a 00 00 00 03 00 00 00     . . . . . . . . 
00 00 00                    . . . 
-------------------------------------------------

When it returns the view name for the table name (The last case), the data sent to the client is this:

-------------------------------------------------
com.mysql.jdbc.Field@f9c40
  catalog: test
  table name: v1
  original table name: t1
  column name: id
  original column name: id
  MySQL data type: 3

Data as received from server:

03 64 65 66 04 74 65 73     . d e f . t e s 
74 02 76 31 02 74 31 02     t . v 1 . t 1 . 
69 64 02 69 64 0c 3f 00     i d . i d . ? . 
0a 00 00 00 03 00 00 00     . . . . . . . . 
00 00 00                    . . . 

-------------------------------------------------

Given that these are all queries that are of the form "SELECT * FROM v1 ...", I don't think the server should return different table names based on whether rows exist or not, and probably it shouldn't _ever_ return the original underlying table name as that's information leakage about what the view contains. The "original table name" is from the portion of the protocol where _usually_ the server returns something different than "table name" when an ALIAS has been used.
[21 Jun 2006 3:11] Wai Wong
Valeriy,

I think Mark has explained what we expect -- consistency.  Returning metadata depending on query result is obviously not a desired behaviour.

We found some other cases giving inconsistent metadata result.  One is when using "select distinct * from viewABC where ...... ".  It will be consistent if the "distinct" is not there.

And the other is when using "select * from abc as def".  Limit 0 will give "abc" and Limit 1 will give "def" (or vice versa, I cannot remember now).

Maybe they are simply from the same source as the original problem?

Hope this helps.

Wai Wong.
[21 Jun 2006 11:52] Valeriy Kravchuk
Verified. This is a server (protocol?) bug. See also Mark's comment of 20 Jun 17:35 for the details. 

Wai, Mark:

Thank you for the persistance and help with verification.
[14 Aug 2006 3:42] Wai Wong
Any fix for this is planned?
[4 Oct 2006 6:59] Wai Wong
This problem still exists in 5.0.24a.  It is another 40 days after my last question.  Shall we see this fix soon?
[20 Oct 2006 11:06] Wai Wong
Testing again in 5.0.26.

Now getTableName is always giving the underlying table name.  Is this behaviour expected?  If it is confirmed that the view name is NOT used, we shall change our code accordingly.
[7 Nov 2006 9:55] Wai Wong
Tested with standard 5.0.27 and Connector/J 5.0.4, the error comes back.  The getTableName is giving inconsistent result again.

Output:

when limit is 1, getTableName gives: t1

when limit is 0, getTableName gives: v1

when result not found, getTableName gives: v1

when result not found, getTableName gives: t1

Note that "group by" is essential in this case
[9 Nov 2006 14:56] Bugs System
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/15089

ChangeSet@1.2302, 2006-11-09 16:55:42+02:00, gkodinov@macbook.gmz +4 -0
  Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs
  
  When compiling GROUP BY Item_ref instances are dereferenced in 
  setup_copy_fields(), i.e. replaced with the corresponding Item_field 
  (if they point to one) or Item_copy_string for the other cases.
  Since the Item_ref (in the Item_field case) is no longer used the information
  about the aliases stored in it is lost.   
  Fixed by preserving the column, table and DB alias on dereferencing Item_ref
[27 Nov 2006 17:14] Georgi Kodinov
Pushed in 5.0.32/5.1.14-beta
[14 Dec 2006 20:10] Paul Dubois
Noted in 5.0.32, 5.1.14 changelogs.

For queries that select from a view, the server was returning
MYSQL_FIELD metadata inconsistently for view names and table names.
For view columns, the server now returns the view name in the table
field and, if the column selects from an underlying table, the table
name in the org_table field.

Also updated the table and org_table descriptions for MYSQL_FIELD
in the C API chapter.