List:Bugs« Previous MessageNext Message »
From:Uri Even-Chen Date:March 15 2000 8:52am
Subject:SQL problems.
View as plain text  
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/
--------------------------------------------------------
Thread
SQL problems.Uri Even-Chen15 Mar
  • Re: SQL problems.sinisa15 Mar
  • Re: SQL problems.Uri Even-Chen15 Mar
    • Re: SQL problems.Thimble Smith15 Mar