📋

Description

Error 1335 indicates that a subquery has been used in a context where MySQL does not support the subquery's returned value or its overall syntax. This typically happens within stored programs (procedures, functions, triggers), DDL statements, or other specific clauses that have limitations on subquery usage.

💬

Error Message

Subquery value not supported

🔍

Known Causes

4 known causes

⚠️

Subquery in Stored Program DDL

Using a subquery within a `CREATE TRIGGER`, `CREATE VIEW`, `CREATE PROCEDURE`, or `CREATE FUNCTION` statement where subqueries are not permitted by MySQL.

⚠️

Subquery in CHECK Constraint

Attempting to use a subquery within a `CHECK` constraint definition, which MySQL does not support for data validation rules.

⚠️

Restricted ALTER TABLE Context

Employing a subquery in an `ALTER TABLE` statement, such as in certain `DEFAULT` value expressions or other clauses where subquery usage is not allowed.

⚠️

Subquery in CREATE EVENT

Using a subquery within the `ON SCHEDULE` or `DO` clause of a `CREATE EVENT` statement, which can have specific restrictions.

🛠️

Solutions

3 solutions available

1. Simplify the Subquery Expression easy

Rewrite the subquery to return a single scalar value or a list of values compatible with the outer query's comparison operator.

1

Identify the problematic subquery. This error typically occurs when a subquery is expected to return a single value (scalar) but returns multiple rows or columns, or when the data type of the subquery's result doesn't match the outer query's context.

2

If the subquery is intended to return a single value, ensure it has a `LIMIT 1` clause or a `WHERE` clause that guarantees uniqueness. If it's meant to return a list for an `IN` or `EXISTS` clause, verify the data types match.

Example of a subquery that might cause this if it returns multiple rows:
SELECT column_name FROM another_table WHERE some_condition;

Corrected for scalar context (if intended):
SELECT column_name FROM another_table WHERE some_condition LIMIT 1;

Corrected for list context (if intended for IN):
SELECT column_name FROM another_table WHERE some_condition;
-- Ensure the outer query uses IN or EXISTS appropriately.

3

Re-evaluate the logic of your query. Sometimes, a subquery is used incorrectly. Consider if a `JOIN` operation would be more appropriate and efficient than a subquery.

Example of a potential JOIN replacement:
-- Original (problematic subquery):
SELECT * FROM table1 WHERE column_a = (SELECT column_b FROM table2 WHERE table2.id = table1.fk_id);

-- Using JOIN:
SELECT t1.* FROM table1 t1 JOIN table2 t2 ON t1.fk_id = t2.id WHERE t1.column_a = t2.column_b;

2. Ensure Subquery Returns a Single Column easy

Verify that the subquery's `SELECT` list contains only one column when used in a context expecting a scalar value.

1

Examine the `SELECT` statement within your subquery. The error 1335 specifically points to a subquery returning more than one column when a single value is expected.

2

Modify the subquery's `SELECT` list to include only the single column that is relevant to the comparison in the outer query.

Example of a subquery with multiple columns:
SELECT column1, column2 FROM some_table WHERE condition;

Corrected to return a single column:
SELECT column1 FROM some_table WHERE condition;

3

If you need to compare against multiple values from the subquery, ensure the outer query uses operators like `IN`, `ANY`, or `EXISTS` and that the subquery returns a single column. If you need to compare against multiple columns, a `JOIN` is usually a better approach.

Example using IN with a single-column subquery:
SELECT * FROM main_table WHERE id IN (SELECT user_id FROM active_users WHERE status = 'active');

3. Check Data Types and NULL Values medium

Ensure data types are compatible between the subquery and outer query, and handle potential NULLs that might lead to unexpected subquery results.

1

Compare the data types of the column(s) selected in the subquery with the column(s) they are being compared against in the outer query. Mismatched types can lead to implicit conversions that might not behave as expected, especially with NULLs.

Use DESCRIBE or SHOW COLUMNS to check table schemas:
DESCRIBE table1;
DESCRIBE table2;

2

If the subquery can return NULL values, and the outer query is expecting a non-NULL value (e.g., in a direct equality comparison `=`), this can lead to unexpected behavior. Consider using `COALESCE` or `IFNULL` in the subquery if NULLs need to be treated as a specific value.

Example where NULLs might cause issues:
-- Outer query expects a specific value, subquery might return NULL
SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE last_login IS NULL);

-- Handling NULLs in subquery if appropriate:
SELECT * FROM orders WHERE customer_id = (SELECT COALESCE(id, -1) FROM customers WHERE last_login IS NULL); -- Assuming -1 is a safe default

3

If the subquery is intended to return a single value and might return multiple identical values, ensure the `DISTINCT` keyword is used if necessary to avoid ambiguity, or use `LIMIT 1` if any one of the matching values is acceptable.

Using DISTINCT:
SELECT * FROM products WHERE category_id = (SELECT DISTINCT category_id FROM archived_products WHERE last_updated < '2023-01-01');

Using LIMIT 1:
SELECT * FROM products WHERE category_id = (SELECT category_id FROM archived_products WHERE last_updated < '2023-01-01' LIMIT 1);

🔗

Related Errors

5 related errors

MySQL Error 1335: Unsupported Subquery Value