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

ORDER BY is incorrect with window functions inside IF

    Details

      Description

      wrong order while using Window functions (RANK(), ROW_NUMBER) inside an IF, ordered also by joined table and st_distance function.

      testcase:
      {{
      CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `cat` int(11) NOT NULL DEFAULT 0,
      `cat_order` int(11) NOT NULL DEFAULT 0,
      `distance` int(11) NOT NULL DEFAULT 0,
      `title` varchar(50) NOT NULL DEFAULT '',
      `address` int(11) NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`),
      KEY `dist` (`distance`),
      KEY `cat` (`cat`)
      ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

      CREATE TABLE `addresses` (
      `a_id` int(11) NOT NULL,
      `a_type` tinyint(3) unsigned NOT NULL,
      `a_address` varchar(90) NOT NULL,
      `a_x` double NOT NULL,
      `a_y` double NOT NULL,
      `a_visible` tinyint(1) NOT NULL DEFAULT 1,
      `a_status` tinyint(3) unsigned NOT NULL DEFAULT 0,
      `a_city` int(11) NOT NULL DEFAULT 0,
      /* `a_point` point NOT NULL,*/
      PRIMARY KEY (`a_id`),
      UNIQUE KEY `type_address2` (`a_address`,`a_city`,`a_type`),
      KEY `xy` (`a_x`,`a_y`),
      KEY `id_x_y` (`a_id`,`a_x`,`a_y`),
      KEY `city_w_address` (`a_city`,`a_address`,`a_x`,`a_y`),
      /SPATIAL KEY `sa_point` (`a_point`),/
      KEY `point` (`a_point`(25))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      insert into test
      values ('1', '1', '2', '191', 'cat', '57316'),(
      '2', '1', '1', '90', 'dog', '57317'),(
      '3', '2', '1', '5', 'chair', '57318'),(
      '4', '2', '2', '2', 'desk', '57319');

      INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57316,1,'רח\' הציונות 78 ,אריאל',32.105,35.19709,1,1,110);
      INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57317,1,'דרך בר יהודה 53, נשר, ישראל',32.77686,35.04083,1,1,1651);
      INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57318,1,'העמל 37 פתח תקווה',32.096686,34.848931,1,1,1792);
      INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57319,1,'תרצה 19 רמת גן',32.071911,34.829069,1,1,1893);

      SET @hereami = POINT(35.22245,31.94236); /*בית אל */

      /works/

      select row_number() over (partition by cat) c, round( ST_Distance(@hereami, point( a_y,a_x) ) * 111195) distance, test.*
      from test, addresses
      where a_id=address
      order by c desc, distance asc

      /*wrong order */

      select if( row_number() over (partition by cat) =1,1,0) c, round( ST_Distance(@hereami, point( a_y,a_x) ) * 111195) distance, test.*
      from test, addresses
      where a_id=address
      order by c desc, distance asc

      ;}}

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                alice Alice Sherepa
                Reporter:
                Moshe L Moshe L
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: