From: Thimble Smith
Date: March 15 2000 5:12pm
Subject: Re: SQL problems.
List-Archive: http://lists.mysql.com/bugs/45
Message-Id: <20000315121222.G7604@threads.polyesthetic.msg>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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
-- 
Tim Smith   < tim@stripped >  :MySQL Development Team:  Boone, NC  USA.