📋

Description

This error occurs when a SQL query or subquery is executed in a context that expects a single row (e.g., a scalar subquery, or an assignment to a single variable), but the query actually returns more than one row. This prevents the database from unambiguously assigning or using the result, leading to a failure.

💬

Error Message

Result consisted of more than one row

🔍

Known Causes

3 known causes

⚠️

Scalar Subquery Returned Multiple Rows

A subquery used in a `SELECT` list, `SET` statement, or a `WHERE` clause with an equality operator (`=`) returned more than one row, where only a single value was expected.

⚠️

Assignment from Multi-Row Result

Attempting to assign the output of a query that yields multiple rows to a single variable or parameter within a stored procedure or application code.

⚠️

Ambiguous Filtering Logic

The `WHERE` clause or `JOIN` conditions were not specific enough, causing a query to retrieve multiple rows when the surrounding context required a unique row.

🛠️

Solutions

4 solutions available

1. Limit Query Results to a Single Row easy

Add a LIMIT clause to ensure only one row is returned.

1

Identify the SQL query that is causing the error. This is usually a `SELECT` statement.

SELECT column1, column2 FROM your_table WHERE some_condition;

2

Modify the query to include a `LIMIT 1` clause. This instructs MariaDB to stop processing and return the first row it finds that matches the `WHERE` clause.

SELECT column1, column2 FROM your_table WHERE some_condition LIMIT 1;

3

If you need a specific row (e.g., based on an ID), ensure your `WHERE` clause is precise enough to uniquely identify that row. If not, `LIMIT 1` will return an arbitrary row, which might not be the intended one.

SELECT column1, column2 FROM your_table WHERE id = 123 LIMIT 1;

2. Use Aggregate Functions for Summarization medium

Employ aggregate functions like `MAX()`, `MIN()`, `AVG()`, or `COUNT()` to return a single aggregated value.

1

Analyze the data you are trying to retrieve. If the intention is to get a summary (e.g., the highest price, the total count) rather than individual records, aggregate functions are appropriate.

SELECT price FROM products WHERE category = 'electronics';

2

Rewrite the query using an aggregate function to produce a single result. For example, to get the highest price:

SELECT MAX(price) FROM products WHERE category = 'electronics';

3

If you need to group results and still want a single row per group, use `GROUP BY` along with aggregate functions. However, if the `GROUP BY` clause itself results in multiple groups, and your application expects only one row, you might still encounter issues if not handled correctly. For this error specifically, ensure your `GROUP BY` doesn't produce more than one group if a single row output is strictly required.

SELECT COUNT(*) FROM orders GROUP BY customer_id;

3. Refine WHERE Clause for Uniqueness medium

Ensure your `WHERE` clause uniquely identifies a single row.

1

Examine the `WHERE` clause of your query. Determine if it logically should return only one row based on the data model and the values provided.

SELECT * FROM users WHERE username = 'john.doe';

2

If the `WHERE` clause is intended to be a primary key lookup or a unique constraint, verify that the column(s) used in the `WHERE` clause indeed have unique values for the specified condition. You can check this with a separate query:

SELECT username, COUNT(*) FROM users GROUP BY username HAVING COUNT(*) > 1;

3

If duplicate values are found and the `WHERE` clause is meant to be unique, you will need to address the data integrity issue. This might involve removing duplicates, updating records, or changing the query's logic.

-- Example: If you find duplicate usernames, you might need to decide which one to keep or update the other.
-- DELETE FROM users WHERE user_id = (SELECT MIN(user_id) FROM users WHERE username = 'john.doe' AND user_id != <primary_key_of_intended_record>);

4

Alternatively, if the intent is to select a specific record among duplicates, you might need to add more conditions to the `WHERE` clause or use `ORDER BY` with `LIMIT 1`.

SELECT * FROM users WHERE username = 'john.doe' ORDER BY registration_date DESC LIMIT 1;

4. Modify Application Logic to Handle Multiple Rows advanced

Adjust the application code to process a result set with more than one row.

1

Understand why the query is returning multiple rows. Is it expected behavior, or an indication of a data issue or a misunderstanding of the query's purpose?

SELECT * FROM products WHERE name LIKE '%widget%';

2

If multiple rows are expected and the application is failing because it's designed to handle only one, refactor the application logic to iterate through the result set. This typically involves looping through the returned rows in your programming language (e.g., Python, PHP, Java).

# Example in Python using a database connector
cursor.execute("SELECT * FROM products WHERE name LIKE '%widget%';")
results = cursor.fetchall()

if results:
    for row in results:
        print(f"Product ID: {row['product_id']}, Name: {row['name']}")
else:
    print("No products found.")

3

If the application error is occurring in a context where a single value is expected (e.g., assigning a scalar value to a variable, using `executeUpdate` for a `SELECT` statement in some ORMs), consider if the query should indeed be returning a single value. If so, revisit solutions 1-3.

/* No direct SQL code, but this implies a programming context where a scalar is expected. */

🔗

Related Errors

5 related errors

MariaDB Error 1172: Query Returned Multiple Rows