MySQL Bugs: #15821: Error while reading data from resultset using table aliases

Bug #15821	Error while reading data from resultset using table aliases
Submitted:	17 Dec 2005 11:03	Modified:	26 Feb 2006 21:45
Reporter:	Alexander Tarasyuk	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql-standard-5.0.18-linux-i686	OS:	Linux (Red Hat Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Cannot read data from resultset using table aliases.
See code below.

This code was worked when I use mysql 4.0.12 and connector/J 3.0.8
But when I use mysql 5.0.16 and connector/J 3.1.12 I have exception.

How to repeat:
SQL for tables:
CREATE TABLE newslettersubscribers (
  id bigint(20) NOT NULL,
  email varchar(100) NOT NULL default '',
  registrationtime bigint(20) NOT NULL default '0',
  ip varchar(15) default NULL,
  firstname varchar(250) default NULL,
  lastname varchar(250) default NULL,
  PRIMARY KEY  (id),
  KEY byemail (email)
);

CREATE TABLE newslettersubscribersingroups (
  subscriberid bigint(20) NOT NULL,
  groupid bigint(20) NOT NULL,
  PRIMARY KEY  (subscriberid,groupid)
);

SQL to execute (works properly in mysql console):
select * from newslettersubscribers a, newslettersubscribersingroups b where a.id=b.subscriberid and b.groupid=?;

Java code to read data:
.......
Connection con = ..........
PreparedStatement stmt = con.prepareStatement("select * from newslettersubscribers a, newslettersubscribersingroups b where a.id=b.subscriberid and b.groupid=?");
stmt.setLong(1, 1120732740992);
ResultSet rs = stmt.executeQuery();
rs.next();
String s = rs.getString("a.email"); // crash
String s1 = rs.getString("email"); // works properly
.......

When I use same mysql connector but older mysql engine (4.0.12) this code works properly.
So I think that problem is in mysql engine but not in connector.

Could you please verify if the bug http://bugs.mysql.com/bug.php?id=14861
can be related to your case and to test with 5.0.18 when released?

Thanks in advance.

I was test this bug in mysql 5.0.18 but I still have the same error.

There is similar bug report: http://bugs.mysql.com/bug.php?id=14897
I found several solutions: 
1) If I dump tables in mysql 4 and create them in mysql 5 - everything works fine.
2) If I copy tables from mysql4 to mysql 5 and then run repair table on each table - everything works fine.
So I think this should be included to mysql manual (upgrading from 4.1 to 5.0 section)
By the way, repair operation is not very fast on big tables. So maybe there is some fast utility? Because in our case we should migrate without long downtime.

Then according with your last post this bug can be considered duplicate
of bug  http://bugs.mysql.com/bug.php?id=14897 ?

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".