MySQL bug?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

MySQL bug?

Post 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
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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

You cannot have a select in the return area. It must be in the FROM area.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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"?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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]
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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

VladSun wrote:OK, so there are two bugs in MySQL engine???
... What bug?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL bug?

Post by VladSun »

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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

But what bug? Where is a bug?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply