Description:
Filter containing left shift operator is throwing "ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(32 - `authservice`.`route`.`networkLen`)'".
The table structure I am using is as follows
CREATE TABLE `routes` (
`id` bigint NOT NULL AUTO_INCREMENT,
`fieldId` bigint NOT NULL,
`ip` varchar(255) NOT NULL,
`networkLen` int unsigned NOT NULL DEFAULT '24',
`addressType` enum('IPv4','IPv6') NOT NULL DEFAULT 'IPv4',
PRIMARY KEY (`id`),
KEY `fieldId` (`fieldId`,`ip`,`networkLen`)
) ENGINE=InnoDB AUTO_INCREMENT=49772538 DEFAULT CHARSET=utf8mb3;
On such a table, a query with filter containing the left shift operator is failing.
mysql> SELECT DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4' AND (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(32 - `authservice`.`route`.`networkLen`)'
When I use no NO_ICP hint, the error goes away.
mysql> SELECT /*+ NO_ICP(route) */ DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4' AND (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip);
+----------+
| routeId |
+----------+
| 49672547 |
+----------+
1 row in set (0.07 sec)
The explain plan looks like after disabling ICP.
mysql> EXPLAIN SELECT /*+ NO_ICP(route) */ DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType=
'IPv4' AND (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip);
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | route | NULL | ref | PRIMARY,fieldId | fieldId | 8 | const | 49999 | 50.00 | Using where |
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
When ICP is not disabled, the plan looks like this
mysql> EXPLAIN SELECT DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4' AND (
-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip);
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+------------------------------------+
| 1 | SIMPLE | route | NULL | ref | PRIMARY,fieldId | fieldId | 8 | const | 49999 | 50.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
Please note that this was not throwing error in previous versions. AFAIK I know It definitely works in 5.7. Even after we upgraded to 8.x, we did not face the issue until recently. Currently we are in 8.0.33 and we face the issue.
Please note that the same filter predicate used in select does not throw any error.
mysql> SELECT (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip) FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4';
+------------------------------------------------------------------------+
| (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip) |
+------------------------------------------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
.......
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+------------------------------------------------------------------------+
16691 rows in set (0.05 sec)
How to repeat:
Create table as
CREATE TABLE `routes` (
`id` bigint NOT NULL AUTO_INCREMENT,
`fieldId` bigint NOT NULL,
`ip` varchar(255) NOT NULL,
`networkLen` int unsigned NOT NULL DEFAULT '24',
`addressType` enum('IPv4','IPv6') NOT NULL DEFAULT 'IPv4',
PRIMARY KEY (`id`),
KEY `fieldId` (`fieldId`,`ip`,`networkLen`)
) ENGINE=InnoDB AUTO_INCREMENT=49772538 DEFAULT CHARSET=utf8mb3;
Insert random data like this.(I have about 100000 rows in my table)
fielId column is one of 1, 2,3, 4
ip is either ipv4 or ipv6 address
networkLen is 0-32 for ipv4 and 0-128 for ipv6.
INSERT INTO `routes` VALUES
(49672537,3,'61.184.111.131',22,'IPv4'),
(49672538,3,'4a2:3066:12a4:be77:bcbd:df91:517c:251a',48,'IPv6'),
(49672539,2,'169.206.212.22',9,'IPv4'),
(49672540,3,'685d:46de:30e0:e7a4:c313:d6cf:c760:4db4',69,'IPv6'),
(49672541,1,'e391:c972:f038:7032:7e8a:c72a:cbf3:a859',108,'IPv6'),
(49672542,1,'7a09:c286:911d:a023:ed36:1f3:108c:9bdd',102,'IPv6'),
(49672543,3,'3715:1ea6:151d:2e6:4de1:570:5d00:64e8',84,'IPv6'),
(49672544,3,'88.73.103.218',27,'IPv4'),
(49672545,1,'138.10.64.0',4,'IPv4'),
(49672546,1,'179.159.7.109',1,'IPv4'),
(49672547,1,'183.115.195.215',16,'IPv4'),
(49672548,3,'9c3b:3cde:dd48:9d0b:caf1:1635:9c53:37aa',0,'IPv6'),
(49672549,3,'103.14.225.128',15,'IPv4');
After this, run the queries given in the description to reproduce this issue.
Description: Filter containing left shift operator is throwing "ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(32 - `authservice`.`route`.`networkLen`)'". The table structure I am using is as follows CREATE TABLE `routes` ( `id` bigint NOT NULL AUTO_INCREMENT, `fieldId` bigint NOT NULL, `ip` varchar(255) NOT NULL, `networkLen` int unsigned NOT NULL DEFAULT '24', `addressType` enum('IPv4','IPv6') NOT NULL DEFAULT 'IPv4', PRIMARY KEY (`id`), KEY `fieldId` (`fieldId`,`ip`,`networkLen`) ) ENGINE=InnoDB AUTO_INCREMENT=49772538 DEFAULT CHARSET=utf8mb3; On such a table, a query with filter containing the left shift operator is failing. mysql> SELECT DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4' AND (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip); ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(32 - `authservice`.`route`.`networkLen`)' When I use no NO_ICP hint, the error goes away. mysql> SELECT /*+ NO_ICP(route) */ DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4' AND (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip); +----------+ | routeId | +----------+ | 49672547 | +----------+ 1 row in set (0.07 sec) The explain plan looks like after disabling ICP. mysql> EXPLAIN SELECT /*+ NO_ICP(route) */ DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType= 'IPv4' AND (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip); +----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+-------------+ | 1 | SIMPLE | route | NULL | ref | PRIMARY,fieldId | fieldId | 8 | const | 49999 | 50.00 | Using where | +----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) When ICP is not disabled, the plan looks like this mysql> EXPLAIN SELECT DISTINCT route.id routeId FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4' AND ( -1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip); +----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+------------------------------------+ | 1 | SIMPLE | route | NULL | ref | PRIMARY,fieldId | fieldId | 8 | const | 49999 | 50.00 | Using index condition; Using where | +----+-------------+-------+------------+------+-----------------+---------+---------+-------+-------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec) Please note that this was not throwing error in previous versions. AFAIK I know It definitely works in 5.7. Even after we upgraded to 8.x, we did not face the issue until recently. Currently we are in 8.0.33 and we face the issue. Please note that the same filter predicate used in select does not throw any error. mysql> SELECT (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip) FROM routes route WHERE route.fieldId = 1 AND route.addressType='IPv4'; +------------------------------------------------------------------------+ | (-1 << (32-networkLen)) & INET_ATON('183.115.195.215') = INET_ATON(ip) | +------------------------------------------------------------------------+ | 0 | | 0 | | 0 | | 0 | ....... | 0 | | 0 | | 0 | | 0 | | 0 | +------------------------------------------------------------------------+ 16691 rows in set (0.05 sec) How to repeat: Create table as CREATE TABLE `routes` ( `id` bigint NOT NULL AUTO_INCREMENT, `fieldId` bigint NOT NULL, `ip` varchar(255) NOT NULL, `networkLen` int unsigned NOT NULL DEFAULT '24', `addressType` enum('IPv4','IPv6') NOT NULL DEFAULT 'IPv4', PRIMARY KEY (`id`), KEY `fieldId` (`fieldId`,`ip`,`networkLen`) ) ENGINE=InnoDB AUTO_INCREMENT=49772538 DEFAULT CHARSET=utf8mb3; Insert random data like this.(I have about 100000 rows in my table) fielId column is one of 1, 2,3, 4 ip is either ipv4 or ipv6 address networkLen is 0-32 for ipv4 and 0-128 for ipv6. INSERT INTO `routes` VALUES (49672537,3,'61.184.111.131',22,'IPv4'), (49672538,3,'4a2:3066:12a4:be77:bcbd:df91:517c:251a',48,'IPv6'), (49672539,2,'169.206.212.22',9,'IPv4'), (49672540,3,'685d:46de:30e0:e7a4:c313:d6cf:c760:4db4',69,'IPv6'), (49672541,1,'e391:c972:f038:7032:7e8a:c72a:cbf3:a859',108,'IPv6'), (49672542,1,'7a09:c286:911d:a023:ed36:1f3:108c:9bdd',102,'IPv6'), (49672543,3,'3715:1ea6:151d:2e6:4de1:570:5d00:64e8',84,'IPv6'), (49672544,3,'88.73.103.218',27,'IPv4'), (49672545,1,'138.10.64.0',4,'IPv4'), (49672546,1,'179.159.7.109',1,'IPv4'), (49672547,1,'183.115.195.215',16,'IPv4'), (49672548,3,'9c3b:3cde:dd48:9d0b:caf1:1635:9c53:37aa',0,'IPv6'), (49672549,3,'103.14.225.128',15,'IPv4'); After this, run the queries given in the description to reproduce this issue.