MDEV-17724 Wrong result for BETWEEN 0 AND 18446744073709551615

The fix for "MDEV-17698 MEMORY engine performance regression"
previously fixed this problem.

- Adding the test for MDEV-17724
- Re-recording wrong results for tests:
  * engines/iuds/r/insert_number
  * engines/iuds/r/update_delete_number
  which started to fail since MDEV-17698

4 files changed, 137 insertions, 0 deletions

diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index b06ec5805a0..760b9c7b205 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -508,3 +508,14 @@ DROP TABLE t1;
 SELECT 100 BETWEEN 1 AND 9223372036854775808;
 100 BETWEEN 1 AND 9223372036854775808
 1
+#
+# MDEV-17724 Wrong result for BETWEEN 0 AND 18446744073709551615
+#
+CREATE TABLE t1 (c1 bigint(20) unsigned NOT NULL);
+INSERT INTO t1 VALUES (0),(101),(255);
+SELECT * FROM t1 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1;
+c1
+0
+101
+255
+DROP TABLE t1;
diff --git a/mysql-test/suite/engines/iuds/r/insert_number.result b/mysql-test/suite/engines/iuds/r/insert_number.result
index ab56b82807c..9f937b33364 100644
--- a/mysql-test/suite/engines/iuds/r/insert_number.result
+++ b/mysql-test/suite/engines/iuds/r/insert_number.result
@@ -31565,8 +31565,33 @@ c1	c2	c3	c4	c5	c6	c7
 0	-9223372036854775808	1	2	3	4	5
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
+0	0	17	18	19	20	21
+0	124	22	23	24	25	26
+0	124	27	28	29	30	31
+0	-9223372036854775808	31	32	33	34	35
+0	0	32	32	34	35	36
+101	0	37	38	39	40	41
+101	-102	103	104	105	106	107
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	101	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	113	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	26	27	28	29	30
+18446744073709551615	9223372036854775807	36	37	38	39	40
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
 0	NULL	5	6	NULL	0	NULL
@@ -31726,8 +31751,33 @@ c1	c2	c3	c4	c5	c6	c7
 0	-9223372036854775808	31	32	33	34	35
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
+0	124	27	28	29	30	31
+0	124	22	23	24	25	26
+0	0	17	18	19	20	21
+0	-9223372036854775808	1	2	3	4	5
+0	NULL	5	6	NULL	0	NULL
+101	-102	103	104	105	106	107
+101	0	37	38	39	40	41
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	113	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	101	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	36	37	38	39	40
+18446744073709551615	9223372036854775807	26	27	28	29	30
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
 0	0	32	32	34	35	36
@@ -31993,8 +32043,33 @@ c1	c2	c3	c4	c5	c6	c7
 18446744073709551615	9223372036854775807	36	37	38	39	40
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
+0	0	17	18	19	20	21
+0	124	22	23	24	25	26
+0	124	27	28	29	30	31
+0	-9223372036854775808	31	32	33	34	35
+0	0	32	32	34	35	36
+101	0	37	38	39	40	41
+101	-102	103	104	105	106	107
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	101	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	113	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	26	27	28	29	30
+18446744073709551615	9223372036854775807	36	37	38	39	40
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
 0	NULL	5	6	NULL	0	NULL
@@ -32154,8 +32229,33 @@ c1	c2	c3	c4	c5	c6	c7
 18446744073709551615	9223372036854775807	26	27	28	29	30
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
+0	124	27	28	29	30	31
+0	124	22	23	24	25	26
+0	0	17	18	19	20	21
+0	-9223372036854775808	1	2	3	4	5
+0	NULL	5	6	NULL	0	NULL
+101	-102	103	104	105	106	107
+101	0	37	38	39	40	41
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	113	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	101	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	36	37	38	39	40
+18446744073709551615	9223372036854775807	26	27	28	29	30
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
 0	0	32	32	34	35	36
diff --git a/mysql-test/suite/engines/iuds/r/update_delete_number.result b/mysql-test/suite/engines/iuds/r/update_delete_number.result
index 8e190870092..e676b7a79f8 100644
--- a/mysql-test/suite/engines/iuds/r/update_delete_number.result
+++ b/mysql-test/suite/engines/iuds/r/update_delete_number.result
@@ -3768,8 +3768,25 @@ c1	c2	c3
 -12	18446744073709551615	12
 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1;
 c1	c2	c3
+-4	4	4
+-9	9	9
+0	255	13
+-9223372036854775808	18446744073709551615	12
+-12	18446744073709551615	12
+-11	18446744073709551615	11
+-8	18446744073709551615	8
+-7	18446744073709551615	7
+-6	18446744073709551615	6
+-5	18446744073709551615	5
+-3	18446744073709551615	3
+-2	18446744073709551615	2
+-1	18446744073709551615	1
+50	18446744073709551615	10
+9223372036854775807	18446744073709551615	14
 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
 c1	c2	c3
+-4	4	4
+-9	9	9
 SELECT * FROM t2 WHERE c2 IN(0,18446744073709551615) ORDER BY c2,c1 DESC;
 c1	c2	c3
 9223372036854775807	18446744073709551615	14
diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test
index fb18d60edd9..71fef813a8d 100644
--- a/mysql-test/t/bigint.test
+++ b/mysql-test/t/bigint.test
@@ -414,3 +414,12 @@ DROP TABLE t1;
 --echo # MDEV-9372 select 100 between 1 and 9223372036854775808 returns false
 --echo #
 SELECT 100 BETWEEN 1 AND 9223372036854775808;
+
+--echo #
+--echo # MDEV-17724 Wrong result for BETWEEN 0 AND 18446744073709551615
+--echo #
+
+CREATE TABLE t1 (c1 bigint(20) unsigned NOT NULL);
+INSERT INTO t1 VALUES (0),(101),(255);
+SELECT * FROM t1 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1;
+DROP TABLE t1;