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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.27, 10.1.42, 10.3.18, 10.4.8
    • 10.1.44, 10.2.31, 10.3.22, 10.4.12, 10.5.1
    • Temporal Types

    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

        Issue Links

          Activity

            People

              bar Alexander Barkov
              le_top Mario DE WEERD
              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.