Planner: Plan Cache incorrectly blocks nullable-but-logically-non-null Join Keys
- 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
Assessment
This issue has not been assessed yet.