Bug #8020 subquery row comparisasion depends on argumnet order (select *)
Submitted: 19 Jan 2005 21:02 Modified: 5 Feb 2005 1:53
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:
Assigned to: Oleksandr Byelkin

[19 Jan 2005 21:02] Martin Friebe
Description:
the how to repeat block demonstrates, that mysql is able to compare row subqueries. This even applies if the subquery is a "select * from t".

select * does not work, if the "select *" is the first argument to the row comparisation.

It does again work, if the table has only one column (as soon below).

How to repeat:
create table t (a integer, b integer);

 select (select 1,2) = (select 1,2);
+-----------------------------+
| (select 1,2) = (select 1,2) |
+-----------------------------+
|                           1 |
+-----------------------------+

select (select 1,2) = (select * from t);
+----------------------------------+
| (select 1,2) = (select * from t) |
+----------------------------------+
|                             NULL |
+----------------------------------+

select (select a,b from t) = (select 1,2);
+------------------------------------+
| (select a,b from t) = (select 1,2) |
+------------------------------------+
|                               NULL |
+------------------------------------+

select (select * from t) = (select 1,2);
ERROR 1241 (21000): Operand should contain 1 column(s)

select row(1,2)=(select * from t) ;
+----------------------------+
| row(1,2)=(select * from t) |
+----------------------------+
|                       NULL |
+----------------------------+

select (select * from t) = row( 1,2);
ERROR 1241 (21000): Operand should contain 1 column(s)

### but a table with only one column
 create table t1 (a integer);

select (select * from t1) = 1;
+------------------------+
| (select * from t1) = 1 |
+------------------------+
|                   NULL |
+------------------------+

 select (select *,1 from t1) = row(1,1);
+---------------------------------+
| (select *,1 from t1) = row(1,1) |
+---------------------------------+
|                            NULL |
+---------------------------------+

# back to the first table

select (select *,3 from t) = row( 1,2,3);
ERROR 1241 (21000): Operand should contain 2 column(s)
select (select *,3,4,5 from t) = row( 1,2,3);
ERROR 1241 (21000): Operand should contain 4 column(s)

# mysql is clearly counting the * as one column (probably at parsing time), and later it does count it with the real amount of columns

Suggested fix:
defer the error untill the real amount of columns is known for sure. (seems to happen if * appears in the 2nd argument)
[19 Jan 2005 21:38] Martin Friebe
also happens below with ALL, but strangely not with ANY. (same thing, if data is in table)

CREATE TABLE t1 ( b1 int(11),  b2 int(11) ) ;

select  row(1,2) = ALL (select * from t1);
ERROR 1241 (21000): Operand should contain 1 column(s)

select  row(1,2) = ANY (select * from t1);
+-----------------------------------+
| row(1,2) = ANY (select * from t1) |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
[19 Jan 2005 21:54] Miguel Solorzano
Verified on BK source.
[24 Jan 2005 10:38] Oleksandr Byelkin
ALL/ANY should support only support only scalar operation in 4.1, but yes, it is bug that it allow row comparison sometimes.
[24 Jan 2005 11:07] Martin Friebe
the "row subquery" page at http://dev.mysql.com/doc/mysql/en/Row_subqueries.html

does give the example:
SELECT column1,column2,column3
       FROM t1
       WHERE (column1,column2,column3) IN
             (SELECT column1,column2,column3 FROM t2);

In as equvivalent for =ANY
[24 Jan 2005 12:10] Oleksandr Byelkin
It supported only for IN and ALL/ANY which can be converted to IN (= ANY or <> ALL):
+ select  row(1,2) = ANY (select * from t1);
+ row(1,2) = ANY (select * from t1)
+ 0
+ select  row(1,2) <> ALL (select * from t1);
+ row(1,2) != ALL (select * from t1)
+ 1

If you think that it should be described more clear in documentation we will.
[24 Jan 2005 12:27] Oleksandr Byelkin
ChangeSet
  1.2164 05/01/24 14:25:44 bell@sanja.is.com.ua +7 -0
  fixed column number fetchinmg for subqueries. (BUG#8020)
  fixed cols() method call (it have to be called only after fix_fields())
[24 Jan 2005 12:27] Martin Friebe
happens with in too
create table t2(a integer, b integer); insert into t2values (1,2),(3,4),(5,6);

select (select a,b from t2 limit 1) in (select * from t2);
# get a result

select (select * from t2 limit 1) in (select * from t2);
# get an error

the differnce to the documentation is that the 1st row (left operand) is derived by a select, instead of by a row().
However the example shows that this works, the bug is in the query validation, not in the execution (it seems)

Same about the other comparisation, tehy might be undocumented features, the work, as long as mysql, does not count "*" as ONE column sometimes.

Of course your comment is right, ifsome of these features are side-products, and therefore not (yet) properly tested, there availabilty is a bug too.

However the bug I am trying to point out is, how many colums an "*" weights for:
select (select *,1,1,1 from t2 limit 1) in (select a,b from t2);
OR
select (select *,1,1,1 from t2 limit 1) in (select * from t2);
# get an error 
"should contain 4 colums" obviously mysql counts the amount of columsn in the left operator as 4. but the "*" represents 2 columns. so it really is 5.

So the error msgs is definitley wrong. It should expect 5 colums, or if the feature is not yet wanted (even so it seems to be working otherwise), it should return a different error
[24 Jan 2005 12:30] Martin Friebe
overlapped, with the fix, sorry
[24 Jan 2005 15:00] Oleksandr Byelkin
Yes, there was bug in * processing and I committed patch for it (I hope it will be in source tree soon), I mean do we need more clear description that row operation supported only for IN and ALL/ANY which can be converted to IN (in manual)?
[24 Jan 2005 15:12] Martin Friebe
Ok, I understand the question.

The documentation is allready clear that row comparisation compares *one* row only (except for inside ANY,ALL)
Also multi-row selects, will give an appropriate error in comparisation.

The non "*" subqueries wich I compared as rows that I did (with one row select result on both sides) did work.

If this is however a site result, so If there are no test cases currently ensuring this, then it should at least be documented as experimental. 
Otherwise this is fine.
[1 Feb 2005 12:04] Oleksandr Byelkin
Thank you for bugreport. Bugfix is pushed into 4.1.10 (5.0.3) source repository
[5 Feb 2005 1:53] Paul Dubois
Mentioned in 4.1.10 and 5.0.3 change notes.