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.
