Page 1 of 3

MySQL bug?

Posted: Wed Aug 15, 2007 10:19 am
by VladSun
While trying to build my SQL query I found this to be working:

[sql] SELECTestates.id AS estates_id, estates.price AS estates_price,estate_pictures.name AS pic_name,FROM estatesLEFT JOIN estate_pictures ON estate_pictures.estate_id = estates.idGROUP BY estates.id [/sql]
 
AND that what I wanted it TO be:
[sql] SELECTestates.id AS estates_id, estates.price AS estates_price,         (SELECT estate_pictures.name         FROM estate_pictures WHERE estate_pictures.estate_id=estates.id         ORDER BY rand() LIMIT 1) AS pic_name,FROM estates [/sql]

The question is: Why the first query is considered correct by MySQL? I've tried it in PGSQL and it gave an error message ...

PS: MySQL 5.0.16-max

Posted: Wed Aug 15, 2007 10:37 am
by CoderGoblin
You cannot have a select in the return area. It must be in the FROM area.

Posted: Wed Aug 15, 2007 10:44 am
by VladSun
Well, the only error I see in my queries above is the comma before FROM ...
I can't understand what are you talking about? What is "return area" - is it "select area"?

Posted: Wed Aug 15, 2007 10:55 am
by CoderGoblin
Given SELECT [return area] FROM [from area] WHERE [Condition Area]...

Code: Select all

(SELECT estate_pictures.name
         FROM estate_pictures WHERE estate_pictures.estate_id=estates.id
         ORDER BY rand() LIMIT 1) AS pic_name
must be in the [from area]. You have it in the [return area]

try

Code: Select all

SELECT estates.id AS estates_id, estates.price AS estates_price, name
FROM estates LEFT OUTER JOIN  (SELECT estates_id,estate_pictures.name
         FROM estate_pictures
         ORDER BY rand() LIMIT 1) AS pic_name ON pic_name.estate_id=estates.estates_id

Posted: Wed Aug 15, 2007 10:57 am
by superdezign
VladSun wrote:Well, the only error I see in my queries above is the comma before FROM ...
I can't understand what are you talking about? What is "return area" - is it "select area"?
In other works, you can't have a select in a select. It could return more than one result. It's like creating a virtual table / row.

Posted: Wed Aug 15, 2007 11:03 am
by VladSun
[s]OK, so there are two bugs in MySQL engine???
[/s]
Because the second query works exactly as I expected. :)

[s]Thanks for pointing me to this subselect restriction.[/s]

Posted: Wed Aug 15, 2007 11:14 am
by superdezign
VladSun wrote:OK, so there are two bugs in MySQL engine???
... What bug?

Re: MySQL bug?

Posted: Wed Aug 15, 2007 11:16 am
by VladSun
VladSun wrote: I've tried it in PGSQL and it gave an error message ...

PS: MySQL 5.0.16-max

Posted: Wed Aug 15, 2007 11:26 am
by superdezign
But what bug? Where is a bug?

Posted: Wed Aug 15, 2007 3:26 pm
by VladSun
..................
In the first query, can anybody show me where is the aggregate function which is required when using the GROUP BY function?
Then can anybody show me the SQL standard which this query confirms to?
And finaly can anybody explain me which record of estate_pictures should I expect to be returned and why?

For every query I've post in this topic you have found a reason not to be working - but ALL of them are parsed, executed and they return a row without any error indication?

So isn't this qualiffied as a bug?

PS: Using PGSQL, which I believe is closer to SQL standard than MySQL, raises an error when executing these queries ... It is another clue that MySQL engine have some bugs ...

PPS: I've searched hard but couldn't find a page which restricts the subselect to be in the "conditional area"... Could you point one at me?

Posted: Wed Aug 15, 2007 3:55 pm
by VladSun
Hm ...
A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries.
@superdezign: So ... it could be one row, column or a table ... it's up to the developer.

Posted: Wed Aug 15, 2007 4:02 pm
by superdezign
I wouldn't say that MySQL allowing a GROUP BY clause in that situation is a bug, as it would have no affect on the outcome, anyway. It's possible that the developers decided that throwing an error would be unnecessary.
VladSun wrote:@superdezign: So ... it could be one row, solumn or a table ... it's up to the developer.
Yes, and the fact that the result could be any of those is why it cannot be a part of the SELECT. The fact that you can make it return a bunch of different types of results makes it so that you could still accomplish what you want by returning a row containing all of the other results too... I'm not sure how Postgre handles all of the things that MySQL does, but I've grown used to the way MySQL works.

Of course, they do admit multiple times in the manual that subqueries are still being developed and shouldn't be seen as perfect, but there are plenty of workarounds.

Posted: Wed Aug 15, 2007 4:09 pm
by VladSun
superdezign wrote:I wouldn't say that MySQL allowing a GROUP BY clause in that situation is a bug, as it would have no affect on the outcome, anyway. It's possible that the developers decided that throwing an error would be unnecessary.
Well, in case that I remove GROU BY statement in the first query there would be an SQL error ... So, indeeed, it has a really big effect...

PS: I've been discussing it with a friend who is a technical director in a middle sized software company - both agreed that the first SQL query of the shown above is not correct. Even his DB admin agree with this.

Posted: Wed Aug 15, 2007 4:29 pm
by superdezign
VladSun wrote:Well, in case that I remove GROU BY statement in the first query there would be an SQL error ... So, indeeed, it has a really big effect...
Really?

I stand corrected.

Posted: Wed Aug 15, 2007 4:34 pm
by VladSun
OK, indeed there would be a wrong result instead :) You are right - no SQL error :)
Instead, there would be multiple rows and I'd wanted just one - in this case GROUP BY gives only one result, but using it in *this* query is worng according to SQL standrards, isn't it?