MySQL bug?
Moderator: General Moderators
MySQL bug?
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
[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
Last edited by VladSun on Mon Jan 23, 2017 9:45 am, edited 3 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Given SELECT [return area] FROM [from area] WHERE [Condition Area]...
must be in the [from area]. You have it in the [return area]
try
Code: Select all
(SELECT estate_pictures.name
FROM estate_pictures WHERE estate_pictures.estate_id=estates.id
ORDER BY rand() LIMIT 1) AS pic_nametry
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- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.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"?
[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]
[/s]
Because the second query works exactly as I expected.
[s]Thanks for pointing me to this subselect restriction.[/s]
Last edited by VladSun on Wed Nov 12, 2008 6:06 pm, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: MySQL bug?
VladSun wrote: I've tried it in PGSQL and it gave an error message ...
PS: MySQL 5.0.16-max
There are 10 types of people in this world, those who understand binary and those who don't
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
..................
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?
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?
There are 10 types of people in this world, those who understand binary and those who don't
Hm ...
@superdezign: So ... it could be one row, column or a table ... it's up to the developer.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.
There are 10 types of people in this world, those who understand binary and those who don't
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.
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.
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.VladSun wrote:@superdezign: So ... it could be one row, solumn or a table ... it's up to the developer.
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.
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...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.
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.
There are 10 types of people in this world, those who understand binary and those who don't
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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?
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?
There are 10 types of people in this world, those who understand binary and those who don't