hi.
I managed to use MySQL with the C API (mysql++ does not work properly).
but I found a bug or unknown problem related to SQL.
1. COUNT(DISTINCT expr,[expr...]) does not work in my version (mysql
Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686)), which is your
recommended version. it does appear in your manual.
2. a very strange behavior of GROUP BY - queries that are not allowed in
SQL still work; and queries return wrong results. here are a few
examples of my queries & the output:
mysql> select FirstName from Users LEFT JOIN LanguageDescriptions USING
(UserName) WHERE ((DatingStatus='A') AND (Gender='F')) ORDER BY
FirstName;
+-----------------+
1329 rows in set (0.28 sec)
(OK)
mysql> select COUNT(FirstName) from Users LEFT JOIN LanguageDescriptions
USING (UserName) WHERE ((DatingStatus='A') AND (Gender='F')) ORDER BY
FirstName;
+------------------+
| COUNT(FirstName) |
+------------------+
| 1329 |
+------------------+
1 row in set (0.21 sec)
(OK)
mysql> select DISTINCT FirstName from Users LEFT JOIN
LanguageDescriptions USING (UserName) WHERE ((DatingStatus='A') AND
(Gender='F')) ORDER BY FirstName;
+-----------------+
477 rows in set (2.70 sec)
(OK)
mysql> select COUNT(DISTINCT FirstName) from Users LEFT JOIN
LanguageDescriptions USING (UserName) WHERE ((DatingStatus='A') AND
(Gender='F')) ORDER BY FirstName;
ERROR 1064: You have an error in your SQL syntax near 'DISTINCT
FirstName) from Users LEFT JO
IN LanguageDescriptions USING (UserName) W' at line 1
(not working)
mysql> select FirstName from Users LEFT JOIN LanguageDescriptions USING
(UserName) WHERE ((DatingStatus='A') AND (Gender='F')) GROUP BY
FirstName;
+-----------------+
55 rows in set (0.27 sec)
(there should be 477 names, not 55!!!)
mysql> select Users.UserName, FirstName from Users LEFT JOIN
LanguageDescriptions USING (UserName) WHERE ((DatingStatus='A') AND
(Gender='F')) GROUP BY FirstName;
+--------------+-----------------+
55 rows in set (0.28 sec)
(this is not allowed in SQL. it also returns wrong output, like before).
I think it's important than queries that are not allowed should not
work, instead of returning wrong results.
note - if I do the same queries without LEFT JOIN & WHERE, it works
properly. but I can't use the WHERE without the join - each data is on a
different table.
please tell me if I made a mistake, or if there is really a problem with
queries like these.
Uri.
--------------------------------------------------------
MySQL license: #7259
MySQL basic email support: #489
--------------------------------------------------------
Uri Even-Chen
Speedy Software
Raanana, Israel.
E-mail: webmaster@stripped
ICQ Number: 7461268
Phone Number: 09-7715013
--------------------------------------------------------
Welcome to Speedy Net (In Hebrew):
http://www.speedy.co.il/
Speedy Dating (In Hebrew):
http://www.speedy.co.il/dating/
Speedy Composer (In English):
http://www.speedy.co.il/composer/
--------------------------------------------------------
