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

COUNT(*) returns 1, actual SELECT returns no result in 10.3.21, but 1 result in 10.1.41

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.42, 10.2.27, 10.3.18, 10.4.8
    • Fix Version/s: 10.1.44, 10.2.31, 10.3.22, 10.4.12, 10.5.1
    • Component/s: Temporal Types
    • Labels:
    • Environment:

      Description

      The select query works as expected on 10.1.41-MariaDB-0+deb9u1 - Debian 9.9, but not on 10.3.21-MariaDB-1:10.3.21+maria~stretch-log - mariadb.org binary distribution .

      Next Request indicates that there is 1 result

      SELECT COUNT(*) FROM (SELECT *,UNIX_TIMESTAMP(time) as utcTime FROM `positions` `t` USE INDEX(dev_good_reason_time) WHERE ((`t`.`device_id`=54) AND (`t`.`log_reason`=0)) AND (`t`.`is_good_position`=1) GROUP BY `t`.`time` HAVING `t`.`time`=MAX(`t`.`time`)) sq;
      

      Actual request returns no result on 10.3.21

      SELECT *,UNIX_TIMESTAMP(time) as utcTime FROM `positions` `t` USE INDEX(dev_good_reason_time) WHERE ((`t`.`device_id`=54) AND (`t`.`log_reason`=0)) AND (`t`.`is_good_position`=1) GROUP BY `t`.`time` HAVING `t`.`time`=MAX(`t`.`time`) ORDER BY time DESC LIMIT 1;
      

      The above query returns record number 30031 on mariadb 10.1.41

      This was verified using the attached data imported in a new database after exporting the sample from the original database and anomyzing the data. The unexpected behavior occurs on the original database and both test databases running in the checked systems.

      This is blocking (to use 10.3) - I downgraded the setup to 10.1 already used in production to be able to put this new instance in production. After downgrading to 10.1 the functionnality relying on this query works fine.

        Attachments

        1. mysql_cnf.mdev-21319.tar.gz
          6 kB
          Mario DE WEERD
        2. test_select.sql.gz
          608 kB
          Mario DE WEERD

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              le_top Mario DE WEERD
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: