Well, actually reading the query this timeVladSun 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?
MySQL bug?
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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

PS: Another one from MySQL official site:
2. From http://dev.mysql.com/doc/refman/5.0/en/ ... rrors.html
So ... subselect in return/select area is permitted in case it returns a scalar value!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”.
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
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Except in MySQL?VladSun wrote:1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
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.VladSun wrote:2. From http://dev.mysql.com/doc/refman/5.0/en/ ... rrors.htmlSo ... subselect in return/select area is permitted in case it returns a scalar value!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”.
![]()
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.
I have no errors 
The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions:
Actually, the second query in my first post is working exactly as one could expect. No errors, no multiple rows, etc.... 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. ...
There are 10 types of people in this world, those who understand binary and those who don't
Yeah ... this is a kind of "this works for me" ... You could never know whether it will work with other (higher) versions of MySQL ...superdezign wrote:Except in MySQL?VladSun wrote:1. As far as I know GROUP BY is to be used *only* with aggregate functions ...
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
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.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. ...
I missed that onesuperdezign 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.
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
There are 10 types of people in this world, those who understand binary and those who don't
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
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.
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.
What should I look for there?AKA Panama Jack wrote:You might want to read up on how SQL standard JOINs work.
http://www.w3schools.com/sql/sql_join.asp
PS:
I have no aggregate functions in my first query .... that is the point ...GROUP BY... was added to SQL because aggregate functions
There are 10 types of people in this world, those who understand binary and those who don't
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
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:Exactly what a LEFT JOIN will return when used with two tables.
Well, surprisingly to me, it does do what I want it to do.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.
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
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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.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.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Well if that is the case I apologise.VladSun wrote: The quotations above show that you and CoderGoblin have misleaded me with these "no subselect in return area" restrictions: