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

LP:1010116 - Incorrect query results in subqueries

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      I recently attempted to upgrade to MariaDB 5.5.24 from MySQL 5.1.61, especially to take advantage of Sub-query optimizations. We are developing with the MSFT Entity framework which tends to create a lot of subqueries in its generated sql. Most simple queries have run fine, but I found a scenario that does not work correctly in Maria 5.5.23-24, but works correctly on MySQL 5.1.61:

      SELECT
      `Derived1`.`id`,
      `Derived2`.`Val1` = 1
      FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
      2 as `id`,
      1 AS `Val1`
      FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`
      WHERE `Derived1`.`id` = 30631 LIMIT 2;

      I removed any actual table references, so you can run this query on any system, and the result is still wrong. The "1 AS `Val1`" is the same as the original query, all other values could be from actual tables.

      MariaDB Returns: 30631,1
      MySQL Returns: 30631, NULL

      A similar query works correctly on both MariaDb and MySql:

      SELECT Derived1.Clientid, Val1
      FROM
      (SELECT 1234 AS Clientid) Derived1
      right outer JOIN (SELECT 123 AS Clientid, 999 AS Val1) Derived2
      ON Derived1.Clientid = Derived2.ClientID
      WHERE
      Derived2.Clientid = 123;

      Returns: Null, 999

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            jaredlucas Jared Lucas (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.