pingcap / pingcap/tidb

Planner: Plan Cache incorrectly blocks nullable-but-logically-non-null Join Keys

Open
#60,421 1 comment 0 reactions 2 assignees Claimed by @time-and-fate View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

Currently, the Plan Cache does not support this scenario involving `test.ratings.user_id` because the field is not explicitly marked as NOT NULL, which could lead to correctness risks. However, since this field is a Join Key (and logically cannot be null), the Plan Cache should support this scenario.

Key Points:

- Root Cause: Missing NOT NULL constraint on test.ratings.user_id triggers correctness safeguards.

- Why It Should Work: Join Keys are inherently non-nullable, so the restriction is unnecessary.

- Expected Behavior: Plan Cache should permit such queries.

```
CREATE TABLE `users` (
`id` bigint,
`nickname` varchar(100),
`balance` decimal(15,2)
);

CREATE TABLE `books` (
`id` bigint NOT NULL,
`title` varchar(100),
`type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports'),
`published_at` datetime,
`stock` int,
`price` decimal(15,2)
);

CREATE TABLE `ratings` (
`book_id` bigint,
`user_id` bigint,
`score` tinyint,
`rated_at` datetime,
PRIMARY KEY (`book_id`,`user_id`) CLUSTERED
);

PREPARE `books_query` FROM '
select t1.nickname, t1.balance
from users t1 LEFT JOIN ratings t2 on t1.id = t2.user_id
where t2.user_id=?';

SET @user_id = 1;
EXECUTE `books_query` USING @user_id;
```
```
root@172.16.6.59:test>PREPARE `books_query` FROM '
'> select t1.nickname, t1.balance
'> from users t1 LEFT JOIN ratings t2 on t1.id = t2.user_id
'> where t2.user_id=?';
Query OK, 0 rows affected (0.05 sec)

root@172.16.6.59:test>
root@172.16.6.59:test>SET @user_id = 1;
Query OK, 0 rows affected (0.04 sec)

root@172.16.6.59:test>EXECUTE `books_query` USING @user_id;
Empty set, 1 warning (0.05 sec)

root@172.16.6.59:test>show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1105 | skip prepared plan-cache: eq(test.ratings.user_id, 1) affects null check |
+---------+------+--------------------------------------------------------------------------+
1 row in set (0.05 sec)
```
version: v8.5.1

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.