List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 15 2000 6:31pm
Subject:SQL problems.
View as plain text  
Hi!

>>>>> "Uri" == Uri Even-Chen <webmaster@stripped> writes:

Uri> hi.
Uri> I managed to use MySQL with the C API (mysql++ does not work properly).
Uri> but I found a bug or unknown problem related to SQL.

Uri> 1. COUNT(DISTINCT expr,[expr...]) does not work in my version (mysql 
Uri> Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686)), which is your
Uri> recommended version. it does appear in your manual.

In the Changelog section you can check in Which MySQL version each new
function was implemented; COUNT(DISTINCT) works only in MySQL 3.23.x

Uri> 2. a very strange behavior of GROUP BY - queries that are not allowed in
Uri> SQL still work; and queries return wrong results. here are a few
Uri> examples of my queries & the output:

Yes, MySQL has some very useful extensions to how GROUP BY works, but
these doesn't conflict with the ANSI SQL definition.

<cut>

mysql> select COUNT(DISTINCT FirstName) from Users LEFT JOIN
Uri> LanguageDescriptions USING (UserName) WHERE ((DatingStatus='A') AND
Uri> (Gender='F')) ORDER BY FirstName;
Uri> ERROR 1064: You have an error in your SQL syntax near 'DISTINCT
Uri> FirstName) from Users LEFT JO
Uri> IN LanguageDescriptions USING (UserName) W' at line 1

Uri> (not working)

As espected in MySQL 3.2.2

mysql> select FirstName from Users LEFT JOIN LanguageDescriptions USING
Uri> (UserName) WHERE ((DatingStatus='A') AND (Gender='F')) GROUP BY
Uri> FirstName;
Uri> +-----------------+
Uri> 55 rows in set (0.27 sec)

Uri> (there should be 477 names, not 55!!!)

Can you please post a full example of this so that we can verify this!

mysql> select Users.UserName, FirstName from Users LEFT JOIN
Uri> LanguageDescriptions USING (UserName) WHERE ((DatingStatus='A') AND
Uri> (Gender='F')) GROUP BY FirstName;
Uri> +--------------+-----------------+
Uri> 55 rows in set (0.28 sec)

Uri> (this is not allowed in SQL. it also returns wrong output, like before).

MySQL has a strict definition what the above would do; The number of
rows in the query sand the value if 'First Name' should of course be as
excepted by SQL

Uri> I think it's important than queries that are not allowed should not
Uri> work, instead of returning wrong results.

They should do work and return the correct answer according to the
MySQL extension GROUP BY extension;  When doing joins between tables,
the extension can speed up things a LOT!  (Please consult the MySQL
manual about this).

Uri> note - if I do the same queries without LEFT JOIN & WHERE, it works
Uri> properly. but I can't use the WHERE without the join - each data is on a
Uri> different table.

Uri> please tell me if I made a mistake, or if there is really a problem with
Uri> queries like these.

There shouldn't be any problems with the query:

select FirstName from Users LEFT JOIN LanguageDescriptions USING
(UserName) WHERE ((DatingStatus='A') AND (Gender='F')) GROUP BY
FirstName;

Please repost with mysqlbug script + a full repeatable example of your problem!

We need more information about your MySQL version and OS to be able to
help you.

You can find information how to do a correct bug report at:

http://www.tcx.se/Manual_chapter/manual_Questions.html#Asking_questions

PLEASE read the above before reposting your question, this will save us all
a lot of time!

Yours,
Monty

PS: This post should not have been posted to bugs@stripped as
    it didn't follow the guidelines required by this list.
Thread
RE: Comparing bug in 4.1.7David Brodbeck2 Dec
  • Re: Comparing bug in 4.1.7Fredrick Bartlett2 Dec