Bug #48947 mysql J connector 5.1.10 returns wrong metadata for view
Submitted: 20 Nov 2009 16:58 Modified: 3 Dec 2009 13:35
Reporter: Alan LaPenn Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.1.10, Connector/J, meteadata, VIEW

[20 Nov 2009 16:58] Alan LaPenn
Description:
When trying the retrieve metadata from a view the wrong column name is returned for the view columns.

For example if I define the following view:

CREATE ALGORITHM=UNDEFINED DEFINER=`someuser`@`localhost` SQL SECURITY DEFINER VIEW `f_pim_contact` AS SELECT `c`.`userid` AS `userid`,`c`.`id` AS `id`,`c`.`first_name` AS `first_name`,`c`.`middle_name` AS `middle_name`,`c`.`last_name` AS `last_name`,`c`.`title` AS `title`,`c`.`company` AS `company`,`c`.`photo_type` AS `photo_type`,`c`.`job_title` AS `job_title`,`c`.`department` AS `department`,`c`.`body` AS `body`,`c`.`categories` AS `categories`,`c`.`display_name` AS `display_name`,`i`.`type` AS `item_type`,`i`.`value` AS `value`,`a`.`type` AS `address_type`,`a`.`street` AS `street`,`a`.`city` AS `city`,`a`.`state` AS `state`,`a`.`postal_code` AS `postal_code`,`a`.`country` AS `country` FROM ((`fnbl_pim_contact` `c` LEFT JOIN `fnbl_pim_address` `a` ON((`a`.`contact` = `c`.`id`))) LEFT JOIN `fnbl_pim_contact_item` `i` ON((`i`.`contact` = `c`.`id`))) WHERE (`c`.`status` <> _UTF8 'D');

When I try to get the column named `item_type` it returns the name of the column from the table it was linked to in  the view `type`. 

`i`.`type` AS `item_type`

Switching to an earlier version of connector/J  5.0.8 does not exhibit this behavior.

How to repeat:
Create a view making sure there is a column in the view defined with a label other than the name of the column the data was retrieved from, like `type` AS `item_type`

Execute a select statement on the view and retrieve the column names with the following snippet:

ResultSetMetaData rsmd;
for (int i = 1; i <= columnCount; ++i) {
            column = rsmd.getColumnName(i);
            log.trace("Column Name = "  + column);
}

Suggested fix:
Don't know anything about the connector/J code so I can't venture a guess at the fix. (I know there are a connector/J experts out there that can solve this problem in a much shorter time than I can).
[20 Nov 2009 17:03] Mark Matthews
What's returned if you use RSMD.getColumnLabel() instead of .getColumnName()? Later versions of Connector/J 5.1 changed the behavior of .getColumnName() to match a clarification in the JDBC-4.0 specification surrounding the behavior of this method.

There's a workaround if getColumnLabel() indeed returns what you expect, although really I say that's a bug in the server's returned metadata, because the client should expect to see a VIEW as a TABLE and know nothing about the underlying TABLEs in said VIEW.
[20 Nov 2009 18:11] Alan LaPenn
getColumnLabel() does return the correct column title and it is a workaround but as Mark Matthews said getColumnName really should be returning the same value regardless if it is a table or a view.
[23 Nov 2009 7:17] Tonci Grgin
Hi Alan.

I would like you to attach small but complete test case demonstrating unwanted behavior. You can find numerous examples in BugsDB.

I guess an output of same query in mysql command line client (use -T for MySQL servers 5.0 or --column-type-info for 5.1 and up) should be interesting too.
[23 Nov 2009 13:45] Alan LaPenn
I am a bit busy this week, have to pay the bills by working for a living:-) ao I won't get to the test case until next week. However I did run the select statement against the view and the results are below. Everything looks ok when I query from the mysql command line client on the local host:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1743
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from fp_pim_contact;
Field   1:  `userid`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     765
Max_length: 18
Decimals:   0
Flags:      MULTIPLE_KEY BINARY PART_KEY 

Field   2:  `id`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 4
Decimals:   0
Flags:      NOT_NULL PRI_KEY NUM PART_KEY 

Field   3:  `first_name`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 8
Decimals:   0
Flags:      BINARY 

Field   4:  `middle_name`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 5
Decimals:   0
Flags:      BINARY 

Field   5:  `last_name`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 10
Decimals:   0
Flags:      BINARY 

Field   6:  `title`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     96
Max_length: 0
Decimals:   0
Flags:      BINARY 

Field   7:  `company`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     765
Max_length: 14
Decimals:   0
Flags:      BINARY 

Field   8:  `photo_type`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       SHORT
Collation:  binary (63)
Length:     6
Max_length: 1
Decimals:   0
Flags:      NUM 

Field   9:  `job_title`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     384
Max_length: 0
Decimals:   0
Flags:      BINARY 

Field  10:  `department`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     765
Max_length: 0
Decimals:   0
Flags:      BINARY 

Field  11:  `body`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       BLOB
Collation:  utf8_general_ci (33)
Length:     196605
Max_length: 2
Decimals:   0
Flags:      BLOB 

Field  12:  `categories`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     765
Max_length: 8
Decimals:   0
Flags:      BINARY 

Field  13:  `display_name`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     384
Max_length: 18
Decimals:   0
Flags:      BINARY 

Field  14:  `item_type`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact_item`
Type:       SHORT
Collation:  binary (63)
Length:     6
Max_length: 2
Decimals:   0
Flags:      PRI_KEY NUM PART_KEY 

Field  15:  `value`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_contact_item`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     765
Max_length: 21
Decimals:   0
Flags:      BINARY 

Field  16:  `address_type`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_address`
Type:       SHORT
Collation:  binary (63)
Length:     6
Max_length: 1
Decimals:   0
Flags:      PRI_KEY NUM PART_KEY 

Field  17:  `street`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_address`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     384
Max_length: 19
Decimals:   0
Flags:      BINARY 

Field  18:  `city`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_address`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 6
Decimals:   0
Flags:      BINARY 

Field  19:  `state`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_address`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 0
Decimals:   0
Flags:      BINARY 

Field  20:  `postal_code`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_address`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     48
Max_length: 0
Decimals:   0
Flags:      BINARY 

Field  21:  `country`
Catalog:    `def`
Database:   `funambol`
Table:      `fp_pim_contact`
Org_table:  `fnbl_pim_address`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     96
Max_length: 2
Decimals:   0
Flags:      BINARY 

mysql> exit
Bye

User time 0.00, System time 0.01
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 749, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 54, Involuntary context switches
[24 Nov 2009 12:32] Tonci Grgin
Ok Alan. Waiting on small but complete test case.
[30 Nov 2009 15:20] Alan LaPenn
//Create the database and tables and a user

CREATE DATABASE IF NOT EXIST conjprob DEFAULT CHARSET=utf8; 

CREATE TABLE `testTbl` (
  `fieldName` char(10) DEFAULT NULL,
  `type` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW `testView` AS SELECT `testTbl`.`fieldName` AS `fieldName`,`testTbl`.`type` AS `field_type` FROM `testTbl`;

// Code to reproduce problem with connector
import java.sql.*;

public class j {
  public static void main(String args[]) {
    Connection con = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    Statement s = null;

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql://localhost:3306/conjprob",
        "conj", "test");

      if(!con.isClosed())
      {

        s = con.createStatement ();
        s.executeQuery ("SELECT * FROM testView");
        rs = s.getResultSet ();
        rsmd = rs.getMetaData();

        System.out.println();
        System.out.println("View Name: testView");
        System.out.println("-------------------");
        System.out.println("Column Label" + "\t" + "Column Name");
        System.out.println("------------" + "\t" + "-----------");
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i <= columnCount; ++i)
        {
            System.out.println(rsmd.getColumnLabel(i) + "\t" + rsmd.getColumnName(i));
        }
      }

    } catch(Exception e) {
      System.err.println("Exception: " + e.getMessage());
    } finally {
      try {
        if(con != null)
          s.close();
          rs.close();
          con.close();
      } catch(SQLException e) {}
    }
  }
}

// Result of running  above code against the created database 
// Using the Connector/J version 5.1.10

View Name: testView
-------------------
Column Label    Column Name
------------    -----------
fieldName       fieldName
field_type      type
[3 Dec 2009 11:34] Tonci Grgin
Alan, I can not repeat this behavior using latest c/J sources...

Connected to 5.1.31-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os                      : Windows Server 2008, 6.0, x86
sun.management.compiler : HotSpot Client Compiler
-------------------------------------------------

View Name: vbug48947
-------------------
Column Label	Column Name
------------	-----------
fieldName	fieldName
field_type	field_type

Time: 0,387

OK (1 test)

Test case attached.
JVM arguments and connection string:
-Xmx512M -XX:+UseParallelGC -Dcom.mysql.jdbc.java6.javac=C:\jvms\jdk1.6.0\bin\javac.exe -Dcom.mysql.jdbc.java6.rtjar=C:\jvms\jdk1.6.0\jre\lib\rt.jar -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://xx:xx/test?user=xx&password=xx&autoReconnect=false&connectTimeout=5000&socketTimeout=30000&useUnicode=true&characterSetResults=utf8&useInformationSchema=true&useServerPrepStmts=true&cacheResultSetMetadata=true&useAffectedRows=false&useColumnNamesInFindColumn=true
[3 Dec 2009 11:36] Tonci Grgin
Test case for our test framework, working in JDK 1.5.

Attachment: TestBug48947.java (text/x-java), 2.37 KiB.

[3 Dec 2009 13:34] Alan LaPenn
I am using:

java version "1.6.0_12"
Java(TM) SE Runtime Environment (build 1.6.0_12-b04)
[3 Dec 2009 13:35] Alan LaPenn
Java HotSpot(TM) Client VM (build 11.2-b01, mixed mode, sharing)

centOS 5.0
[4 Dec 2009 16:53] Tonci Grgin
Alan, no, still can't repeat with JDK 1.6:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

java.vm.version         : 11.0-b16
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.6.0_11-b03
os                      : Windows Server 2008, 6.0, x86
sun.management.compiler : HotSpot Client Compiler
-------------------------------------------------
Connected to 5.1.31-log

View Name: vbug48947
-------------------
Column Label	Column Name
------------	-----------
fieldName	fieldName
field_type	field_type

Time: 0,741

OK (1 test)

Guys, please use my test case and latest snapshot of cJ while trying to repeat the problem.