MySQL Bugs: #68663: _BINARY introducers with same value in single query causing syntax error

Bug #68663	_BINARY introducers with same value in single query causing syntax error
Submitted:	13 Mar 2013 20:48	Modified:	19 Apr 2013 20:13
Reporter:	ABC XYZ	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.5.28	OS:	MacOS (Mountain Lion)
Assigned to:		CPU Architecture:	Any
Tags:	_binary, BINARY, parser, query, UUID

Description:
I have a many-to-many relationship in my database, using BINARY(16) UUIDs as keys. When I issue a query batch containing multiple _BINARY introducers with the same value -- for example, inserting a parent entity and a row in the junction table -- MySQL comes back with a generic syntax error in spite of the SQL being perfectly valid.

The error does not occur if multiple introducers are present and do not have the same value; inserting a row into the junction table works fine. It's only when my query uses the same binary value more than once.

How to repeat:
create table table1 (id binary(16) not null primary key, field1 varchar(50));
create table table2 (id binary(16) not null primary key, field2 varchar(50));
create table table1_table2 (table1_id binary(16) not null, table2_id binary(16) not null, 
	foreign key (table1_id) references table1(id),
	foreign key (table2_id) references table2(id));

/* Both of the following query blocks fail. You'll need to fill the binary values somehow; I've been using data mappers such as MyBatis. Also note that my insert query expects table2 to have data and you to have an id from it, it's a lookup table in my application. */

insert into table1 (id, field1) values (_binary'TABLE1 ID VAL', 'field1value'); insert into table1_table2 (table1_id, table2_id) values (_binary'TABLE1 ID VAL', _binary'TABLE2 ID VAL');

delete from table1_table2 where table1_id = _binary'TABLE1 ID VAL'; delete from table1 where id = _binary'TABLE1 ID VAL';

Not sure if I understood you correct, can you check this output?

Attachment: bug68663_output.txt (text/plain), 2.10 KiB.

The only thing I can think of is that my binary values are representing Java UUIDs and contain non-ASCII characters (_binary'?lϕpL???;?' and the like). Which, unfortunately, is more difficult to test, but it might be a problem with Connector/J?

Thank you for the report.

Please provide complete copy-paste of your MySQL session in command line client.

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".