469,080 Members | 1,722 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,080 developers. It's quick & easy.

Mysql 4.1.0 Nested Sql Statements

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 150199)?

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
Jul 20 '05 #1
3 1228
"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!
Jul 20 '05 #2
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!

Jul 20 '05 #3
"Paul" <pa**********@hotmail.com> wrote in message
Just tried it on 4.1.1alpha (windows version) and it doesnt work.


I'm using Windows.

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Neil Zanella | last post: by
175 posts views Thread by Sai Hertz And Control Systems | last post: by
3 posts views Thread by Tcs | last post: by
10 posts views Thread by nimmi_srivastav | last post: by
3 posts views Thread by tulika dutta | last post: by
reply views Thread by Neil Cerutti | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.