(mysql.info.gz) Subquery errors

Info Catalog

(mysql.info.gz) Unnamed views

(mysql.info.gz) Subqueries

(mysql.info.gz) Optimizing subqueries

 
 13.1.8.9 Subquery Errors
 ........................
 
 There are some new error returns that apply only to subqueries.  This
 section groups them together because reviewing them will help remind
 you of some points.
 
    * Unsupported subquery syntax:
 
           ERROR 1235 (ER_NOT_SUPPORTED_YET)
           SQLSTATE = 42000
           Message = "This version of MySQL doesn't yet support
           'LIMIT & IN/ALL/ANY/SOME subquery'"
 
      This means that statements of the following form will not work,
      although this happens only in some early versions, such as MySQL
      4.1.1:
 
           SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)
 
    * Incorrect number of columns from subquery:
 
           ERROR 1241 (ER_OPERAND_COL)
           SQLSTATE = 21000
           Message = "Operand should contain 1 column(s)"
 
      This error will occur in cases like this:
 
           SELECT (SELECT column1, column2 FROM t2) FROM t1;
 
      It's okay to use a subquery that returns multiple columns, if the
      purpose is comparison.  Row subqueries.  But in other
      contexts, the subquery must be a scalar operand.
 
    * Incorrect number of rows from subquery:
 
           ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
           SQLSTATE = 21000
           Message = "Subquery returns more than 1 row"
 
      This error will occur for statements such as the following one,
      but only when there is more than one row in `t2':
 
           SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
 
      That means this error might occur in code that had been working
      for years, because somebody happened to make a change that
      affected the number of rows that the subquery can return. Remember
      that if the object is to find any number of rows, not just one,
      then the correct statement would look like this:
 
           SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
 
    * 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 will occur in cases like this:
 
           UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
 
      It's okay to use a subquery for assignment within an `UPDATE'
      statement, since 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.
 
 
 For transactional storage engines, a failure of a subquery causes the
 entire statement to fail. For non-transactional storage engines, data
 modifications made before the error was encountered are preserved.
 

Info Catalog

(mysql.info.gz) Unnamed views

(mysql.info.gz) Subqueries

(mysql.info.gz) Optimizing subqueries