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



    • 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


      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.


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

        Issue Links



              bar Alexander Barkov
              le_top Mario DE WEERD
              0 Vote for this issue
              5 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.