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

Coalesce returning incorrect results in MariaDB Community Edition 10.3.29

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.3.29
    • Fix Version/s: N/A
    • Component/s: N/A
    • Labels:
      None
    • Environment:
      CentOS

      Description

      As per CS0342409, the customer reported receiving different result sets for the same data and query on two different MariaDB Community Edition versions. The two versions are 10.3.28 and 10.3.29.

      I have been able to recreate this on two CentOS7 VMs, with one configured as 10.3.28 and the other as 10.3.29. The cls database tables were created and populated with the attached sql file on this Jira ticket. The sql statement that generated the two different result sets is attached to this ticket too. It appears to be an issue with the coalesce function on MariaDB Community 10.3.29.

      Below are the steps I followed on both DB version nodes:
      ---------------------------------------------------------------------------------------------------------------------------------------------
      MariaDB Community Edition 10.3.28

      [root@mariadb-10-3-28 MariaDB-CS]# rpm -qa | grep -E 'Maria|galera'
      MariaDB-backup-10.3.28-1.el7.centos.x86_64
      galera-25.3.32-1.el7.centos.x86_64
      MariaDB-compat-10.3.28-1.el7.centos.x86_64
      MariaDB-server-10.3.28-1.el7.centos.x86_64
      MariaDB-common-10.3.28-1.el7.centos.x86_64
      MariaDB-devel-10.3.28-1.el7.centos.x86_64
      MariaDB-client-10.3.28-1.el7.centos.x86_64
      MariaDB-shared-10.3.28-1.el7.centos.x86_64
      

      MariaDB [(none)]> create schema cls;
      Query OK, 1 row affected (0.001 sec)
      

      [root@mariadb-10-3-28 MariaDB-CS]# mysql -u root -p cls < /host/cls-input.sql 
      Enter password:
      

      MariaDB [cls]> select featurecode, coalesce(l.total, 0) as total, coalesce(r.used, 0) as used from cls.features left join (select featurecode, sum(total) as total from cls.licenses where state = 'active' group by featurecode) l using (featurecode) left join (select featurecode, sum(capacity) as used from cls.reservations group by featurecode) r using (featurecode) where featurecode=309;
      +-------------+-------+------+
      | featurecode | total | used |
      +-------------+-------+------+
      |         309 |     2 |    0 |
      +-------------+-------+------+
      1 row in set (0.001 sec)
       
      MariaDB [cls]> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.3.28-MariaDB |
      +-----------------+
      1 row in set (0.000 sec)
      

      ---------------------------------------------------------------------------------------------------------------------------------------------
      MariaDB Community Edition 10.3.29

      [root@mariadb-10-3-29 MariaDB-CS]# rpm -qa | grep -E 'Maria|galera'
      MariaDB-backup-10.3.29-1.el7.centos.x86_64
      galera-25.3.33-1.el7.centos.x86_64
      MariaDB-compat-10.3.29-1.el7.centos.x86_64
      MariaDB-server-10.3.29-1.el7.centos.x86_64
      MariaDB-common-10.3.29-1.el7.centos.x86_64
      MariaDB-devel-10.3.29-1.el7.centos.x86_64
      MariaDB-client-10.3.29-1.el7.centos.x86_64
      MariaDB-shared-10.3.29-1.el7.centos.x86_64
      

      MariaDB [(none)]> create schema cls;
      Query OK, 1 row affected (0.001 sec)
      

      [root@mariadb-10-3-29 MariaDB-CS]# mysql -u root -p cls < /host/cls-input.sql 
      Enter password: 
      

      MariaDB [cls]> select featurecode, coalesce(l.total, 0) as total, coalesce(r.used, 0) as used from cls.features left join (select featurecode, sum(total) as total from cls.licenses where state = 'active' group by featurecode) l using (featurecode) left join (select featurecode, sum(capacity) as used from cls.reservations group by featurecode) r using (featurecode) where featurecode=309;
      +-------------+-------+------+
      | featurecode | total | used |
      +-------------+-------+------+
      |         309 |     0 |    0 |
      +-------------+-------+------+
      1 row in set (0.002 sec)
       
      MariaDB [cls]> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.3.29-MariaDB |
      +-----------------+
      1 row in set (0.000 sec)
      

      ---------------------------------------------------------------------------------------------------------------------------------------------

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              scottsommerville Scott Sommerville
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration