Select 1 Returns Wrong Results?

[Donna]

select 1 from test where testdata=200;

In SQLyog, this returns 1 whether or not there is a matching row in the table. In the MySQL client, it returns 1 if it finds at least one matching row, otherwise it returns an empty set. I thought it was maybe my SQL Mode, but I set the session sql mode to be the same as them MySQL server, and it still didn't return the expected value. Is this a bug?

Donna

Test Case:

CREATE TABLE `test` (

`testid` int(11) NOT NULL auto_increment,

`testdata` int(11) NOT NULL,

PRIMARY KEY (`testid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

insert into test (testdata) values (100), (300);

select 1 from test where testdata=200;

mysql> select 1 from test where testdata=200;

Empty set (0.00 sec)

[peterlaursen]

and I assume that

SELECT `1` FROM …..  (backticks used) returnswhat you expected?

[Donna]

The command: select `1` from test returns

Error Code : 1054

Unknown column '1' in 'field list'

(0 ms taken)

I would expect that the command: select 1 from test where testdata=200;

would return 1 if there exists at least one matching row, and an empty set when no matching rows exist. This is the behavior I see when I run that query in the MySQL client, but in SQLyog it returns 1 whether a matching row exists or not.

Donna

[adarsh]

hi,

I think what you want is to print `TRUE`(1) if there exists at least one match else `FALSE`(0)

for that you can use :

select count(*) >= 1 from test where testdata = 5;

This will return `1` if there is one or more rows that satisfies the given condition.

else if no match is found it will return `0`

If you want the number of matching rows then simply execute the following.

select count(*) from test where testdata = 5;

hope that will solve the problem.

[Rohit]

We will investigate the difference in behaviour (if any) between MySQL client and SQLyog according to your test case. We will keep you updated.

[Donna]

adarsh wrote on Apr 4 2007, 08:50 AM:

hi,

I think what you want is to print `TRUE`(1) if there exists at least one match else `FALSE`(0)

for that you can use :

select count(*) >= 1 from test where testdata = 5;

This will return `1` if there is one or more rows that satisfies the given condition.

else if no match is found it will return `0`

If you want the number of matching rows then simply execute the following.

select count(*) from test where testdata = 5;

hope that will solve the problem.

That's not really what I wanted to do, the example was simple to demonstrate the difference between SQLyog and the MySQL client. What I want to do is something like this:

SELECT something FROM table1

WHERE EXISTS (SELECT 1 FROM table2 WHERE column1=somevalue)

[adarsh]

@Donna:

I checked with your test case.

Both MySQL client and SQLyog return an empty result set. In case of SQLyog, it returns the column headers even for an empty result set. MySQL client does not return column headers for an empty result set.

So the “1” that you see is the column header – the resultset is empty.

[Donna]

adarsh wrote on Apr 5 2007, 11:32 AM:

@Donna:

I checked with your test case.

Both MySQL client and SQLyog return an empty result set. In case of SQLyog, it returns the column headers even for an empty result set. MySQL client does not return column headers for an empty result set.

So the “1” that you see is the column header – the resultset is empty.

I feel like a real idiot! Sorry to have wasted your time.

Donna

[Author]

Posts