List:Bugs« Previous MessageNext Message »
From:Uri Even-Chen Date:March 15 2000 3:24pm
Subject:Re: SQL problems.
View as plain text  
Sinisa,

1. I spent too much time trying to work with mysql++, and didn't find
solution to the segmentation fault. I even installed the new GCC version
you told me, together with all the libraries - no use. after about 2
weeks of trying, I gave up, and went back to the good & old C language.

2. it is not "GROUP BY" that is not allowed, but only a few queries
using it. I'll give you an example. if I use this query:

> select DatingStatus, Gender, count(*) from Users group by DatingStatus, Gender;

I get these results:
+--------------+--------+----------+
| DatingStatus | Gender | count(*) |
+--------------+--------+----------+
| A            | F      |     1325 |
| A            | M      |     3682 |
| D            | M      |        3 |
| F            | F      |     2374 |
| F            | M      |     8665 |
| I            |        |      317 |
| I            | F      |      207 |
| I            | M      |      528 |
| Z            | F      |        1 |
+--------------+--------+----------+
9 rows in set (0.07 sec)


and if I use it like this:

> select DatingStatus, Gender, count(*) from Users group by DatingStatus;

I get these results:
+--------------+--------+----------+
| DatingStatus | Gender | count(*) |
+--------------+--------+----------+
| A            | F      |     5007 |
| D            | M      |        3 |
| F            | F      |    11039 |
| I            |        |     1052 |
| Z            | F      |        1 |
+--------------+--------+----------+
5 rows in set (0.06 sec)


which are obviously not correct. the first query is allowed in SQL, and
the second isn't. but mySQL doesn't report any problem. I also read in
your manual, 

"MySQL has extended the use of GROUP BY. You can use columns or
calculations in the SELECT expressions which don't appear in the GROUP
BY part. This stands for any
possible value for this group. "

which is very tricky & not natural. personally, I perfer an error
message than incorrect results.

is there any way to work in ANSI SQL mode, without interfering with the
installation? I installed everything from RPM files, and I don't feel
like installing it again.

3. (this is the main problem) - my tables are not corrupted, there is a
consistent difference in the results of the queries I mentioned. it just
appears that GROUP BY doesn't work properly together with JOIN & WHERE,
unless I misunderstand the SQL syntax of these queries. I can manage
working without GROUP BY, I prefer to do so than to use it & get wrong
results. but I just want you to know, there is a problem.


Uri.


sinisa@stripped wrote:
> 
> 
> 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       |
> +----------------------------------------------------------------------+

> 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
--------------------------------------------------------
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