I've got a program that's been working fine under MySQL 5.0.24. I ran
it on a machine with 5.0.32 installed, and it started failing. I've
narrowed the problem down to this simple test case:

create table A (id bigint);
create table B (id bigint, b varchar(255), c bigint);
insert into A values (1);
insert into B values (1, 'X', 1);
select * from A a where 'X' in (select b.b from B b where a.id=b.c);

Under 5.0.24, this returns 1 row. Under 5.0.32, this returns 0 rows.

I'm baffled.

Any suggestions?

Thanks.

--Tim

On 31 Mar, 10:36, "tmcc...@yahoo-inc.com" <ksuwild...@gmail.com>
wrote:
> I've got a program that's been working fine under MySQL 5.0.24. I ran
> it on a machine with 5.0.32 installed, and it started failing. I've
> narrowed the problem down to this simple test case:
>
> create table A (id bigint);
> create table B (id bigint, b varchar(255), c bigint);
> insert into A values (1);
> insert into B values (1, 'X', 1);
> select * from A a where 'X' in (select b.b from B b where a.id=b.c);
>
> Under 5.0.24, this returns 1 row. Under 5.0.32, this returns 0 rows.
>
> I'm baffled.
>
> Any suggestions?
>
> Thanks.
>
> --Tim

I don't have that version available at the moment, but surely
SELECT
a.*
FROM A a
JOIN B b ON a.id = b.c AND b.b = 'X'
is a better way to achieve this.

On Mar 31, 6:29 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> I don't have that version available at the moment, but surely
> SELECT
> a.*
> FROM A a
> JOIN B b ON a.id = b.c AND b.b = 'X'
> is a better way to achieve this.

Thanks for the reply. Sure, but this SQL is being generated by an ORM
tool (Hibernate), so I don't have control over the SQL that it
generates. Rather than "don't do that", I'd really like to know why
such a simple query stopped working.