[MDEV-35238] Wrong results from tables with a single record and an aggregate - Jira

Details

Type: Bug
Status: In Review (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5
Fix Version/s: 10.5
Component/s: None
Labels:
None

Description

This query seems to return a correct result:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=MyISAM;

INSERT INTO t1 VALUES (NULL);

SELECT 1,min(1) FROM t1 WHERE if(uuid_short(), a,1);

+---+--------+

| 1 | min(1) |

+---+--------+

| 1 |   NULL |

+---+--------+

Notice, the WHERE condition returns NULL in this statement and all statements below.

This query on a table with a single record erroneously returns 0 instead of 1

CREATE OR REPLACE TABLE t1 (a int) ENGINE=MyISAM;

INSERT INTO t1 VALUES (NULL);

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

+-----+--------+

| 1+0 | min(1) |

+-----+--------+

|   0 |   NULL |

+-----+--------+

But if the table if empty, the result is correct:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=MyISAM;

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

+-----+--------+

| 1+0 | min(1) |

+-----+--------+

|   1 |   NULL |

+-----+--------+

And if the table has more than one records, the result is also correct:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=MyISAM;

INSERT INTO t1 VALUES (NULL),(NULL);

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

+-----+--------+

| 1+0 | min(1) |

+-----+--------+

|   1 |   NULL |

+-----+--------+

Attachments

Issue Links

relates to

MDEV-34922 Assertion `value.length() == FbtImpl::binary_length()' failed in Type_handler_fbt<FbtImpl, TypeCollectionImpl>::Field_fbt::store_native, Assertion `item->null_value' failed in Type_handler::Item_send_str

Stalled

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2024-10-23 07:59 - edited

Note, with InnoDB it works fine:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=InnoDB;

INSERT INTO t1 VALUES (NULL);

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

+-----+--------+

| 1+0 | min(1) |

+-----+--------+

|   1 |   NULL |

+-----+--------+

MEMORY works incorrectly:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=MEMORY;

INSERT INTO t1 VALUES (NULL);

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

+-----+--------+

| 1+0 | min(1) |

+-----+--------+

|   0 |   NULL |

+-----+--------+

ARIA works incorrectly:

CREATE OR REPLACE TABLE t1 (a int) ENGINE=ARIA;

INSERT INTO t1 VALUES (NULL);

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

+-----+--------+

| 1+0 | min(1) |

+-----+--------+

|   0 |   NULL |

+-----+--------+

Alexander Barkov added a comment - 2024-10-23 07:59 - edited Note, with InnoDB it works fine: CREATE OR REPLACE TABLE t1 (a int ) ENGINE=InnoDB; INSERT INTO t1 VALUES ( NULL ); SELECT 1+0, min (1) FROM t1 WHERE if (uuid_short(), a,1); + -----+--------+ | 1+0 | min (1) | + -----+--------+ | 1 | NULL | + -----+--------+ MEMORY works incorrectly: CREATE OR REPLACE TABLE t1 (a int ) ENGINE=MEMORY; INSERT INTO t1 VALUES ( NULL ); SELECT 1+0, min (1) FROM t1 WHERE if (uuid_short(), a,1); + -----+--------+ | 1+0 | min (1) | + -----+--------+ | 0 | NULL | + -----+--------+ ARIA works incorrectly: CREATE OR REPLACE TABLE t1 (a int ) ENGINE=ARIA; INSERT INTO t1 VALUES ( NULL ); SELECT 1+0, min (1) FROM t1 WHERE if (uuid_short(), a,1); + -----+--------+ | 1+0 | min (1) | + -----+--------+ | 0 | NULL | + -----+--------+

Oleksandr Byelkin added a comment - 2025-01-21 12:18

the different between innodb and others is in optimizations:

REATE TABLE t1 (a int) ENGINE=innodb;

INSERT INTO t1 VALUES (NULL);

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

1+0	min(1)

1	NULL

explain format=json

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

EXPLAIN

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "t1",

      "access_type": "ALL",

      "rows": 1,

      "filtered": 100,

      "attached_condition": "if(uuid_short(),t1.a,1)"

drop table t1;

CREATE TABLE t1 (a int) ENGINE=MyISAM;

INSERT INTO t1 VALUES (NULL);

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

1+0	min(1)

0	NULL

explain format=json

SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1);

EXPLAIN

  "query_block": {

    "select_id": 1,

    "pseudo_bits_condition": "if(uuid_short(),NULL,1)",

    "table": {

      "table_name": "t1",

      "access_type": "system",

      "rows": 1,

      "filtered": 100

drop table t1;

Oleksandr Byelkin added a comment - 2025-01-21 12:18 the different between innodb and others is in optimizations: REATE TABLE t1 (a int) ENGINE=innodb; INSERT INTO t1 VALUES (NULL); SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1); 1+0 min(1) 1 NULL explain format=json SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1); EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "t1", "access_type": "ALL", "rows": 1, "filtered": 100, "attached_condition": "if(uuid_short(),t1.a,1)" } } } drop table t1; CREATE TABLE t1 (a int) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL); SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1); 1+0 min(1) 0 NULL explain format=json SELECT 1+0,min(1) FROM t1 WHERE if(uuid_short(), a,1); EXPLAIN { "query_block": { "select_id": 1, "pseudo_bits_condition": "if(uuid_short(),NULL,1)", "table": { "table_name": "t1", "access_type": "system", "rows": 1, "filtered": 100 } } } drop table t1;

Oleksandr Byelkin added a comment - 2025-01-21 12:33

The difference is in pseudo_bits_cond, it is expression over special tables and constant tables. It is the different with innod (innodb can not report one row tables, i.e. constant tables)

Oleksandr Byelkin added a comment - 2025-01-21 12:33 The difference is in pseudo_bits_cond, it is expression over special tables and constant tables. It is the different with innod (innodb can not report one row tables, i.e. constant tables)

Oleksandr Byelkin added a comment - 2025-01-22 09:42

uuid_short() is important

Oleksandr Byelkin added a comment - 2025-01-22 09:42 uuid_short() is important

Oleksandr Byelkin added a comment - 2025-01-22 10:03 - edited

longlong Item_copy_string::val_int()

  int err;

  return null_value ? 0 : str_value.charset()->strntoll(str_value.ptr(),

                                                        str_value.length(), 10,

                                                        (char**) 0, &err);

returns 0 (str_value is empty)

Oleksandr Byelkin added a comment - 2025-01-22 10:03 - edited longlong Item_copy_string::val_int() { int err; return null_value ? 0 : str_value.charset()->strntoll(str_value.ptr(), str_value.length(), 10, (char**) 0, &err); } returns 0 (str_value is empty)

Oleksandr Byelkin added a comment - 2025-01-22 16:44

It is clear that this fix the problem, the question is how it work in other cases (looks like field list is different)

diff --git a/sql/sql_select.cc b/sql/sql_select.cc

index f0da265f86b..bb7cc72e623 100644

--- a/sql/sql_select.cc

+++ b/sql/sql_select.cc

@@ -20788,6 +20788,8 @@ do_select(JOIN *join, Procedure *procedure)

*/

         clear_tables(join, &cleared_tables);

+      if (join->tmp_table_param.copy_funcs.elements)

+        copy_fields(&join->tmp_table_param);

       if (!join->having || join->having->val_int())

         List<Item> *columns_list= (procedure ? &join->procedure_fields_list :

Oleksandr Byelkin added a comment - 2025-01-22 16:44 It is clear that this fix the problem, the question is how it work in other cases (looks like field list is different) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f0da265f86b..bb7cc72e623 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20788,6 +20788,8 @@ do_select(JOIN *join, Procedure *procedure) */ clear_tables(join, &cleared_tables); } + if (join->tmp_table_param.copy_funcs.elements) + copy_fields(&join->tmp_table_param); if (!join->having || join->having->val_int()) { List<Item> *columns_list= (procedure ? &join->procedure_fields_list :

Oleksandr Byelkin added a comment - 2025-01-22 20:52

It looks like just this execution path, where field_items changed on copy functions but not items1.

All cases in our est site which go by this path are in subqueries where aggregate function is the only result, but is change it we get the same:

CREATE TABLE t1 (a int PRIMARY KEY);

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (a int NOT NULL);

INSERT INTO t2 VALUES (10);

SELECT 1+0, MIN(t1.a) FROM t1,t2 WHERE t2.a = rand();

DROP TABLE t1,t2;

Oleksandr Byelkin added a comment - 2025-01-22 20:52 It looks like just this execution path, where field_items changed on copy functions but not items1. All cases in our est site which go by this path are in subqueries where aggregate function is the only result, but is change it we get the same: CREATE TABLE t1 (a int PRIMARY KEY); INSERT INTO t1 VALUES (1); CREATE TABLE t2 (a int NOT NULL); INSERT INTO t2 VALUES (10); SELECT 1+0, MIN(t1.a) FROM t1,t2 WHERE t2.a = rand(); DROP TABLE t1,t2;

Oleksandr Byelkin added a comment - 2025-01-23 08:36 - edited

commit  4b588818e43133cd3565d93e2673975abf7ef34b  (HEAD -> bb-10.5-MDEV-35238, origin/bb-10.5-MDEV-35238)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Wed Jan 22 22:08:56 2025 +0100

    MDEV-35238 (MDEV-34922) Wrong results from a tables with a single record and an aggregate

    The problem is that copy function was used in field list but never

    copied in this execution path.

    So copy should be performed before returning result.

    Protection against uninitialized copy usage added.

Oleksandr Byelkin added a comment - 2025-01-23 08:36 - edited commit 4b588818e43133cd3565d93e2673975abf7ef34b (HEAD -> bb-10.5-MDEV-35238, origin/bb-10.5-MDEV-35238) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Wed Jan 22 22:08:56 2025 +0100 MDEV-35238 (MDEV-34922) Wrong results from a tables with a single record and an aggregate The problem is that copy function was used in field list but never copied in this execution path. So copy should be performed before returning result. Protection against uninitialized copy usage added.

People

Assignee:: Sergei Petrunia

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2024-10-23 06:59

Updated:: 2025-01-23 14:18

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server