Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-84

If a select list contains the same column more than once, the last instance of the column will be returned

    XMLWordPrintable

    Details

      Description

      As per the JDBC 4.1 spec (15.2.3 Retrieving Values -
      Synopsis:

      http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf
      ), If a select list contains the same column more than once, the first instance of the column should be returned. The mariadb java client returns the last instance.

      How To Repeat:

      – Create two tables with the same column name and a relationship between the tables using table1.id=table2.fk
      CREATE TABLE `table1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `attempt` int(11) NOT NULL DEFAULT '0',
      `description` varchar(50) NOT NULL DEFAULT 'description-table1',
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

      CREATE TABLE `table2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `fk` int(11) NOT NULL,
      `attempt` int(11) NOT NULL DEFAULT '0',
      `description2` varchar(50) NOT NULL DEFAULT 'description-table2',
      PRIMARY KEY (`id`),
      KEY `fk` (`fk`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

      ALTER TABLE `table2`
      ADD CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `table1` (`id`);

      – Insert three records into table1, two matching records into table2, so there is one set of nulls
      INSERT INTO `table1` (`id`, `attempt`, `description`) VALUES
      (1, 1, 'matched1'),
      (2, 2, 'unmatched2'),
      (3, 3, 'matched3');

      INSERT INTO `table2` (`id`, `fk`, `attempt`, `description2`) VALUES
      (1, 1, 1, 'description1-table2'),
      (3, 3, 3, 'description3-table2');

      – Select data with outer join including nulls using wildcard and named columns
      SELECT t1.id, t1.attempt, t1.description, t2.* FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t2.fk=t1.id;

      Results having duplicate columns through left outer join:

      mysql> SELECT t1.id, t1.attempt, t1.description, t2.* FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t2.fk=t1.id;
      --------------------------------------------------------

      id attempt description id fk attempt description2

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

      1 1 matched1 1 1 1 description1-table2
      3 3 matched3 3 3 3 description3-table2
      2 2 unmatched2 NULL NULL NULL NULL

      --------------------------------------------------------
      3 rows in set (0.00 sec)

      – Finding the column "attempt"
      rs.findColumn("attempt") gives us "6" . Should be "2"

      PS: The findColumn strategy is used heavily by ORM frameworks like Hibernate for Mapping objects and causes severe failures as a result of this bug.

        Attachments

          Activity

            People

            Assignee:
            georg Georg Richter
            Reporter:
            krisiyer Kris Iyer
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.