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

Post by superdezign »

VladSun wrote: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?
Well, actually reading the query this time :P, I would think using GROUP BY in that manner would return only one result per estates.id from the JOINed estate_pictures table, but it would always choose the one with the lowest / earliest primary key. Using GROUP BY would basically give you what DISTINCT could give you with one table. I'm not too sure about the way DISTINCT works with multiple tables though, but in theory, GROUP BY should do exactly that.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
2. From http://dev.mysql.com/doc/refman/5.0/en/ ... rrors.html
Incorrect number of columns from subquery:

ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = "Operand should contain 1 column(s)"

This error occurs in cases like this:

SELECT (SELECT column1, column2 FROM t2) FROM t1;

You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”.
So ... subselect in return/select area is permitted in case it returns a scalar value! ;) :P

PS: Another one from MySQL official site:
#

Incorrectly used table in subquery:

Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"

This error occurs in cases such as the following:

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target.
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:1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
Except in MySQL? :P
VladSun wrote:2. From http://dev.mysql.com/doc/refman/5.0/en/ ... rrors.html
Incorrect number of columns from subquery:

ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = "Operand should contain 1 column(s)"

This error occurs in cases like this:

SELECT (SELECT column1, column2 FROM t2) FROM t1;

You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”.
So ... subselect in return/select area is permitted in case it returns a scalar value! ;) :P

PS: Another one from MySQL official site:
#

Incorrectly used table in subquery:

Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"

This error occurs in cases such as the following:

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target.
What was the error from the second query you posted? Was it from using the data from the outside of the subquery inside of it, possibly? As far as I know, subqueries are evaluated first.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

I have no errors :)
The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
... You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”...

... You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target. ...
Actually, the second query in my first post is working exactly as one could expect. No errors, no multiple rows, etc.
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 »

superdezign wrote:
VladSun wrote:1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
Except in MySQL? :P
Yeah ... this is a kind of "this works for me" ... You could never know whether it will work with other (higher) versions of MySQL ...
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:I have no errors :)
The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
... You may use a subquery that returns multiple columns, if the purpose is comparison. In other contexts, the subquery must be a scalar operand. See Section 12.2.8.5, “Row Subqueries”...

... You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target. ...
Yeah, when I said it I was assuming that CoderGoblin was correct. I actually use subqueries in SELECT statements in my own code. I've done a lot with subqueries just to see what they are capable of and they can do most anything, but a lot of the more 'advanced' things are hack-ish. So, I naturally assumed that this might have been one of those times. Hehe.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Thanks for participating in this argue - another important manual has been read :)
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 »

superdezign wrote:Using GROUP BY would basically give you what DISTINCT could give you with one table. I'm not too sure about the way DISTINCT works with multiple tables though, but in theory, GROUP BY should do exactly that.
I missed that one ;)

Using DISTINCT is pointless in this query - all of my estate_pictures records have distinct name field. So every row in this query will be unique. As a result I'll have multiple rows for each estates.id due to LEFT JOINing tables. I.e. with or without DISTINCT the result remains still the same.

PS: I wonder whether there is a way (a MySQL keyword/modifier) to have DISTINCT which is not per row but per coloumn. So I could be able to build such query:

Code: Select all

select distinct coloumn1, coloumn2, distinct coloumn3 from table
and the result should be: all records with unique coloumn1 AND unique coloumn3 without any care of the values of coloumn2...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

You might want to read up on how SQL standard JOINs work.

http://www.w3schools.com/sql/sql_join.asp

and group by

http://www.w3schools.com/sql/sql_groupby.asp

Your first example is a valid query and will usually return a value even if that value is 0 matching record sets.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

AKA Panama Jack wrote:You might want to read up on how SQL standard JOINs work.

http://www.w3schools.com/sql/sql_join.asp
What should I look for there?

PS:
GROUP BY... was added to SQL because aggregate functions
I have no aggregate functions in my first query .... that is the point ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Exactly what a LEFT JOIN will return when used with two tables. And since you are grouping by estate.id you are probably not going to get what you think you should be getting.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

AKA Panama Jack wrote:Exactly what a LEFT JOIN will return when used with two tables.
Can't understand it - why LEFT JOIN should behave like an aggregate function - I've searched really, really hard and couldn't find an example where there are only LEFT JOIN and GROUP BY without any aggregate function - no luck!
AKA Panama Jack wrote:And since you are grouping by estate.id you are probably not going to get what you think you should be getting.
Well, surprisingly to me, it does do what I want it to do.

PS: And for N-th time - PostgreSQL would never accept this query - try it, see it and try to explain it.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Because Postgres doesn't accept it doesn't mean it isn't a valid SQL query. :) Even Postgres isn't 100% SQL compatible.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

superdezign wrote:I'm not sure how Postgre handles all of the things that MySQL does, but I've grown used to the way MySQL works.
Postgres (up until the last major version of Mysql) had/has more inbuilt functionality than MySQL. So it should be phrased the other way around. Unfortunately my knowledge is with Postgres rather than MySQL so I know how that works. :roll:
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

VladSun wrote: The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
Well if that is the case I apologise. :oops: I answer from my experience and normally have no problems getting things working. Is there any advantage to putting a select in the Return area ? If not, for readability, I would maintain the SELECT structure initially talked about.
Post Reply