Re: SQL problems. - Thimble Smith - com.mysql.lists.bugs

4 messages in com.mysql.lists.bugsRe: SQL problems.

From	Sent On	Attachments
Uri Even-Chen	15 Mar 2000 00:51
sin...@mysql.com	15 Mar 2000 04:56
Uri Even-Chen	15 Mar 2000 07:23
Thimble Smith	15 Mar 2000 09:12

Subject:	Re: SQL problems.
From:	Thimble Smith (ti...@mysql.com)
Date:	03/15/2000 09:12:00 AM
List:	com.mysql.lists.bugs

On Wed, Mar 15, 2000 at 05:24:03PM +0200, Uri Even-Chen wrote:

I get these results: +--------------+--------+----------+ | DatingStatus | Gender | count(*) | +--------------+--------+----------+ | A | F | 1325 | | A | M | 3682 | | D | M | 3 | | F | F | 2374 | | F | M | 8665 | | I | | 317 | | I | F | 207 | | I | M | 528 | | Z | F | 1 | +--------------+--------+----------+ 9 rows in set (0.07 sec)

and if I use it like this:

select DatingStatus, Gender, count(*) from Users group by DatingStatus;

I get these results: +--------------+--------+----------+ | DatingStatus | Gender | count(*) | +--------------+--------+----------+ | A | F | 5007 | | D | M | 3 | | F | F | 11039 | | I | | 1052 | | Z | F | 1 | +--------------+--------+----------+ 5 rows in set (0.06 sec)

which are obviously not correct. the first query is allowed in SQL, and the second isn't. but mySQL doesn't report any problem. I also read in your manual,

The above is correct; why do you think it is incorrect? You get some unspecified value for Gender; you get the correct totals in count(*). This is the way the manual describes this extension.

I haven't had the chance to set up some test data and figure out your previous problem. I'll do that shortly.

To run MySQL in ANSI mode, start mysqld with the --ansi flag. This doesn't change MySQL's behaviour in the above case, though; --ansi only affects MySQL's behaviour that would cause ANSI-compliant code to break in MySQL. So there is no way, right now, of making MySQL throw an error on the above code.

Tim