Mysql 4.1.0 Nested Sql Statements

[Paul]

I've just noticed this pecularity with mysql 4.1.0 running on windows,
wondered if anyone could explain it / or is it a bug.

I have a table called tokContact, which has a column called id, which
has unique values from 1 up into the thousands with no gaps.

This sql statement :

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id > 10 and id < 150)

returns rows 1..10, 150..199 (As expected)

The problem is if I try :

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id < 150)

I get an empty result set (why don't I get 150…199)?

Whilst :

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id > 150)

works as expected, giving 1..150

Thanks,

Paul

[Siemel Naran]

"Paul" <pa**********@hotmail.com> wrote in message

Using 4.1.1a-alpha:

create table tokContact (id int(1));
insert into tokContact(id) values (1), (2), (10), (11), (12), (149), (150),
(151), (199), (200), (201);

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id > 10 and id < 150)

returns rows 1..10, 150..199 (As expected)
Right, I get 1, 2, 10, 150, 151, 199.
The problem is if I try :

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id < 150)

I get an empty result set (why don't I get 150.199)?
I get 150, 151, 199 which is what you expect.

Whilst :

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id > 150)

works as expected, giving 1..150

Right, I get 1, 2, 10, 11, 12, 149, 150.
Maybe secret is to upgrade!

[Paul]

Just tried it on 4.1.1alpha (windows version) and it doesnt work.

I guess you are running on Linux?

Thanks,

Paul

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message news:<QN*********************@bgtnsc04-news.ops.worldnet.att.net>...

"Paul" <pa**********@hotmail.com> wrote in message

Using 4.1.1a-alpha:

create table tokContact (id int(1));
insert into tokContact(id) values (1), (2), (10), (11), (12), (149), (150),
(151), (199), (200), (201);

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id > 10 and id < 150)

returns rows 1..10, 150..199 (As expected)

Right, I get 1, 2, 10, 150, 151, 199.

The problem is if I try :

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id < 150)

I get an empty result set (why don't I get 150.199)?

I get 150, 151, 199 which is what you expect.

Whilst :

select id from tokContact where id < 200 and id <> ALL (select id from
tokContact where id > 150)

works as expected, giving 1..150

Right, I get 1, 2, 10, 11, 12, 149, 150.
Maybe secret is to upgrade!

[Siemel Naran]

"Paul" <pa**********@hotmail.com> wrote in message

Just tried it on 4.1.1alpha (windows version) and it doesnt work.

I'm using Windows.