MySQL Bugs: #8020: subquery row comparisasion depends on argumnet order (select *)

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	CPU Architecture:	Any

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)

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 |
+-----------------------------------+

Verified on BK source.

ALL/ANY should support only support only scalar operation in 4.1, but yes, it is bug that it allow row comparison sometimes.

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

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.

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())

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

overlapped, with the fix, sorry

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)?

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.

Thank you for bugreport. Bugfix is pushed into 4.1.10 (5.0.3) source repository

Mentioned in 4.1.10 and 5.0.3 change notes.