[MySQL] Inspection hint: column aliases required for...

Hello.

Could you show your table definitions? I've tried your sample with arbitrary tables and got no warning.

Regards,
Alexander.

Sure thing, no crown jewels in these tables. :)

CREATE TABLE `address` (
  `addressid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` char(5) NOT NULL,
  `postcode` char(10) NOT NULL,
  `firstline` char(50) NOT NULL DEFAULT '-',
  PRIMARY KEY (`addressid`),
  KEY `number` (`number`,`postcode`),
  KEY `postcode` (`postcode`,`number`)
) ENGINE=InnoDB AUTO_INCREMENT=44520 DEFAULT CHARSET=utf8

CREATE TABLE `addresslines` (
  `addressid` int(10) unsigned NOT NULL DEFAULT '0',
  `addressline` int(10) unsigned NOT NULL DEFAULT '0',
  `text` varchar(250) NOT NULL,
  PRIMARY KEY (`addressid`,`addressline`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The stored function does some crunching and gives a hash for each address - the field is called {{firstline}} for historial reasons.

I have discovered some extra information, however, which might need a bit more digging on my part to figure out how to recreate it concidely.
When I copied the statement to a console the warning disappears; so I created a completely fresh sql file and found that on its own the error is not displayed.  I surmise that the problem must be due to something else within the sql file - this is over 700 lines long and performing a whole host of updates to a system ranging from adding tables, views and stored programs to rearranging data and eliminating some unused 'temporary' tables.

Okay, after further digging, it is being caused by a previous line.

Allow me to describe the position for this particular table - the {{firstline}} field gets dropped and then recreated before being filled with the calculated hash code... there is quite a lot goes on between those two actions, but the final two actions are sufficient to demonstrate the problem.

# CREATE TABLE address
# (
#     addressid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
#     number CHAR(5) NOT NULL,
#     postcode CHAR(10) NOT NULL
# );
# CREATE INDEX number ON address (number, postcode);
# CREATE INDEX postcode ON address (postcode, number);
# 
# CREATE TABLE addresslines
# (
#     addressid INT UNSIGNED DEFAULT 0 NOT NULL,
#     addressline INT UNSIGNED DEFAULT 0 NOT NULL,
#     text VARCHAR(250) NOT NULL,
#     PRIMARY KEY (addressid, addressline)
# );

ALTER TABLE `address`
ADD COLUMN `firstline` CHAR(50) DEFAULT '-' NOT NULL
AFTER `postcode`;

UPDATE
  `address` a
SET
  a.firstline =
  (SELECT
      GROUP_CONCAT(
          l.text
          ORDER BY l.addressline SEPARATOR ' '
      ) AS firstline
   FROM
     addresslines l
   WHERE a.addressid = l.addressid
   GROUP BY l.addressid
   ORDER BY l.addressline);

With the table definitions commented out, the error is displayed; with the table definitions uncommented, the error goes away.

I was thinking that this was a problem that was being picked up in my code, but maybe I should be logging it in YouTrack as a bug?

Thank you for detailed description. I have reproduced it in a bit different case: when same-named column appears in the table definition _and_ alter statement. Like this:

create table tab_1 (id integer primary key, subject char(50));
alter table tab_1 drop column subject;
alter table tab_1 add column subject char(55);
update tab_1 t set tab_1.subject = 'a';

(Drop column statement is not essential to reproduce, it is added just for "realism", to make the whole script valid.) If the column is mentioned only in the table definition, or only in an alter statement (like in your latter sample), then there is no warning for me. This case really looks like some IDE defect, since the warning is inadequate with suggested conditions.

Do you actually have some similar operations with columns in your script? Some removal or maybe rename?

Thank you,
Alexander.

Hi Alexander,

Yes, all similar warnings are occuring at places where there are fieldnames referenced that also appear in either drop or alter statements further up the file.  I have traced each one now and for this to be the case.

I've found something similar with using temporary tables in a stored program too, where the table name appears to get sort of forgotten --- I'm trying to pin down the exact circumstances before I upload it, but that is almost certainly something to go on YouTrack.

I'm sorry for being slow to respond - I'm trying to complete an Open University assignment at the moment and cannot spend as much time on 0xDBE as I'd like to.

Kind regards,
Geoff

Thanks a lot, I think the case is clear enough. Created: https://youtrack.jetbrains.com/issue/DBE-1195

Regards,
Alexander.

Thanks Alexander.