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

Correlated subquery with aggregate function returns empty resultset

    XMLWordPrintable

Details

    Description

      Queries with correlated subqueries containing aggregate function return empty results.

      Steps to reproduce:

      CREATE TABLE `SubqueryBug` (
        `id` int(11) DEFAULT NULL,
        `datetime` timestamp NULL DEFAULT NULL
      ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
       
      INSERT INTO SubqueryBug (id,`datetime`) VALUES
      	 (1,'2022-11-15 11:00:00.0'),
      	 (1,'2022-11-15 10:00:00.0'),
      	 (1,'2022-11-14 11:00:00.0'),
      	 (2,'2022-11-13 09:00:00.0'),
      	 (2,'2022-11-09 11:00:00.0');
       
      select * FROM SubqueryBug sb where sb.id =1 and sb.datetime in (select max(datetime) from SubqueryBug WHERE id=sb.id);
      

      Expected result:
      1 2022-11-15 11:00:00.000

      The query above returns empty result set while the ones below return correct result:

      Use scalar query instead:

      select * FROM SubqueryBug sb where sb.id =1 and sb.datetime = (select max(datetime) from SubqueryBug WHERE id=sb.id);
      

      Use constant as id instead of reference to parent table:

      select * FROM SubqueryBug sb where sb.id =1 and sb.datetime in (select max(datetime) from SubqueryBug WHERE id=1);
      

      Also casting sb.datetime to timestamp works ok:

      select * FROM SubqueryBug sb where sb.id =1 and timestamp(sb.datetime) in (select max(datetime) from SubqueryBug WHERE id=sb.id);
      

      The bug is not present in version 10.3. I experienced the bug after upgrading from 10.3 to 10.6, but I cannot confirm if it was also present in intermediate versions 10.4 and 10.5.

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              Papierski Bartek Papierski
              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.