Description:
ELT(N, str1, str2, ...) returns NULL when N is greater than the number of
arguments. Item_func_elt::val_str() follows that for every N, but val_int() and
val_real() -- used whenever ELT() is evaluated as a number -- cast N to a 32-bit
uint first, so N = 2^32 + k selects argument k. The same expression is then
NULL and non-NULL at the same time:
SELECT ELT(4294967297,1,2,3) IS NULL, -- 1 (string path: NULL)
ELT(4294967297,1,2,3)+0, -- 1 (numeric path: argument 1)
ELT(4294967297,1,2,3)|0, -- 1
ELT(4294967298,1,2,3)+0, -- 2
ELT(4294967297,1.1,1.2,1.3)+0, -- 1.1
ELT(4294967297,'a','b','c'), -- NULL
ELT(4,1,2,3)+0, -- NULL (4 > 3 arguments: correct)
ELT(-4294967295,1,2,3)+0; -- 1 (also wraps)
From a table, an aggregate counts a row that IS NULL says is NULL:
CREATE TABLE t17 (id INT PRIMARY KEY, n BIGINT, a INT, b INT, c INT);
INSERT INTO t17 VALUES (1, 4294967297, 10, 20, 30), (2, 4, 10, 20, 30);
SELECT id, ELT(n,a,b,c), ELT(n,a,b,c) IS NULL, ELT(n,a,b,c)+0, SUM(ELT(n,a,b,c))
FROM t17 GROUP BY id ORDER BY id;
1 NULL 1 10 10
2 NULL 1 NULL NULL
The manual: "ELT() returns NULL if N is less than 1, greater than the number of
arguments, or NULL." TiDB returns NULL on every path for these values.
Where (trunk 3b99be40): sql/item_strfunc.cc
2425 double Item_func_elt::val_real() {
2427 uint tmp;
2429 if ((tmp = (uint)args[0]->val_int()) == 0 || args[0]->null_value ||
2430 tmp >= arg_count)
...
2437 longlong Item_func_elt::val_int() {
2439 uint tmp;
2441 if ((tmp = (uint)args[0]->val_int()) == 0 || args[0]->null_value ||
2442 tmp >= arg_count)
while val_str() (line 2450) keeps a longlong:
2453 const longlong eltno = args[0]->val_int();
2457 if (eltno <= 0 || eltno >= arg_count) {
How to repeat:
SELECT ELT(4294967297,1,2,3) IS NULL, ELT(4294967297,1,2,3)+0,
ELT(4294967297,1,2,3)|0, ELT(4294967297,1.1,1.2,1.3)+0, ELT(4,1,2,3)+0;
-- expected: 1, NULL, NULL, NULL, NULL
-- actual: 1, 1, 1, 1.1, NULL
CREATE TABLE t17 (id INT PRIMARY KEY, n BIGINT, a INT, b INT, c INT);
INSERT INTO t17 VALUES (1, 4294967297, 10, 20, 30);
SELECT ELT(n,a,b,c) IS NULL, ELT(n,a,b,c)+0 FROM t17;
SELECT SUM(ELT(n,a,b,c)) FROM t17;
-- expected: 1, NULL and NULL
-- actual: 1, 10 and 10
Suggested fix:
Use the same longlong check as val_str() in val_int() and val_real():
const longlong eltno = args[0]->val_int();
if (args[0]->null_value || eltno <= 0 || eltno >= arg_count) return 0;
Description: ELT(N, str1, str2, ...) returns NULL when N is greater than the number of arguments. Item_func_elt::val_str() follows that for every N, but val_int() and val_real() -- used whenever ELT() is evaluated as a number -- cast N to a 32-bit uint first, so N = 2^32 + k selects argument k. The same expression is then NULL and non-NULL at the same time: SELECT ELT(4294967297,1,2,3) IS NULL, -- 1 (string path: NULL) ELT(4294967297,1,2,3)+0, -- 1 (numeric path: argument 1) ELT(4294967297,1,2,3)|0, -- 1 ELT(4294967298,1,2,3)+0, -- 2 ELT(4294967297,1.1,1.2,1.3)+0, -- 1.1 ELT(4294967297,'a','b','c'), -- NULL ELT(4,1,2,3)+0, -- NULL (4 > 3 arguments: correct) ELT(-4294967295,1,2,3)+0; -- 1 (also wraps) From a table, an aggregate counts a row that IS NULL says is NULL: CREATE TABLE t17 (id INT PRIMARY KEY, n BIGINT, a INT, b INT, c INT); INSERT INTO t17 VALUES (1, 4294967297, 10, 20, 30), (2, 4, 10, 20, 30); SELECT id, ELT(n,a,b,c), ELT(n,a,b,c) IS NULL, ELT(n,a,b,c)+0, SUM(ELT(n,a,b,c)) FROM t17 GROUP BY id ORDER BY id; 1 NULL 1 10 10 2 NULL 1 NULL NULL The manual: "ELT() returns NULL if N is less than 1, greater than the number of arguments, or NULL." TiDB returns NULL on every path for these values. Where (trunk 3b99be40): sql/item_strfunc.cc 2425 double Item_func_elt::val_real() { 2427 uint tmp; 2429 if ((tmp = (uint)args[0]->val_int()) == 0 || args[0]->null_value || 2430 tmp >= arg_count) ... 2437 longlong Item_func_elt::val_int() { 2439 uint tmp; 2441 if ((tmp = (uint)args[0]->val_int()) == 0 || args[0]->null_value || 2442 tmp >= arg_count) while val_str() (line 2450) keeps a longlong: 2453 const longlong eltno = args[0]->val_int(); 2457 if (eltno <= 0 || eltno >= arg_count) { How to repeat: SELECT ELT(4294967297,1,2,3) IS NULL, ELT(4294967297,1,2,3)+0, ELT(4294967297,1,2,3)|0, ELT(4294967297,1.1,1.2,1.3)+0, ELT(4,1,2,3)+0; -- expected: 1, NULL, NULL, NULL, NULL -- actual: 1, 1, 1, 1.1, NULL CREATE TABLE t17 (id INT PRIMARY KEY, n BIGINT, a INT, b INT, c INT); INSERT INTO t17 VALUES (1, 4294967297, 10, 20, 30); SELECT ELT(n,a,b,c) IS NULL, ELT(n,a,b,c)+0 FROM t17; SELECT SUM(ELT(n,a,b,c)) FROM t17; -- expected: 1, NULL and NULL -- actual: 1, 10 and 10 Suggested fix: Use the same longlong check as val_str() in val_int() and val_real(): const longlong eltno = args[0]->val_int(); if (args[0]->null_value || eltno <= 0 || eltno >= arg_count) return 0;