Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-681

LP:1002326 - Wrong result with empty table, implicit grouping and non-aggregated column in select

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Minor
        • Resolution: Won't Fix
        • Affects Version/s: 5.3.12
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          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.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                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.

                Show
                timour Timour Katchaounov added a comment - 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.
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 1002326

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 1002326
                Hide
                Permalink
                timour Timour Katchaounov added a comment -

                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.

                Show
                timour Timour Katchaounov added a comment - 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.
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                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.

                Show
                elenst Elena Stepanova added a comment - 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.

                  People

                  • Assignee:
                    timour Timour Katchaounov
                    Reporter:
                    timour Timour Katchaounov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: