プログラマー向け: MariaDBとMySQLにおけるサブクエリと親テーブル参照の比較
MariaDB: Can't reference parent table in dependent subquery -- same query works in MySQL
問題
Can't reference parent table 'parent_table' in dependent subquery
一方、MySQLでは同じクエリが問題なく実行できます。
原因
この問題は、MariaDBのデフォルト設定である sql_mode=STRICT_ALL_TABLES に起因します。この設定では、サブクエリ内で親テーブルを参照する場合、サブクエリ内で親テーブルのすべての列を参照する必要があります。
一方、MySQLではデフォルト設定で sql_mode が設定されていないため、この問題は発生しません。
解決策
この問題を解決するには、以下のいずれかの方法を実行します。
sql_mode 設定を変更する
MariaDBの sql_mode 設定を変更して、STRICT_ALL_TABLES オプションを無効化できます。
SET sql_mode = '...' ^ 'STRICT_ALL_TABLES';
サブクエリ内で親テーブルのすべての列を参照するようにクエリを変更します。
FROM 節で親テーブルと子テーブルを結合し、サブクエリを使用しないクエリに変更します。
例
以下のクエリは、親テーブル parent_table の id 列と子テーブル child_table の parent_id 列を結合して、child_table のすべてのレコードを取得します。
SELECT *
FROM child_table c
JOIN parent_table p ON c.parent_id = p.id;
注意事項
sql_mode設定を変更する場合は、他の設定への影響を考慮する必要があります。- サブクエリで親テーブルのすべての列を参照する場合は、不要な列も含めることになります。
-- Original query that fails in MariaDB due to STRICT_ALL_TABLES
SELECT *
FROM child_table c
WHERE parent_id IN (
SELECT id
FROM parent_table
WHERE name = 'John Doe'
);
This query will fail in MariaDB because the subquery is trying to reference the name column from the parent_table, but the STRICT_ALL_TABLES setting requires that all columns from the parent table be referenced in the subquery.
-- Solution 1: Change sql_mode setting
SET sql_mode = '...' ^ 'STRICT_ALL_TABLES';
-- Execute the original query
SELECT *
FROM child_table c
WHERE parent_id IN (
SELECT id
FROM parent_table
WHERE name = 'John Doe'
);
-- Reset sql_mode setting
SET sql_mode = '...';
This solution changes the sql_mode setting to disable the STRICT_ALL_TABLES option, allowing the query to execute successfully. However, it is important to note that this will also disable other strict mode checks, which may have unintended consequences.
-- Solution 2: Reference all columns from parent_table in subquery
SELECT *
FROM child_table c
WHERE parent_id IN (
SELECT *
FROM parent_table
WHERE name = 'John Doe'
);
This solution modifies the subquery to reference all columns from the parent_table, even though only the id column is actually needed. This will satisfy the STRICT_ALL_TABLES requirement and allow the query to execute successfully. However, it is inefficient and may cause performance issues if the parent_table is large.
-- Solution 3: Join parent_table and child_table in FROM clause
SELECT *
FROM child_table c
JOIN parent_table p ON c.parent_id = p.id
WHERE p.name = 'John Doe';
This solution eliminates the need for a subquery by joining the parent_table and child_table directly in the FROM clause. This is the most efficient and recommended solution, as it avoids the overhead of executing a subquery and does not require any changes to the sql_mode setting.
I hope this helps!
A correlated subquery is a subquery that references a column from the outer query. This can be used to avoid the need to reference all columns from the parent table in the subquery.
SELECT *
FROM child_table c
WHERE parent_id IN (
SELECT id
FROM parent_table p
WHERE name = 'John Doe' AND p.id = c.parent_id
);
In this example, the subquery is correlated to the outer query by referencing the parent_id column from the child_table. This allows the query to execute successfully without violating the STRICT_ALL_TABLES requirement.
Use a window function
Window functions can be used to perform calculations over a set of rows within a query. This can be used to avoid the need for a subquery in some cases.
SELECT *
FROM child_table c
WHERE parent_id IN (
SELECT id
FROM parent_table
WHERE name = 'John Doe'
ORDER BY id
WINDOW FRAME CURRENT ROW
);
In this example, the window function ROW_NUMBER() is used to assign a row number to each row in the parent_table. The subquery then filters the rows based on the id of the row with the name John Doe.
A common table expression (CTE) is a temporary named result set that can be used within a query. This can be used to break down a complex query into smaller, more manageable parts.
WITH parent_data AS (
SELECT id
FROM parent_table
WHERE name = 'John Doe'
)
SELECT *
FROM child_table c
WHERE parent_id IN (
SELECT id
FROM parent_data
);
In this example, the CTE parent_data is used to store the filtered rows from the parent_table. The main query then joins the child_table to the parent_data CTE on the parent_id column.
Upgrade to MySQL
If you are able to, you can upgrade to MySQL, which does not have the STRICT_ALL_TABLES setting by default. This will eliminate the need for any workarounds.
Considerations
The best method for fixing the error will depend on the specific situation. Correlated subqueries can be efficient for simple queries, but they can become complex and difficult to maintain for more complex queries. Window functions can be a powerful tool, but they can also be more difficult to understand and use. CTEs can be a good way to break down complex queries, but they can also add overhead to the query execution. Upgrading to MySQL is the simplest solution, but it may not be possible in all cases.
mariadb
