ERROR 1690 (22003): constant 9223372036854775808 overflows bigint

[yahonda]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Startup TiDB Playground

% tiup playground nightly

2. Connect to the TiDB Playground

% mysql --comments --host 127.0.0.1 --port 4000 -u root test

3. Run these sql statements

CREATE TABLE `authors` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) NOT NULL, `author_address_id` bigint, `author_address_extra_id` bigint, `organization_id` varchar(255), `owned_essay_id` varchar(255), INDEX `index_authors_on_author_address_id` (`author_address_id`), INDEX `index_authors_on_author_address_extra_id` (`author_address_extra_id`));

CREATE TABLE `posts` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `author_id` bigint, `title` varchar(255) NOT NULL, `body` text NOT NULL, `type` varchar(255), `legacy_comments_count` int DEFAULT 0, `taggings_with_delete_all_count` int DEFAULT 0, `taggings_with_destroy_count` int DEFAULT 0, `tags_count` int DEFAULT 0, `indestructible_tags_count` int DEFAULT 0, `tags_with_destroy_count` int DEFAULT 0, `tags_with_nullify_count` int DEFAULT 0, INDEX `index_posts_on_author_id` (`author_id`));

INSERT INTO `authors` (`id`, `name`, `author_address_id`, `author_address_extra_id`, `organization_id`, `owned_essay_id`) VALUES (1, 'David', 1, 2, 'No Such Agency', 'A Modest Proposal'), (2, 'Mary', 3, DEFAULT, DEFAULT, DEFAULT), (3, 'Bob', 4, DEFAULT, DEFAULT, DEFAULT);

INSERT INTO `posts` (`id`, `author_id`, `title`, `body`, `type`, `legacy_comments_count`, `taggings_with_delete_all_count`, `taggings_with_destroy_count`, `tags_count`, `indestructible_tags_count`, `tags_with_destroy_count`, `tags_with_nullify_count`) VALUES (1, 1, 'Welcome to the weblog', 'Such a lovely day', 'Post', 2, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (2, 1, 'So I was thinking', 'Like I hopefully always am', 'SpecialPost', 1, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (3, 0, 'I don\'t have any comments', 'I just don\'t want to', 'Post', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (4, 1, 'sti comments', 'hello', 'Post', 5, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (5, 1, 'sti me', 'hello', 'StiPost', 2, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (6, 1, 'habtm sti test', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (7, 2, 'eager loading with OR\'d conditions', 'hello', 'Post', 1, DEFAULT, DEFAULT, 3, DEFAULT, DEFAULT, DEFAULT), (8, 3, 'misc post by bob', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (9, 2, 'misc post by mary', 'hullo', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (10, 3, 'other post by bob', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (11, 2, 'other post by mary', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT);

SELECT `posts`.* FROM `posts` WHERE (id = 1 or id = 9223372036854775808);

2. What did you expect to see? (Required)

SELECT `posts`.* FROM `posts` WHERE (id = 1 or id = 9223372036854775808); should return the row as MySQL 8.0.33 does.

mysql> SELECT `posts`.* FROM `posts` WHERE (id = 1 or id = 9223372036854775808);
+----+-----------+-----------------------+-------------------+------+-----------------------+--------------------------------+-----------------------------+------------+---------------------------+-------------------------+-------------------------+
| id | author_id | title                 | body              | type | legacy_comments_count | taggings_with_delete_all_count | taggings_with_destroy_count | tags_count | indestructible_tags_count | tags_with_destroy_count | tags_with_nullify_count |
+----+-----------+-----------------------+-------------------+------+-----------------------+--------------------------------+-----------------------------+------------+---------------------------+-------------------------+-------------------------+
|  1 |         1 | Welcome to the weblog | Such a lovely day | Post |                     2 |                              0 |                           0 |          1 |                         0 |                       0 |                       0 |
+----+-----------+-----------------------+-------------------+------+-----------------------+--------------------------------+-----------------------------+------------+---------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

The same query raises ERROR 1690 (22003): constant 9223372036854775808 overflows bigint

mysql> CREATE TABLE `authors` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) NOT NULL, `author_address_id` bigint, `author_address_extra_id` bigint, `organization_id` varchar(255), `owned_essay_id` varchar(255), INDEX `index_authors_on_author_address_id` (`author_address_id`), INDEX `index_authors_on_author_address_extra_id` (`author_address_extra_id`));
Query OK, 0 rows affected (0.24 sec)

mysql> CREATE TABLE `posts` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `author_id` bigint, `title` varchar(255) NOT NULL, `body` text NOT NULL, `type` varchar(255), `legacy_comments_count` int DEFAULT 0, `taggings_with_delete_all_count` int DEFAULT 0, `taggings_with_destroy_count` int DEFAULT 0, `tags_count` int DEFAULT 0, `indestructible_tags_count` int DEFAULT 0, `tags_with_destroy_count` int DEFAULT 0, `tags_with_nullify_count` int DEFAULT 0, INDEX `index_posts_on_author_id` (`author_id`));
Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO `authors` (`id`, `name`, `author_address_id`, `author_address_extra_id`, `organization_id`, `owned_essay_id`) VALUES (1, 'David', 1, 2, 'No Such Agency', 'A Modest Proposal'), (2, 'Mary', 3, DEFAULT, DEFAULT, DEFAULT), (3, 'Bob', 4, DEFAULT, DEFAULT, DEFAULT);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `posts` (`id`, `author_id`, `title`, `body`, `type`, `legacy_comments_count`, `taggings_with_delete_all_count`, `taggings_with_destroy_count`, `tags_count`, `indestructible_tags_count`, `tags_with_destroy_count`, `tags_with_nullify_count`) VALUES (1, 1, 'Welcome to the weblog', 'Such a lovely day', 'Post', 2, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (2, 1, 'So I was thinking', 'Like I hopefully always am', 'SpecialPost', 1, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (3, 0, 'I don\'t have any comments', 'I just don\'t want to', 'Post', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (4, 1, 'sti comments', 'hello', 'Post', 5, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (5, 1, 'sti me', 'hello', 'StiPost', 2, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (6, 1, 'habtm sti test', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (7, 2, 'eager loading with OR\'d conditions', 'hello', 'Post', 1, DEFAULT, DEFAULT, 3, DEFAULT, DEFAULT, DEFAULT), (8, 3, 'misc post by bob', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (9, 2, 'misc post by mary', 'hullo', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (10, 3, 'other post by bob', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT), (11, 2, 'other post by mary', 'hello', 'Post', DEFAULT, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT `posts`.* FROM `posts` WHERE (id = 1 or id = 9223372036854775808);
ERROR 1690 (22003): constant 9223372036854775808 overflows bigint
mysql>

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.3.0-alpha
Edition: Community
Git Commit Hash: fc906e745ac2ed5ed2a152894d2e7229c3f05ac0
Git Branch: heads/refs/tags/v7.3.0-alpha
UTC Build Time: 2023-08-01 14:25:46
GoVersion: go1.20.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

[yahonda]

This behavior is tested at one of the Ruby on Rails unit tests.

https://github.com/rails/rails/blob/ff470edccc746fe3cf466a0fa54fb08383e8de66/activerecord/test/cases/relation/or_test.rb#L34-L37

[windtalker]

It is related to non_prepared_plan_cache

mysql> set tidb_enable_non_prepared_plan_cache=1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT `posts`.* FROM `posts` WHERE (id = 1 or id = 9223372036854775808);
ERROR 1690 (22003): constant 9223372036854775808 overflows bigint
mysql> set tidb_enable_non_prepared_plan_cache=0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT `posts`.* FROM `posts` WHERE (id = 1 or id = 9223372036854775808);
+----+-----------+-----------------------+-------------------+------+-----------------------+--------------------------------+-----------------------------+------------+---------------------------+-------------------------+-------------------------+
| id | author_id | title                 | body              | type | legacy_comments_count | taggings_with_delete_all_count | taggings_with_destroy_count | tags_count | indestructible_tags_count | tags_with_destroy_count | tags_with_nullify_count |
+----+-----------+-----------------------+-------------------+------+-----------------------+--------------------------------+-----------------------------+------------+---------------------------+-------------------------+-------------------------+
|  1 |         1 | Welcome to the weblog | Such a lovely day | Post |                     2 |                              0 |                           0 |          1 |                         0 |                       0 |                       0 |
+----+-----------+-----------------------+-------------------+------+-----------------------+--------------------------------+-----------------------------+------------+---------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)