MySQL Bugs: #15753: column alias not recognized in a query that uses views

Bug #15753	column alias not recognized in a query that uses views
Submitted:	14 Dec 2005 18:11	Modified:	23 Dec 2005 11:24
Reporter:	Kevin Gilpin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.16, 5.0.17	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
The following query is being executed. Each table (ont_subject and ont_property) is a view created SELECT * FROM subject and SELECT * FROM property, respectively.

select distinct subject0_.id as id4_0_, properties1_.id as id7_1_, subject0_.namespace_id as namespace3_4_0_, subject0_.local_name as local2_4_0_, properties1_.subject_id as subject9_7_1_, properties1_.created_version as created3_7_1_, properties1_.deleted_version as deleted4_7_1_, properties1_.ontology_id as ontology10_7_1_, properties1_.meta_id as meta11_7_1_, properties1_.priority as priority7_1_, properties1_.value_boolean as value6_7_1_, properties1_.value_analyzed as value7_7_1_, properties1_.value_text as value8_7_1_, properties1_.object_id as object12_7_1_, properties1_.value_type as value1_7_1_, properties1_.subject_id as subject9_0__, properties1_.id as id0__ from ont_subject subject0_ inner join ont_property properties1_ on subject0_.id=properties1_.subject_id where subject0_.id in (? , ? , ? , ? , ? , ? , ? , ? , ? , ?)

Reading the result set fails in ResultSet.findColumn line 950 (column not found) as the column id7_1_ cannot be located. Note that this column alias refers to property.id which is also referenced later as id0__

Looking at the toString() of the Field[] fields in the debugger shows that fields[1] and fields[16] are both

test . property(property) . id0__(id0__), Mysql type: 3

Somehow the first alias is being lost

How to repeat:
CREATE TABLE `property` (
  `value_type` char(1) NOT NULL default '',
  `id` int(11) NOT NULL auto_increment,
  `deleted_version` int(11) default NULL,
  `value_analyzed` varchar(255) default NULL,
  `value_text` text,
  `subject_id` int(11) NOT NULL default '0',
  `meta_id` int(11) NOT NULL default '0',
  `ontology_id` int(11) NOT NULL default '0',
  `object_id` int(11) default NULL,
  `priority` int(11) NOT NULL default '0',
  `created_version` int(11) NOT NULL default '0',
  `value_boolean` tinyint(1) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `subject` (
  `id` int(11) NOT NULL auto_increment,
  `local_name` varchar(255) NOT NULL default '',
  `namespace_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If I run this query in the MySQL console the wrong column alias is printed, so apparently the mix-up is occurring on the server side

If the tables are real tables and not views, the correct column name (id7_1_) is shown on the console.

It looks like a problem with the fix to this issue

http://bugs.mysql.com/bug.php?id=11399

changed from Connector/J version (where first seen) to MySQL server version

Could you please provide the create view and the query for
to complete the how to repeat.

Thanks in advance.

CREATE OR REPLACE VIEW ont_ontology AS SELECT * FROM ontology;

CREATE OR REPLACE VIEW ont_property AS SELECT * FROM property;

Sorry still I am not able for to test your case using the console:

win5018>> CREATE OR REPLACE VIEW ont_ontology AS SELECT * FROM ontology;
ERROR 1146 (42S02): Table 'db1.ontology' doesn't exist

also you didn't added the select query for to test with the mysql client.

Thanks in advance.

Should be 

CREATE OR REPLACE VIEW ont_subject AS SELECT * FROM subject;

to match the subject in the create table script

The query is right there in the issue description

Sorry, but I was not able to repeat the problem you described with 5.0.19:

mysql> CREATE TABLE `property` (
    ->   `value_type` char(1) NOT NULL default '',
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `deleted_version` int(11) default NULL,
    ->   `value_analyzed` varchar(255) default NULL,
    ->   `value_text` text,
    ->   `subject_id` int(11) NOT NULL default '0',
    ->   `meta_id` int(11) NOT NULL default '0',
    ->   `ontology_id` int(11) NOT NULL default '0',
    ->   `object_id` int(11) default NULL,
    ->   `priority` int(11) NOT NULL default '0',
    ->   `created_version` int(11) NOT NULL default '0',
    ->   `value_boolean` tinyint(1) default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `subject` (
     ->   `id` int(11) NOT NULL auto_increment,
    ->   `local_name` varchar(255) NOT NULL default '',
    ->   `namespace_id` int(11) NOT NULL default '0',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE OR REPLACE VIEW ont_subject AS SELECT * FROM subject;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE OR REPLACE VIEW ont_property AS SELECT * FROM property;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into property () values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into property () values();
Query OK, 1 row affected (0.08 sec)

mysql> insert into property () values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into property () values();
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into subject () values();
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into subject () values();
Query OK, 1 row affected (0.00 sec)

mysql> update property set subject_id = 1;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select distinct subject0_.id as id4_0_, properties1_.id as id7_1_, subje
ct0_.namespace_id as namespace3_4_0_, subject0_.local_name as local2_4_0_, properties1_.subject_id as subject9_7_1_, properties1_.created_version as created3_7_1_, properties1_.deleted_version as deleted4_7_1_, properties1_.ontology_id as ontology10_7_1_, properties1_.meta_id as meta11_7_1_, properties1_.priority a
s priority7_1_, properties1_.value_boolean as value6_7_1_, properties1_.value_analyzed as value7_7_1_, properties1_.value_text as value8_7_1_, properties1_.object_id as object12_7_1_, properties1_.value_type as value1_7_1_, properties1_.subject_id as subject9_0__, properties1_.id as id0__ from ont_subject subject0_
 inner join ont_property properties1_ on subject0_.id=properties1_.subject_id where subject0_.id in (1,2,3);
+--------+--------+-----------------+-------------+---------------+-------------
--+---------------+-----------------+-------------+--------------+-------------+
-------------+-------------+---------------+-------------+--------------+-------
+
| id4_0_ | id7_1_ | namespace3_4_0_ | local2_4_0_ | subject9_7_1_ | created3_7_1
_ | deleted4_7_1_ | ontology10_7_1_ | meta11_7_1_ | priority7_1_ | value6_7_1_ |
 value7_7_1_ | value8_7_1_ | object12_7_1_ | value1_7_1_ | subject9_0__ | id0__
|
+--------+--------+-----------------+-------------+---------------+-------------
--+---------------+-----------------+-------------+--------------+-------------+
-------------+-------------+---------------+-------------+--------------+-------+
|      1 |      1 |               0 |             |             1 |
0 |          NULL |               0 |           0 |            0 |        NULL |
 NULL        | NULL        |          NULL |             |            1 |     1 |
|      1 |      2 |               0 |             |             1 |
0 |          NULL |               0 |           0 |            0 |        NULL |
 NULL        | NULL        |          NULL |             |            1 |     2 |
|      1 |      3 |               0 |             |             1 |
0 |          NULL |               0 |           0 |            0 |        NULL |
 NULL        | NULL        |          NULL |             |            1 |     3 |
|      1 |      4 |               0 |             |             1 |
0 |          NULL |               0 |           0 |            0 |        NULL |
 NULL        | NULL        |          NULL |             |            1 |     4 |
+--------+--------+-----------------+-------------+---------------+-------------
--+---------------+-----------------+-------------+--------------+-------------+
-------------+-------------+---------------+-------------+--------------+-------+
4 rows in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

So, please, check the above statements in your 5.0.16. If you still get erronious name in mysql client, then this may be a duplicate of bug #14861, really. Please, check.

If mysql client works OK, then it is Connector/J issue. Move it to that category and inform about the version of connector used.

I ran your exact statements in my mysql client and I did see the problem that I reported. I confirmed that my version is 5.0.16

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16-nt |
+-----------+

The latest package I see on mysql.com is 5.0.17. So it appears that the problem definitely occurs in 5.0.16 but is probably fixed in 5.0.19; however I don't have a way to verify that right now.

Looks like the bug #14861 is a real reason. I've just checked 5.0.17, and it gave me 2 id0__ columns (second and at the end). Fix to bug #14861 is included into 5.0.18, so, please, wait for that version to be released or build yourself from current 5.0.x sources.

I mark this bug report as a duplicate of bug #14861.