MySQL Bugs: #109912: Bitwise operation on negative number cause some confusing results

Bug #109912	Bitwise operation on negative number cause some confusing results
Submitted:	3 Feb 2023 7:05	Modified:	6 Feb 2023 14:45
Reporter:	Jiazheng Xu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	ALL	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
When we do some select with "<< 0" on negative numbers, what really happened in mysql? Here we got some confusing results

case 1:
a very simple query: "SELECT -1 >> 0;"
return 18446744073709551615, what does this number mean, why not -1？

case 2:
DROP TABLE IF EXISTS t0;
CREATE TABLE `t0` (
  `c0` int DEFAULT NULL
);
INSERT INTO t0 VALUES (-1782140092);
query 1:select -1782140092 << 0,c0,(-1782140092 << 0) NOT IN (c0,0) from t0;
query 2:select 18446744071927411524 NOT IN (-1782140092,0);

why two queries got different results？

Actually we don't think these really are bug, but we can't get documentition about how mysql works with this literal negative numbers when doing << or >> operation, looking forward to your reply

How to repeat:
case 1:
SELECT -1 >> 0;
SELECT -1 << 0;
SELECT -1244545764 << 0;
It seems like any negative numbers do "<< 0" operation will return same result 18446744072465005852

case 2:
DROP TABLE IF EXISTS t0;
CREATE TABLE `t0` (
  `c0` int DEFAULT NULL
);
INSERT INTO t0 VALUES (-1782140092);
select -1782140092 << 0,c0,(-1782140092 << 0) NOT IN (c0,0) from t0;
select 18446744071927411524 NOT IN (-1782140092,0);

Hi Mr. Xu,

Thank you for your bug report.

We have been able to repeat all your test cases.

This behaviour simply feels wrong.

This affects both 5.7 and 8.0. This is, however, a minor bug as nobody shifts bits by zero.

Verified as reported.