List:Bugs« Previous MessageNext Message »
From:<sinisa Date:March 15 2000 12:56pm
Subject:Re: SQL problems.
View as plain text  
Uri Even-Chen writes:
 > 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


Hi!

First of all MySQL++ version 1.6 works just fine and many, many of
programmers use it happily without any problem. There is just one
Makefile quirk in examples directory that will be fixed soon. Other
then that, there are no reported bugs. 

What is your problem with MySQL++ ??

Count(distinct ..) as it is clearly stated in manual is
introduced 9in 3.23.0.

GROUP BY queries are allowed in SQL. They are part of standard SQL and 
every RDBMS supports it. Please explain why do you think that GROUP BY 
is not allowd in SQL.

Regarding a difference in result set size between DISTINCT and GROUP
BY , please check the table with isamchk, that could be a possible
cause of difference. 

Regards,

Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped     |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+



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