Display columns are confused by USING when selecting * from one table in a join

[bart-schaefer]

This is kind of obscure ... when table1 and table2 share a key column named for example unique_id, then given a SQL statement such as SELECT unique_id, table2.* FROM table1 JOIN table2 USING (unique_id) only a single column named unique_id is returned by MySQL. That is, the columns from table2.* skip unique_id and return only the other possible columns.

This causes Antares to miscount the number of return columns, and the data from table2 is shifted over by one column, leaving mismatched column headings and a blank column at the far right. The more tables and USING clauses are involved, the worse the column mismatches become.

This can be worked around by something like SELECT table1.unique_id AS t1_id, table2.* FROM table1 JOIN table2 USING (unique_id)

Expected behavior The actual column list returned by running the SELECT statement should be used to construct the displayed table, in the right order.

Application (please complete the following information):

• Antares 0.7.25
• Installed from dmg

Environment (please complete the following information):

• MacOS
• Catalina 10.15.7
• MySQL
• community 8.0.37

[Fabio286]

Hi @bart-schaefer, it's strange that something like happens on MySQL/MariaDB. I can't replicate this issue, unfortunately. Can you do some tests on a different instance of MySQL and tell me the result?

[bart-schaefer]

Sorry for the long delay, have been traveling.

I was able to reproduce this on MySQL Community Edition 5.7.42 as well.

The tables are defined as

CREATE TABLE `contact` (
  `contact_id` int unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int unsigned NOT NULL DEFAULT '0',
  `email_id` int unsigned NOT NULL DEFAULT '0',
  `contact_creation_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `contact_email_fmt` enum('text','html','aol') DEFAULT NULL,
  `last_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`contact_id`),
  UNIQUE KEY `client_id` (`client_id`,`email_id`),
  KEY `last_modified` (`last_modified`)
) ENGINE=InnoDB

CREATE TABLE `email_bounce_log` (
  `email_bounce_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mailing_id` int unsigned DEFAULT NULL,
  `contact_id` int unsigned NOT NULL DEFAULT '0',
  `email_bounce_type` enum('soft','hard') NOT NULL DEFAULT 'soft',
  `email_bounce_class` tinyint NOT NULL DEFAULT '0',
  `email_bounce_subject` tinyint NOT NULL DEFAULT '0',
  `email_bounce_detail` tinyint NOT NULL DEFAULT '0',
  `email_bounce_handled_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  KEY `contact_id` (`contact_id`),
  KEY `email_bounce_time` (`email_bounce_time`),
  KEY `mailing_id` (`mailing_id`),
  KEY `handled_time` (`email_bounce_handled_time`)
) ENGINE=InnoDB

CREATE TABLE `email` (
  `email_id` int unsigned NOT NULL AUTO_INCREMENT,
  `email_lhs` varchar(127) NOT NULL DEFAULT '',
  `email_rhs` varchar(127) NOT NULL DEFAULT '',
  `email_fmt` enum('text','html','aol') DEFAULT NULL,
  `email_creation_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`email_id`),
  UNIQUE KEY `email_rhs` (`email_rhs`,`email_lhs`)
) ENGINE=InnoDB

And the query is

select
  contact_id,
  concat(email_lhs, '@', email_rhs) as eaddr,
  b.*
from
  email_bounce_log b
  join contact using (contact_id)
  join email using (email_id)
where
  client_id = @id1
having
  eaddr in (
    'test1@test1.com',
    'test2@hotmail.com',
    'test3@gmail.com'
  )

[Fabio286]

Thank you for the SQLs. The reported problem is due to the driver merging the same fields. In your example query the field contact_id is present in the table contact and email_bounce_log but in the select the first time it is called without specifying table and then returns twice the field present in contact. I will currently make sure to filter out duplicate fields in the header to avoid confusion.