MySQL Bugs: #60467: Connector/J incorrectly reuses byte[] fields for aliased fields

Bug #60467	Connector/J incorrectly reuses byte[] fields for aliased fields
Submitted:	14 Mar 2011 22:11	Modified:	14 Mar 2011 23:20
Reporter:	Richard Kennard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I try to select the same byte[] field, from the same table, twice in the same query. I alias the table to avoid a clash:

select rr1.id, rr2.id from ReportRun rr1, ReportRun rr2 where rr1.id != rr2.id

In MySQL Query Browser (and other such tools) the query returns fine. Through Connector/J I get back a List<Object[]> where each Object[] contains two byte arrays. So far so good.

BUT both byte arrays on every row have identical contents. They further have the same system id, indicating they are actually pointing to the same memory address. If I add a 'hex(...)' function:

select hex(rr1.id), hex(rr2.id) from ReportRun rr1, ReportRun rr2 where rr1.id != rr2.id

Then I get back two Strings, and the Strings are different as expected.

How to repeat:
Create a table containing a binary column. Select the same column twice from this table, aliased to avoid name conflicts. For example:

select a.id, b.id from Foo a, Foo b where a.id != b.id

Return this through Connector/J and check the byte array contents.

Suggested fix:
As a workaround, convert the type to something other than a binary. For example, use 'select hex(id)'.

I believe in the code Connector/J must be allocating a byte array, and then incorrectly reusing it rather than cloning it. This is a common mistake.

Could you post a bit of sample code that demonstrates the issue? "select a.id, b.id from Foo a, Foo b where a.id != b.id Return this through Connector/J and check the byte array contents." leaves a lot of of potential combinations to test. Are you trying to select the column via name, or index? Does the result change if you select via index vs. name? Knowing how the driver works, I don't see how this would be feasible via. index-based ResultSet.getByte(int), but you very well might be getting the exact same column back for ResultSet.get("id"), because the JDBC spec says you should.

Mark,

Thanks for your quick response. My apologies for being too vague. I am actually doing this through JPA:

Query query = mEntityManger.createNativeQuery( "select rr1.id, rr2.id from ReportRun rr1, ReportRun rr2 where rr1.id != rr2.id" );
List<Object[]> results = query.getResultList();

So there are probably a few layers before we get to ResultSet.getByte(). But I am doing 'createNativeQuery' (not 'createQuery') so I was imagining the problem must be with Connector/J. Perhaps it is not, perhaps it is the layer that converts ResultSet into List<Object[]>.

Regards,

Richard.

Here's a test case that demonstrates the driver is doing the right thing (if I understand what you're reporting):

public void testBug60467() throws Exception {
		createTable("testBug60467_1", "(field1 varbinary(32))");
		this.stmt.executeUpdate("INSERT INTO testBug60467_1 VALUES (0x0a)");
		this.stmt.executeUpdate("INSERT INTO testBug60467_1 VALUES (0x0b)");
		this.rs = this.stmt.executeQuery("SELECT t1.field1, t2.field1 FROM testBug60467_1 t1, testBug60467_1 t2 WHERE t1.field1 != t2.field1");
		this.rs.next();

                // these are two different values, byte[].equals() is object.equals(), and thus compares the  
                // memory locations
		assertFalse(this.rs.getBytes(1).equals(this.rs.getBytes(2)));
		
                // We expect this, if we're addressing the column by name
                assertEquals(this.rs.getBytes("field1"), this.rs.getBytes("field1"));
	}

I'm assuming that your JPA implementation is trying to ask for the two different columns by name, which won't work because the JDBC spec states that for repeated aliases, it's the first one that matches that should always be returned. Maybe there's some way to ask it to use the indices of the column instead?

Apologies. I have dug a little further, and there is indeed *much* more going on between calling 'getResultList(...)' and it returning 'List<Object[]>'. I have traced it through the code and found the problem inside JPA (Hibernate). There appears to be a known issue here:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-5040

Thank you so much for your fast response, and the time you spent on this. Apologies again for not doing enough leg-work beforehand!