[MDEV-35592] ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0' on CONCAT in sub-query - Jira

Details

Type: Bug
Status: In Progress (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 11.5.2, 11.6.2
Fix Version/s: 11.8
Component/s: None
Labels:
- regression
Environment:
Official docker image mariadb:11.6.2
Also tested with mariadb 11.5.2

Description

For some SELECT queries with a CONCAT in a sub-query on a table with a lot of data, I get the "ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0'" error.

I was able to get a minimal set of anonymised data to reproduce the issue.

Step to reproduce :

Execute `01_schema.sql`
Execute `02_data_partner.sql`
Execute `03_data_movement.sql`

Run the following query:

SELECT (SELECT CONCAT(partner.companyName, partner.lastName)

        FROM test_partner partner

        WHERE mvt.delivery_fk = partner.id) AS val

FROM test_movement mvt

WHERE mvt.documentId > 'a';

You will get the error.

The `WHERE mvt.documentId > 'a';` filter is necessary to trigger the bug.

Using docker:

# Setup a clean mariadb

docker run --rm --name mariadb_test -d --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11.6

# Create a test db

docker exec mariadb_test mariadb -e 'CREATE DATABASE test'

# Import schema

docker exec -i mariadb_test mariadb test < 01_schema.sql

# Import data

docker exec -i mariadb_test mariadb test < 02_data_partner.sql

docker exec -i mariadb_test mariadb test < 03_data_movement.sql

# Execute the query to trigger the bug

docker exec -i mariadb_test mariadb test < 04_query.sql

# Cleanup

docker stop mariadb_test

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

final.7z
7.45 MB
2024-12-06 14:11
MDEV-35592.7z
6.65 MB
2024-12-10 18:05

Issue Links

is caused by

MDEV-9101 Limit size of created disk temporary files and tables

Closed

Activity

Ascending order - Click to sort in descending order

Colin GILLE created issue - 2024-12-06 14:06

Colin GILLE made changes - 2024-12-06 14:08

Field	Original Value	New Value
Description	For some SELECT queries with a CONCAT in a sub-query on a table with a lot of data, I get the "ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0'" error. This seems to be linked to the usage of filesort. I was able to get a minimal set of anonymised data to reproduce the issue. Step to reproduce : * Execute `01_schema.sql` * Execute `02_data_partner.sql` * Execute `03_data_movement.sql` Run the following query: {code:sql} SELECT (SELECT CONCAT(partner.companyName, partner.lastName) FROM test_partner partner WHERE delivery_fk = mvt.id) AS val FROM test_movement mvt WHERE mvt.documentId > 'a'; {code} You will get the error. The `WHERE mvt.documentId > 'a';` filter is necessary to trigger the bug. Using docker: {code:bash} # Setup a clean mariadb docker run --rm --name mariadb_test -d --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11 # Create a test db docker exec mariadb_test mariadb -e 'CREATE DATABASE test' # Import schema docker exec -i mariadb_test mariadb test < 01_schema.sql # Import data docker exec -i mariadb_test mariadb test < 02_data_partner.sql docker exec -i mariadb_test mariadb test < 03_data_movement.sql # Execute the query to trigger the bug docker exec -i mariadb_test mariadb test < 04_query.sql # Cleanup docker stop mariadb_test {code}	For some SELECT queries with a CONCAT in a sub-query on a table with a lot of data, I get the "ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0'" error. I was able to get a minimal set of anonymised data to reproduce the issue. Step to reproduce : * Execute `01_schema.sql` * Execute `02_data_partner.sql` * Execute `03_data_movement.sql` Run the following query: {code:sql} SELECT (SELECT CONCAT(partner.companyName, partner.lastName) FROM test_partner partner WHERE delivery_fk = mvt.id) AS val FROM test_movement mvt WHERE mvt.documentId > 'a'; {code} You will get the error. The `WHERE mvt.documentId > 'a';` filter is necessary to trigger the bug. Using docker: {code:bash} # Setup a clean mariadb docker run --rm --name mariadb_test -d --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11 # Create a test db docker exec mariadb_test mariadb -e 'CREATE DATABASE test' # Import schema docker exec -i mariadb_test mariadb test < 01_schema.sql # Import data docker exec -i mariadb_test mariadb test < 02_data_partner.sql docker exec -i mariadb_test mariadb test < 03_data_movement.sql # Execute the query to trigger the bug docker exec -i mariadb_test mariadb test < 04_query.sql # Cleanup docker stop mariadb_test {code}

Colin GILLE made changes - 2024-12-06 14:10

Description

For some SELECT queries with a CONCAT in a sub-query on a table with a lot of data, I get the "ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0'" error.

I was able to get a minimal set of anonymised data to reproduce the issue.

Step to reproduce :
* Execute `01_schema.sql`
* Execute `02_data_partner.sql`
* Execute `03_data_movement.sql`

Run the following query:
{code:sql}
SELECT (SELECT CONCAT(partner.companyName, partner.lastName)
FROM test_partner partner
WHERE delivery_fk = mvt.id) AS val
FROM test_movement mvt
WHERE mvt.documentId > 'a';
{code}

You will get the error.

The `WHERE mvt.documentId > 'a';` filter is necessary to trigger the bug.

Using docker:

{code:bash}
# Setup a clean mariadb
docker run --rm --name mariadb_test -d --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11

# Create a test db
docker exec mariadb_test mariadb -e 'CREATE DATABASE test'

# Import schema
docker exec -i mariadb_test mariadb test < 01_schema.sql

# Import data
docker exec -i mariadb_test mariadb test < 02_data_partner.sql
docker exec -i mariadb_test mariadb test < 03_data_movement.sql

# Execute the query to trigger the bug
docker exec -i mariadb_test mariadb test < 04_query.sql

# Cleanup
docker stop mariadb_test
{code}

For some SELECT queries with a CONCAT in a sub-query on a table with a lot of data, I get the "ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0'" error.

I was able to get a minimal set of anonymised data to reproduce the issue.

Step to reproduce :
* Execute `01_schema.sql`
* Execute `02_data_partner.sql`
* Execute `03_data_movement.sql`

Run the following query:
{code:sql}
SELECT (SELECT CONCAT(partner.companyName, partner.lastName)
FROM test_partner partner
WHERE mvt.delivery_fk = partner.id) AS val
FROM test_movement mvt
WHERE mvt.documentId > 'a';
{code}

You will get the error.

The `WHERE mvt.documentId > 'a';` filter is necessary to trigger the bug.

Using docker:

{code:bash}
# Setup a clean mariadb
docker run --rm --name mariadb_test -d --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11

# Create a test db
docker exec mariadb_test mariadb -e 'CREATE DATABASE test'

# Import schema
docker exec -i mariadb_test mariadb test < 01_schema.sql

# Import data
docker exec -i mariadb_test mariadb test < 02_data_partner.sql
docker exec -i mariadb_test mariadb test < 03_data_movement.sql

# Execute the query to trigger the bug
docker exec -i mariadb_test mariadb test < 04_query.sql

# Cleanup
docker stop mariadb_test
{code}

Colin GILLE made changes - 2024-12-06 14:11

Attachment

data.7z [ 74321 ]

Colin GILLE made changes - 2024-12-06 14:11

Attachment

final.7z [ 74322 ]

Colin GILLE made changes - 2024-12-06 14:15

Description

For some SELECT queries with a CONCAT in a sub-query on a table with a lot of data, I get the "ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0'" error.

I was able to get a minimal set of anonymised data to reproduce the issue.

Step to reproduce :
* Execute `01_schema.sql`
* Execute `02_data_partner.sql`
* Execute `03_data_movement.sql`

Run the following query:
{code:sql}
SELECT (SELECT CONCAT(partner.companyName, partner.lastName)
FROM test_partner partner
WHERE mvt.delivery_fk = partner.id) AS val
FROM test_movement mvt
WHERE mvt.documentId > 'a';
{code}

You will get the error.

The `WHERE mvt.documentId > 'a';` filter is necessary to trigger the bug.

Using docker:

{code:bash}
# Setup a clean mariadb
docker run --rm --name mariadb_test -d --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11

# Create a test db
docker exec mariadb_test mariadb -e 'CREATE DATABASE test'

# Import schema
docker exec -i mariadb_test mariadb test < 01_schema.sql

# Import data
docker exec -i mariadb_test mariadb test < 02_data_partner.sql
docker exec -i mariadb_test mariadb test < 03_data_movement.sql

# Execute the query to trigger the bug
docker exec -i mariadb_test mariadb test < 04_query.sql

# Cleanup
docker stop mariadb_test
{code}

For some SELECT queries with a CONCAT in a sub-query on a table with a lot of data, I get the "ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0'" error.

I was able to get a minimal set of anonymised data to reproduce the issue.

Step to reproduce :
* Execute `01_schema.sql`
* Execute `02_data_partner.sql`
* Execute `03_data_movement.sql`

Run the following query:
{code:sql}
SELECT (SELECT CONCAT(partner.companyName, partner.lastName)
FROM test_partner partner
WHERE mvt.delivery_fk = partner.id) AS val
FROM test_movement mvt
WHERE mvt.documentId > 'a';
{code}

You will get the error.

The `WHERE mvt.documentId > 'a';` filter is necessary to trigger the bug.

Using docker:

{code:bash}
# Setup a clean mariadb
docker run --rm --name mariadb_test -d --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11.6

# Create a test db
docker exec mariadb_test mariadb -e 'CREATE DATABASE test'

# Import schema
docker exec -i mariadb_test mariadb test < 01_schema.sql

# Import data
docker exec -i mariadb_test mariadb test < 02_data_partner.sql
docker exec -i mariadb_test mariadb test < 03_data_movement.sql

# Execute the query to trigger the bug
docker exec -i mariadb_test mariadb test < 04_query.sql

# Cleanup
docker stop mariadb_test
{code}

Colin GILLE made changes - 2024-12-07 12:27

Affects Version/s		11.6.2 [ 29908 ]
Affects Version/s		11.5.2 [ 29838 ]

Alice Sherepa made changes - 2024-12-10 16:15

Labels

regression

Alice Sherepa made changes - 2024-12-10 16:29

Fix Version/s

11.7 [ 29815 ]

Alice Sherepa made changes - 2024-12-10 18:00

Link

This issue is caused by ~~MDEV-9101~~ [ ~~MDEV-9101~~ ]

Alice Sherepa added a comment - 2024-12-10 18:05

Thanks! I repeated as described on 11.5+.
It is caused by b9f5793176 or 865ef0f567 (with b9f5793176 I've got compile errors, so not able to check exactly)
I add MDEV-35592.test - nearly the same test, but in 1 file and for running with mtr

Alice Sherepa added a comment - 2024-12-10 18:05 Thanks! I repeated as described on 11.5+. It is caused by b9f5793176 or 865ef0f567 (with b9f5793176 I've got compile errors, so not able to check exactly) I add MDEV-35592 .test - nearly the same test, but in 1 file and for running with mtr

Alice Sherepa made changes - 2024-12-10 18:05

Attachment

MDEV-35592.7z [ 74330 ]

Alice Sherepa made changes - 2024-12-10 18:06

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2024-12-10 18:06

Assignee

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-12-11 13:03

Assignee

Sergei Golubchik [ serg ]

Michael Widenius [ monty ]

Sergei Golubchik made changes - 2024-12-11 13:07

Priority

Major [ 3 ]

Critical [ 2 ]

Colin GILLE added a comment - 2025-02-26 15:24

Hi, is there any news on this issue or a workaround?

I tried to set the `max_tmp_session_space_usage` and `max_tmp_total_space_usage` variables to 0 to disable the feature causing this, but it doesn't seem to help

Colin GILLE added a comment - 2025-02-26 15:24 Hi, is there any news on this issue or a workaround? I tried to set the `max_tmp_session_space_usage` and `max_tmp_total_space_usage` variables to 0 to disable the feature causing this, but it doesn't seem to help

Sergei Golubchik made changes - 2025-02-26 16:52

Fix Version/s		11.8 [ 29921 ]
Fix Version/s	11.7(EOL) [ 29815 ]

Michael Widenius added a comment - 2025-02-26 17:02

I will take a look at this tomorrow.

Michael Widenius added a comment - 2025-02-26 17:02 I will take a look at this tomorrow.

Michael Widenius made changes - 2025-02-27 09:07

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Michael Widenius added a comment - 2025-02-27 15:18

First some background for this issue (from the MTR test case):

CREATE TABLE `t1` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
;

CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`documentId` varchar(24) DEFAULT NULL,
`delivery_fk` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `documentId_index` (`documentId`),
KEY `delivery_index` (`delivery_fk`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci

The exlplain for the query is:

explain SELECT (SELECT 1 FROM t1 WHERE t2.delivery_fk = t1.id) AS val FROM t2
WHERE t2.documentId > 'a';

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

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

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

1	PRIMARY	t2	ALL	documentId_index	NULL	NULL	NULL	743769	Using where
2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.delivery_fk	1	Using index

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

json format is in this case more informative:
explain format=json
{
"query_block": {
"select_id": 1,
"cost": 118.0823664,
"nested_loop": [
{
"table":

{ "table_name": "t2", "access_type": "ALL", "possible_keys": ["documentId_index"], "loops": 1, "rows": 743769, "cost": 118.0823664, "filtered": 100, "attached_condition": "t2.documentId > 'a'" }

}
],
"subqueries": [
{
"expression_cache": {
"state": "uninitialized",
"query_block": {
"select_id": 2,
"cost": 0.000838227,
"outer_ref_condition": "t2.delivery_fk is not null",
"nested_loop": [
{
"table":

{ "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["test.t2.delivery_fk"], "loops": 1, "rows": 1, "cost": 0.000838227, "filtered": 100, "using_index": true }

}
]
}
}
}
]
}
}

The issue is that the subquery_cache (expression cache) has a bug when
one part of the cached keys contains a null and the used table is
null-rejecting (there will be no matching rows if the compared key
is null).

In the test case, there is a lot if rows in t2.delivery_fk that contains
NULL, which causes the problem to appear.

I have created a patch for this. Now we only have to create a smaller
test case for this.

Until next release, this issue can be avoid by turning the subquery
cache off:

SET optimizer_switch='subquery_cache=off';

Michael Widenius added a comment - 2025-02-27 15:18 First some background for this issue (from the MTR test case): CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ; CREATE TABLE `t2` ( `id` int(11) NOT NULL, `documentId` varchar(24) DEFAULT NULL, `delivery_fk` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `documentId_index` (`documentId`), KEY `delivery_index` (`delivery_fk`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci The exlplain for the query is: explain SELECT (SELECT 1 FROM t1 WHERE t2.delivery_fk = t1.id) AS val FROM t2 WHERE t2.documentId > 'a'; ----- ------------------ ----- ------ ---------------- ------- ------- ------------------- ------ ------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ------------------ ----- ------ ---------------- ------- ------- ------------------- ------ ------------+ 1 PRIMARY t2 ALL documentId_index NULL NULL NULL 743769 Using where 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.t2.delivery_fk 1 Using index ----- ------------------ ----- ------ + ------- json format is in this case more informative: explain format=json { "query_block": { "select_id": 1, "cost": 118.0823664, "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "possible_keys": ["documentId_index"], "loops": 1, "rows": 743769, "cost": 118.0823664, "filtered": 100, "attached_condition": "t2.documentId > 'a'" } } ], "subqueries": [ { "expression_cache": { "state": "uninitialized", "query_block": { "select_id": 2, "cost": 0.000838227, "outer_ref_condition": "t2.delivery_fk is not null", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["test.t2.delivery_fk"], "loops": 1, "rows": 1, "cost": 0.000838227, "filtered": 100, "using_index": true } } ] } } } ] } } The issue is that the subquery_cache (expression cache) has a bug when one part of the cached keys contains a null and the used table is null-rejecting (there will be no matching rows if the compared key is null). In the test case, there is a lot if rows in t2.delivery_fk that contains NULL, which causes the problem to appear. I have created a patch for this. Now we only have to create a smaller test case for this. Until next release, this issue can be avoid by turning the subquery cache off: SET optimizer_switch='subquery_cache=off';

Michael Widenius made changes - 2025-02-27 15:32

Assignee

Michael Widenius [ monty ]

Oleksandr Byelkin [ sanja ]

Colin GILLE added a comment - 2025-02-28 09:03

I can confirm the workaround is working fine, thanks!

Colin GILLE added a comment - 2025-02-28 09:03 I can confirm the workaround is working fine, thanks!

People

Assignee:: Oleksandr Byelkin

Reporter:: Colin GILLE

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2024-12-06 14:06

Updated:: 2025-02-28 09:18

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration