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

LP:1029835 - Incorect results in union & subqueries

Details

    • Bug
    • Status: Closed (View Workflow)
    • Resolution: Not a Bug
    • None
    • None
    • None

    Description

      The following query on the attached table & data yields different results in MySQL 5.5 & MariaDB 5.5:

      SELECT id, date_i, date_ontop, date_until
      FROM
      (
      SELECT id, date_i, date_ontop, date_until
      FROM news
      WHERE
      date_i <= CURRENT_TIMESTAMP
      AND
      date_ontop >= CURRENT_TIMESTAMP
      ORDER BY date_i DESC
      ) AS ontop

      UNION

      SELECT id, date_i, date_ontop, date_until
      FROM
      (
      SELECT id, date_i, date_ontop, date_until
      FROM news
      WHERE
      date_i <= CURRENT_TIMESTAMP
      AND
      ( date_until >= CURRENT_TIMESTAMP OR date_until IS NULL)
      AND
      ( date_ontop IS NULL OR date_ontop < CURRENT_TIMESTAMP )
      ORDER BY date_i DESC
      ) AS therest
      LIMIT 0, 18

      In MariaDB 5.2 it gives the same results as MySQL 5.5.

      It may be related to Bug#1010116 because if I switch optimizer_switch='derived_merge=off' it give the correct results.

      Attachments

        Activity

          Transition Time In Source Status Execution Times
          Rasmus Johansson (Inactive) made transition -
          Closed Reopened
          69d 2h 9m 1
          Rasmus Johansson (Inactive) made transition -
          Reopened Closed
          1h 17m 1

          People

            Unassigned Unassigned
            gabrielpreda Gabriel PREDA (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.