MySQL Forums
Forum List  »  Docs

Subquery documentation errors
Posted by: bruno rossi
Date: December 13, 2007 10:07AM

Manual:
Row comparison operations are only partially supported:

· For expr IN (subquery), expr can be an n-tuple (specified via row constructor syntax) and the subquery can return rows of n-tuples.

· For expr op {ALL|ANY|SOME} (subquery), expr must be a scalar value and the subquery must be a column subquery; it cannot return multiple-column rows.

In other words, for a subquery that returns rows of n-tuples, this is supported:

(val_1, ..., val_n) IN (subquery)
But this is not supported:

(val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)
The reason for supporting row comparisons for IN but not for the others is that IN is implemented by rewriting it as a sequence of = comparisons and AND operations. This approach cannot be used for ALL, ANY, or SOME.

There’s a lot of confusion and errors:

I can use IN, = ANY, = SOME, NOT IN, <> ALL with a row constructor of n-columns and n-rows

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test

Database changed

mysql> CREATE TABLE t1 (col1 CHAR(10), col2 CHAR(10), col3 INT);

Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2 (col1 CHAR(10), col2 CHAR(10), col3 INT);

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t3 VALUES ('a', 'b', 100), ('v', 'a', 100);

ERROR 1146 (42S02): Table 'test.t3' doesn't exist

mysql> INSERT INTO t2 VALUES ('a', 'b', 100), ('v', 'a', 100);

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) IN (SELECT col1, col2, col3 FROM t2);

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| a | b | 100 |

+------+------+------+

1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) = ANY (SELECT col1, col2, col3 FROM t2);

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| a | b | 100 |

+------+------+------+

1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM t2);

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| d | e | 100 |

+------+------+------+

1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) <> ALL (SELECT col1, col2, col3 FROM t2);

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| d | e | 100 |

+------+------+------+

1 row in set (0.00 sec)



I can use =, >, <, <> with a row constructor of n-columns and 1 row

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) > (SELECT col1, col2, col3 FROM t2);

ERROR 1242 (21000): Subquery returns more than 1 row

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) < (SELECT col1, col2, col3 FROM t2);

ERROR 1242 (21000): Subquery returns more than 1 row

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) = (SELECT col1, col2, col3 FROM t2);

ERROR 1242 (21000): Subquery returns more than 1 row

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) <> (SELECT col1, col2, col3 FROM t2);

ERROR 1242 (21000): Subquery returns more than 1 row

mysql> DELETE FROM t2 LIMIT 1;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) > (SELECT col1, col2, col3 FROM t2);

Empty set (0.00 sec)

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) < (SELECT col1, col2, col3 FROM t2);

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| a | b | 100 |

| d | e | 100 |

+------+------+------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) = (SELECT col1, col2, col3 FROM t2);

Empty set (0.01 sec)

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) <> (SELECT col1, col2, col3 FROM t2);

+------+------+------+

| col1 | col2 | col3 |

+------+------+------+

| a | b | 100 |

| d | e | 100 |

+------+------+------+

2 rows in set (0.00 sec)


I cannot use a row constructor with < ANY, > ANY, <> ANY, < SOME, > SOME, <> SOME, = ALL, > ALL, < ALL

mysql> SELECT * FROM t1 WHERE (col1, col2, col3) < ANY (SELECT col1, col2, col3 FROM t2);

ERROR 1241 (21000): Operand should contain 1 column(s)

Options: ReplyQuote


Subject
Views
Written By
Posted
Subquery documentation errors
6767
December 13, 2007 10:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.