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

Wrong result for a simple join over a materialized view with a blob column

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.0.1, 5.5.29, 5.3.12
        • Fix Version/s: 10.0.2, 5.5.30, 5.3.13
        • Component/s: None
        • Labels:
          None

          Description

          The following test case returns a wrong result in MariaDB 5.3/5.5:

          CREATE TABLE t1 (c1 text, c2 int);
INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3);
CREATE TABLE t2 (c1 text, c2 int);
INSERT INTO t2 VALUES ('b',2), ('c',3);
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2;

          The last query from the test case returns:

          MariaDB [test]> SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2;
Empty set (0.00 sec)

          The correct result is:

          MariaDB [test]> SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2;
+------+------+
| c1   | c2   |
+------+------+
| c    |    3 |
| c    |    3 |
+------+------+
2 rows in set (0.01 sec)

          This result can be obtained if
          SET optimizer_switch = 'derived_with_keys=off';

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                igor Igor Babaev added a comment -

                The fix for this bug was applied to the 5.3 tree in rev 3631 on 2013-02-28.

                Show
                igor Igor Babaev added a comment - The fix for this bug was applied to the 5.3 tree in rev 3631 on 2013-02-28.

                  People

                  • Assignee:
                    igor Igor Babaev
                    Reporter:
                    igor Igor Babaev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    1 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: