[MDEV-681] LP:1002326 - Wrong result with empty table, implicit grouping and non-aggregated column in select Created: 2012-05-21  Updated: 2014-04-11  Resolved: 2014-04-11

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Timour Katchaounov (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Won't Fix Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1002326.xml    

 Description   

The following test case produces wrong result:

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (7),(0);
 
CREATE TABLE t2 (b INT);
 
 
 
SELECT SUM(a), a FROM (t1, t2) WHERE t1.a >= 4;
 
+--------+------+
 
| SUM(a) | a    |
 
+--------+------+
 
|   NULL | NULL |
 
+--------+------+
 
1 row in set (0.00 sec)
 
 
 
SELECT SUM(a), a FROM t1, t2 WHERE t1.a >= 4;
 
+--------+------+
 
| SUM(a) | a    |
 
+--------+------+
 
|      7 |    7 |
 
+--------+------+

The only difference between the queries are the braces in the FROM clause.

This test case was extracted from bug lp:1002079.

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2012-05-21 ]

Launchpad bug id: 1002326

Comment by Timour Katchaounov (Inactive) [ 2012-05-21 ]

Re: Wrong result with empty table, HAVING, implicit grouping and non-aggregated column in select
The bug is present only in MariaDB 5.5 (not 5.2, and 5.5), and requires a non-aggregated column (which is a MySQL extension).
Thus I consider it low priority.

Comment by Timour Katchaounov (Inactive) [ 2013-02-28 ]

Analysis:

In both cases JOIN::prepare sets table->maybe_null= 1 for all tables because there is mixed implicit grouping (mixes an aggregate with no aggregate).
The idea is to tell the query engine to produce a NULL row for an empty result set.

The join order is "t2, t1", because t2 is constant.

1.correct result execution steps
1.1
In the case with the braces (t1, t2), the join is considered as nested, and it is processed by simplify_joins. There it resets to 0 table->maybe_null:
if (!tbl->embedding && !tbl->on_expr && tbl->table)
tbl->table->maybe_null= FALSE;
1.2
Then join_read_const_table executes this branch:
if ((error=join_read_system(tab)))

{ // Info for DESCRIBE tab->info="const row not found"; /* Mark for EXPLAIN that the row was not found */ pos->records_read=0.0; pos->ref_depend_map= 0; if (!table->maybe_null || error > 0) DBUG_RETURN(error); }

Since table->maybe_null == false, this branch returns -1.
1.3
The caller of join_read_const_table is make_join_statistics, as a result of the -1 return it doesn't add t2 to found_const_table_map.
1.4 Finally JOIN::exec checks:
if (const_table_map != found_const_table_map &&
!(select_options & SELECT_DESCRIBE))

{ // There is at least one empty const table zero_result_cause= "no matching row in const table"; DBUG_PRINT("error",("Error: %s", zero_result_cause)); error= 0; goto setup_subq_exit; }

Having a zero_result_cause results in calling return_zero_rows, which produces the NULL row.

2. Incorrect execution
2.1
The difference is already in step 1.1 - the join is not nested, and simplify_joins doesn't process the join tree.
table->maybe_null remains TRUE as set by JOIN::prepare.
2.2
join_read_const_table doesn't return -1, instead returns 0.
2.3
t2 is added to found_const_table_map
2.4
the same check in JOIN::exec as in 1.4:
if (const_table_map != found_const_table_map &&
fails, so zero_rezult_cause is not set.
2.5
Execution continues normally - sub_select begins with table t1, and finds one row, which is produced as a result.

Comment by Elena Stepanova [ 2014-04-11 ]

The bug is 5.3-only (apparently there is a typo in a previous comment about versions). Since it only happens in the old version and is a corner case with a non-deterministic query, I think it's not critical to fix it. Closing.

Generated at Thu Feb 08 06:30:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.