BIT_XOR window function produces incorrect results with large integers

1

-- Create test table

2

CREATE TABLE inhabitant (

3

personid INT,

4

name VARCHAR(50),

5

job VARCHAR(20),

6

hash BIGINT,

7

gold DECIMAL(10,4)

8

);

9

10

-- Insert test data

11

INSERT INTO inhabitant VALUES

12

(1, 'Paul Bakerman', 'baker', 667574576304, 500),

13

(2, 'Ernest Perry', 'weaponsmith', 73993229357, 400),

14

(3, 'Rita Ox', 'baker', 41932111594, 600),

15

(4, 'Carl Ox', 'merchant', 843473717421, 250),

16

(5, 'Dirty Dieter', 'smith', 637588674311, 700),

17

(6, 'Gerry Slaughterer', 'butcher', 872618219253, 3000),

18

(7, 'Peter Slaughterer', 'butcher', 648197551204, 2800),

19

(8, 'Arthur Tailor', 'pilot', 823379478192, 490),

20

(9, 'Tiffany Drummer', 'baker', 434738589188, 650),

21

(10, 'Peter Drummer', 'smith', 713565808545, 550),

22

(11, 'Dirty Diane', 'farmer', 317598695175, 10),

23

(12, 'Otto Alexander', 'dealer', 371232179800, 800),

24

(13, 'Fred Dix', 'author', 865222686964, 420),

25

(14, 'Enrico Carpenter', 'weaponsmith', 1052048305797, 390),

26

(15, 'Helen Grasshead', 'dealer', 618786795696, 650),

27

(16, 'Ivy Hatter', 'dealer', 255939466731, 680),

28

(17, 'Edward Grasshead', 'butcher', 296311699507, 2770),

29

(18, 'Ryan Horse', 'blacksmith', 98143389713, 390),

30

(19, 'Ann Meaty', 'butcher', 889525893587, 2800);

31

32

-- Query showing the bug

33

SELECT job, sum(hash) AS hash_sum,

34

bit_xor(sum(hash)) OVER () AS window_xor

35

FROM inhabitant

36

GROUP BY job

37

ORDER BY job;

38

39

-- Manual verification (should match window_xor but doesn't in MariaDB)

40

SELECT 865222686964 ^ 1144245277086 ^ 98143389713 ^ 2706653363551 ^

41

1245958442227 ^ 317598695175 ^ 843473717421 ^ 823379478192 ^

42

1351154482856 ^ 1126041535154 AS manual_xor;