[MDEV-11103] Wrong result (missing rows) on query from a view with WHERE .. ANY, condition_pushdown_for_derived=on - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2(EOL)
Fix Version/s: 10.2.3
Component/s: Optimizer, Views
Labels:
- regression-10.2

Sprint:
10.2.4-3

Description

CREATE TABLE t1 (i INT);

CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;

INSERT INTO t1 VALUES (1),(2);

SELECT * FROM v1 WHERE i <= ANY ( SELECT 3 );

Actual result, 10.2 82b974a1b6
MariaDB [test]> SELECT * FROM v1 WHERE i <= ANY ( SELECT 3 );
Empty set (0.00 sec)

Expected result
MariaDB [test]> SELECT * FROM v1 WHERE i <= ANY ( SELECT 3 );
+------+
\| i \|
+------+
\| 1 \|
\| 2 \|
+------+
2 rows in set (0.00 sec)

Attachments

Activity

Ascending order - Click to sort in descending order

Oleksandr Byelkin added a comment - 2016-11-16 15:03

Some how condition (not((t1.i <= 3))) was pushed in the view which made view have no records.

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 0.0577,

    "table": {

      "table_name": "<derived3>",

      "access_type": "ALL",

      "r_loops": 1,

      "rows": 2,

      "r_rows": 0,

      "r_total_time_ms": 2.3e-4,

      "filtered": 100,

      "r_filtered": 100,

      "attached_condition": "<nop>((v1.i <= 3))",

      "materialized": {

        "query_block": {

          "select_id": 3,

          "r_loops": 1,

          "r_total_time_ms": 0.0244,

          "table": {

            "table_name": "t1",

            "access_type": "ALL",

            "r_loops": 1,

            "rows": 2,

            "r_rows": 2,

            "r_total_time_ms": 0.0048,

            "filtered": 100,

            "r_filtered": 0,

            "attached_condition": "(not((t1.i <= 3)))"

Oleksandr Byelkin added a comment - 2016-11-16 15:03 Some how condition (not((t1.i <= 3))) was pushed in the view which made view have no records. { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.0577, "table": { "table_name": "<derived3>", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 0, "r_total_time_ms": 2.3e-4, "filtered": 100, "r_filtered": 100, "attached_condition": "<nop>((v1.i <= 3))", "materialized": { "query_block": { "select_id": 3, "r_loops": 1, "r_total_time_ms": 0.0244, "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.0048, "filtered": 100, "r_filtered": 0, "attached_condition": "(not((t1.i <= 3)))" } } } } } }

Igor Babaev (Inactive) added a comment - 2016-11-17 17:19

The fix for this bug was pushed into the 10.2 tree.

Igor Babaev (Inactive) added a comment - 2016-11-17 17:19 The fix for this bug was pushed into the 10.2 tree.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-10-21 22:01

Updated:: 2016-11-17 17:19

Resolved:: 2016-11-17 17:19

Git Integration

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

MariaDB Server