problem with simple query

[Marcel]

I'm running MySQL version 4.1.7-nt.
When I submit the following query:

SELECT DISTINCT pet from pets;

I get:

dog
cat
lama
chicken

If however, I submit the following query:

SELECT DISTINCT pet from pets WHERE pet IN ('lama');

I get:

Empty set (0.00 sec).

Any suggestions? Thanks, Marcel

[Aggro]

Marcel wrote:

I get:

Empty set (0.00 sec).

I couldn't repeat that. Could you provide similar commands as I provide
below to create the table and insert test material to there which allows
to repeat your problem?

# To create test environment
create table pets(pet text);
insert into pets values('dog');
insert into pets values('dog');
insert into pets values('lama');
insert into pets values('cat');
insert into pets values('lama');

# To test it
mysql> SELECT DISTINCT pet from pets;
+------+
| pet |
+------+
| dog |
| lama |
| cat |
+------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT pet from pets WHERE pet IN ('lama');
+------+
| pet |
+------+
| lama |
+------+
1 row in set (0.00 sec)

[Marcel]

That works.
The difference between the two tables is that the table that was last
created, has YES for column Null and NULL for column Default, while in my
original table all columns where empty (apart from Field and Type of
course).

"Aggro" <sp**********@y ahoo.com> wrote in message
news:zk******** ******@read3.in et.fi...

Marcel wrote:

I get:

Empty set (0.00 sec).

I couldn't repeat that. Could you provide similar commands as I provide
below to create the table and insert test material to there which allows
to repeat your problem?

# To create test environment
create table pets(pet text);
insert into pets values('dog');
insert into pets values('dog');
insert into pets values('lama');
insert into pets values('cat');
insert into pets values('lama');

# To test it
mysql> SELECT DISTINCT pet from pets;
+------+
| pet |
+------+
| dog |
| lama |
| cat |
+------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT pet from pets WHERE pet IN ('lama');
+------+
| pet |
+------+
| lama |
+------+
1 row in set (0.00 sec)

[Marcel]

Just found it.. I loaded the data from a file, all the columns that I was
looking for started with a space...
Pretty stupid from me.
Thanks anyway, Marcel

"Marcel" <in**@nospam-bluegumdata.com .au> wrote in message
news:6%******** **********@news-server.bigpond. net.au...

That works.
The difference between the two tables is that the table that was last
created, has YES for column Null and NULL for column Default, while in my
original table all columns where empty (apart from Field and Type of
course).

"Aggro" <sp**********@y ahoo.com> wrote in message
news:zk******** ******@read3.in et.fi...

Marcel wrote:

I get:

Empty set (0.00 sec).

I couldn't repeat that. Could you provide similar commands as I provide
below to create the table and insert test material to there which allows
to repeat your problem?

# To create test environment
create table pets(pet text);
insert into pets values('dog');
insert into pets values('dog');
insert into pets values('lama');
insert into pets values('cat');
insert into pets values('lama');

# To test it
mysql> SELECT DISTINCT pet from pets;
+------+
| pet |
+------+
| dog |
| lama |
| cat |
+------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT pet from pets WHERE pet IN ('lama');
+------+
| pet |
+------+
| lama |
+------+
1 row in set (0.00 sec)