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

UNION query returns incorrect rows outside conditional evaluation

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.1.67, 5.2.14, 5.3.12, 10.1.13, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 5.5.54, 10.0.29, 10.1.21, 10.2.3
    • Optimizer
    • None
    • 5.5.50, 5.5.54

    Description

      Please consider the following scenario:

      drop table if exists union_bug;
      create table union_bug (d datetime not null primary key);
      insert into union_bug(d) values ('2016-06-01'),('2016-06-02'),('2016-06-03'),('2016-06-04');
      select * from
      (
      	select * from union_bug where d between '2016-06-02' and '2016-06-05'
      	union
      	(select * from union_bug where d < '2016-06-05' order by d desc limit 1)
      ) onlyJun2toJun4
      order by d
      

      The above query should return 3 rows dated 02 through 04 June 2016. This can be born out by running the individual select statements.

      However in practice this query returns 4 rows incorrectly dated 01 through 04 June 2016. It should NEVER NEVER NEVER return a row for 01 June, but it does.

      I suspect this may be somehow related to the duplicate row from the lower query. If I change the 'union to a 'union all' then the query correctly returns the expected 4 rows, including the one duplicate row.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              AndyCooper Andy Cooper
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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