[MDEV-26614] Crash in CTE for complex query Created: 2021-09-15  Updated: 2022-06-09  Resolved: 2022-05-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: None
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3

Type: Bug Priority: Major
Reporter: Michael Widenius Assignee: Sergei Petrunia
Resolution: Duplicate Votes: 0
Labels: None

Attachments: File window-crash-utf8.sql     File window-crash.sql    
Issue Links:
Relates
relates to MDEV-19398 Assertion `item1->type() == Item::FI... Closed

 Description   

One Libre.Chat, "Albright" got a crash on this query on MariaDB 10.3.31

WITH cte AS (
SELECT
DENSE_RANK() OVER (ORDER BY thread DESC) AS order_rank,
DENSE_RANK() OVER (PARTITION BY thread ORDER BY c.id ASC) AS thread_rank,
c.id,
c.parent,
IF(c.parent = 0, c.id, c.parent) AS thread,
c.user_id,
c.created,
CONVERT_TZ(c.created, @@session.time_zone, "+00:00") AS created_utc,
COALESCE(un.username, un.user_id) AS username,
COALESCE(un.username_slug, un.user_id) AS username_slug,
un.profile_pic,
IF(si.user_id IS NULL, 0, 1) AS is_staff,
IF(sn.sub_id IS NULL, 0, 1) AS is_subscriber,
– SUM() returns NULL instead of zero when there are no values
– (votes) to sum
COALESCE(SUM(cv.value), 0) AS score,
IF(c.parent = 0, COALESCE(SUM(cv.value), 0), -2147483648) AS thread_score,
– We have to put this DENSE_RANK() here. If we put it up top with the
– others, MariaDB 10.3 crashes. Maybe it's because we need to have it
– after the field it uses.
DENSE_RANK() OVER(ORDER BY thread_score DESC, thread DESC) AS score_rank,
COALESCE(c2.count, 0) AS user_comment_count,
COALESCE(c3.count, 0) AS reply_count,
cv2.value AS user_score,
br.html
FROM comment c
INNER JOIN users_new un ON c.user_id = un.user_id
INNER JOIN body_revision br USING (body_revision_id)
LEFT JOIN (
SELECT user_id, count AS count
FROM comment c2
WHERE c2.status = 1
GROUP BY user_id
) AS c2 ON c.user_id = c2.user_id
LEFT JOIN (
SELECT parent, count AS count
FROM comment c3
WHERE c3.status = 1
GROUP BY parent
) AS c3 ON c3.parent = c.id
LEFT JOIN staff_info si ON si.user_id = un.user_id
LEFT JOIN subscriptions_new sn ON c.user_id = sn.user_id AND sn.status = 1
LEFT JOIN comment_vote cv ON c.id = cv.id
LEFT JOIN comment_vote cv2 ON cv2.id = c.id AND cv2.user_id = '108509'
WHERE c.entity_type = 5
AND c.entity_id = '22415'
AND c.status = 1
GROUP BY c.id
ORDER BY created DESC
)
SELECT * FROM cte WHERE (order_rank <= 5 OR score_rank <= 5) AND thread_rank <= 6

Here is the stack trace:
0 libsystem_platform.dylib 0x000000018a4e9c44 _sigtramp + 56
0 mysqld 0x00000001024a2a94 ZL19compare_order_listsP10SQL_I_ListI8st_orderES2 + 260
0 mysqld 0x000000010249f944 _ZN24Window_funcs_computation5setupEP3THDP4ListI16Item_window_funcEP13st_join_table + 316
0 mysqld 0x00000001023a0afc _ZN4JOIN21make_aggr_tables_infoEv + 2948
0 mysqld 0x000000010239a374 _ZN4JOIN15optimize_stage2Ev + 10340
0 mysqld 0x000000010239b50c _ZN4JOIN14optimize_innerEv + 2044
0 mysqld 0x0000000102397acc _ZN4JOIN8optimizeEv + 76
0 mysqld 0x0000000102342b8c _Z22mysql_derived_optimizeP3THDP3LEXP10TABLE_LIST + 264
0 mysqld 0x00000001023437e8 _Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj + 168
0 mysqld 0x000000010239b4b4 _ZN4JOIN14optimize_innerEv + 1956
0 mysqld 0x0000000102397acc _ZN4JOIN8optimizeEv + 76
0 mysqld 0x00000001023949a4 _Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex + 100
0 mysqld 0x00000001023948d4 _Z13handle_selectP3THDP3LEXP13select_resultm + 332
0 mysqld 0x0000000102371fa8 _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 952
0 mysqld 0x000000010236c8d4 _Z21mysql_execute_commandP3THD + 1860
0 mysqld 0x000000010236a4e4 _Z11mysql_parseP3THDPcjP12Parser_statebb + 436
0 mysqld 0x00000001023679ac _Z16dispatch_command19enum_server_commandP3THDPcjbb + 2208
0 mysqld 0x00000001023690ec _Z10do_commandP3THD + 292
0 mysqld 0x00000001024332a8 _Z24do_handle_one_connectionP7CONNECT + 444
0 mysqld 0x00000001024330d8 handle_one_connection + 68
0 libsystem_pthread.dylib 0x000000018a49efd4 _pthread_start + 320
0 libsystem_pthread.dylib 0x000000018a499d3c thread_start + 8

 Comments   
Comment by Elena Stepanova [ 2021-09-15 ]

It looks similar to MDEV-19398

Comment by Michael Widenius [ 2021-09-15 ]
  1. Full test case (not tested, got it from the user)

CREATE DATABASE IF NOT EXISTS `window_crash`;
USE `window_crash`;

DROP TABLE IF EXISTS `comment_vote`;
DROP TABLE IF EXISTS `subscriptions_new`;
DROP TABLE IF EXISTS `staff_info`;
DROP TABLE IF EXISTS `staff_cats`;
DROP TABLE IF EXISTS `comment`;
DROP TABLE IF EXISTS `body_revision`;
DROP TABLE IF EXISTS `body_instance`;
DROP TABLE IF EXISTS `users_new`;

CREATE TABLE `users_new` (
`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`username_slug` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`real_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`register_date` timestamp NOT NULL DEFAULT current_timestamp(),
`register_ip` varbinary(16) DEFAULT NULL,
`ui_settings` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_login` timestamp NULL DEFAULT NULL,
`email_sub` tinyint(1) NOT NULL DEFAULT 1,
`country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`level` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = Basic, 2 = Subscriber, 8 = Staff, 9 = Admin',
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = Unverified, 1 = Normal, 9 = Deleted',
`use_themes` tinyint(1) unsigned NOT NULL DEFAULT 1,
`profile_pic` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`profile_pic_hover` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`staff_meta` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`data` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `username_slug` (`username_slug`),
KEY `email_sub` (`email_sub`),
KEY `level` (`level`)
) ENGINE=InnoDB AUTO_INCREMENT=112812 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `body_instance` (
`body_instance_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` tinyint(3) unsigned NOT NULL DEFAULT 0,
`filedir` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`data` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`body_instance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24245 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `body_revision` (
`body_revision_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`body_instance_id` int(11) unsigned NOT NULL,
`author` int(11) unsigned NOT NULL,
`time` datetime NOT NULL DEFAULT current_timestamp(),
`markdown` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`html` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`body_revision_id`),
KEY `body_instance_id` (`body_instance_id`),
CONSTRAINT `body_revision_ibfk_1` FOREIGN KEY (`body_instance_id`) REFERENCES `body_instance` (`body_instance_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=116347 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `comment` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent` int(11) unsigned NOT NULL DEFAULT 0,
`status` tinyint(1) NOT NULL DEFAULT 1,
`entity_type` tinyint(3) unsigned NOT NULL,
`entity_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`body_instance_id` int(11) unsigned NOT NULL,
`body_revision_id` int(11) unsigned NOT NULL,
`created` timestamp NOT NULL DEFAULT current_timestamp(),
`updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
KEY `type_id` (`entity_type`,`entity_id`),
KEY `user_id` (`user_id`),
KEY `body_instance_id` (`body_instance_id`),
KEY `body_revision_id` (`body_revision_id`),
CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_new` (`user_id`) ON DELETE CASCADE,
CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`body_instance_id`) REFERENCES `body_instance` (`body_instance_id`) ON DELETE CASCADE,
CONSTRAINT `comment_ibfk_3` FOREIGN KEY (`body_revision_id`) REFERENCES `body_revision` (`body_revision_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_cats` (
`category_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`sort` tinyint(3) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`color` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`category_id`),
KEY `sort` (`sort`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_info` (
`user_id` int(11) unsigned NOT NULL,
`category_id` tinyint(3) unsigned NOT NULL,
`image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`image_hover` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`small_image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`small_image_hover` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`last_np_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`steam_id` varchar(31) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`last_steam_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`steam_recently` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
KEY `user_id` (`user_id`),
KEY `category_id` (`category_id`),
CONSTRAINT `staff_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_new` (`user_id`) ON DELETE CASCADE,
CONSTRAINT `staff_info_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `staff_cats` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscriptions_new` (
`sub_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`customer_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`subscription_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`type` int(11) NOT NULL,
`ip` varbinary(16) DEFAULT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
`first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
`last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
`country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`cancel_on` int(11) NOT NULL DEFAULT 0,
`created` timestamp NOT NULL DEFAULT current_timestamp(),
`modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = Pending Verification, 1 = Active, 2 = Cancelled',
PRIMARY KEY (`sub_id`),
UNIQUE KEY `subscription_id` (`subscription_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `comment_vote` (
`id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`value` tinyint(1) NOT NULL,
`created` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`,`user_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `comment_vote_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_new` (`user_id`) ON DELETE CASCADE,
CONSTRAINT `comment_vote_ibfk_2` FOREIGN KEY (`id`) REFERENCES `comment` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

WITH cte AS (
SELECT
DENSE_RANK() OVER (ORDER BY thread DESC) AS order_rank,
DENSE_RANK() OVER (PARTITION BY thread ORDER BY c.id ASC) AS thread_rank,
c.id,
c.parent,
IF(c.parent = 0, c.id, c.parent) AS thread,
c.user_id,
c.created,
CONVERT_TZ(c.created, @@session.time_zone, "+00:00") AS created_utc,
COALESCE(un.username, un.user_id) AS username,
COALESCE(un.username_slug, un.user_id) AS username_slug,
un.profile_pic,
IF(si.user_id IS NULL, 0, 1) AS is_staff,
IF(sn.sub_id IS NULL, 0, 1) AS is_subscriber,
– SUM() returns NULL instead of zero when there are no values
– (votes) to sum
COALESCE(SUM(cv.value), 0) AS score,
IF(c.parent = 0, COALESCE(SUM(cv.value), 0), -2147483648) AS thread_score,
– We have to put this DENSE_RANK() here. If we put it up top with the
– others, MariaDB 10.3 crashes. Maybe it's because we need to have it
– after the field it uses.
DENSE_RANK() OVER(ORDER BY thread_score DESC, thread DESC) AS score_rank,
COALESCE(c2.count, 0) AS user_comment_count,
COALESCE(c3.count, 0) AS reply_count,
cv2.value AS user_score,
br.html
FROM comment c
INNER JOIN users_new un ON c.user_id = un.user_id
INNER JOIN body_revision br USING (body_revision_id)
LEFT JOIN (
SELECT user_id, count AS count
FROM comment c2
WHERE c2.status = 1
GROUP BY user_id
) AS c2 ON c.user_id = c2.user_id
LEFT JOIN (
SELECT parent, count AS count
FROM comment c3
WHERE c3.status = 1
GROUP BY parent
) AS c3 ON c3.parent = c.id
LEFT JOIN staff_info si ON si.user_id = un.user_id
LEFT JOIN subscriptions_new sn ON c.user_id = sn.user_id AND sn.status = 1
LEFT JOIN comment_vote cv ON c.id = cv.id
LEFT JOIN comment_vote cv2 ON cv2.id = c.id AND cv2.user_id = '108509'
WHERE c.entity_type = 5
AND c.entity_id = '22415'
AND c.status = 1
GROUP BY c.id
ORDER BY created DESC
)
SELECT * FROM cte WHERE (order_rank <= 5 OR score_rank <= 5) AND thread_rank <= 6;

Comment by Michael Widenius [ 2021-09-15 ]

Comment from the user:
I would add that this worked before I tried converting the encoding from utf8 to utf8mb4 because that is probably relevant.

Comment by Garrett Albright [ 2021-09-15 ]

Albright from IRC here to add a couple notes;

In the pastes above, Jira seems to be converting ( * ) (without spaces) to a star icon for some reason, so I've added the SQL as an attachment.

I also added the same SQL with utf8 encoding on the tables instead of utf8mb4. The utf8 one does not cause the crash, so that really does seem to be relevant here.

window-crash-utf8.sql window-crash.sql

Comment by Sergei Petrunia [ 2021-09-17 ]

Trying on 10.6, both window-crash.sql and window-crash-utf8.sql are crashing

The crash is here

  #0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
 
  #1  0x00007ffff56bb921 in __GI_abort () at abort.c:79
 
  #2  0x00007ffff56ab48a in __assert_fail_base (fmt=0x7ffff5832750 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x555556cbb4a8 "item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM", file=file@entry=0x555556cbb3f8 "/home/psergey/dev-git2/10.6-cp/sql/sql_window.cc", line=line@entry=434, function=function@entry=0x555556cbb8c0 <compare_order_elements(st_order*, st_order*)::__PRETTY_FUNCTION__> "int compare_order_elements(ORDER*, ORDER*)") at assert.c:92
 
  #3  0x00007ffff56ab502 in __GI___assert_fail (assertion=0x555556cbb4a8 "item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM", file=0x555556cbb3f8 "/home/psergey/dev-git2/10.6-cp/sql/sql_window.cc", line=434, function=0x555556cbb8c0 <compare_order_elements(st_order*, st_order*)::__PRETTY_FUNCTION__> "int compare_order_elements(ORDER*, ORDER*)") at assert.c:101
 
  #4  0x0000555556113379 in compare_order_elements (ord1=0x7fff24015588, ord2=0x7fff24018e18) at /home/psergey/dev-git2/10.6-cp/sql/sql_window.cc:433
 
  #5  0x0000555556113537 in compare_order_lists (part_list1=0x7fff240155f8, part_list2=0x7fff24019000) at /home/psergey/dev-git2/10.6-cp/sql/sql_window.cc:474
 
  #6  0x000055555611397e in compare_window_funcs_by_window_specs (win_func1=0x7fff24015668, win_func2=0x7fff24019070, arg=0x0) at /home/psergey/dev-git2/10.6-cp/sql/sql_window.cc:605
 
  #7  0x0000555556119cf1 in bubble_sort<Item_window_func> (list_to_sort=0x7fff24015308, sort_func=0x55555611386b <compare_window_funcs_by_window_specs(Item_window_func*, Item_window_func*, void*)>, arg=0x0) at /home/psergey/dev-git2/10.6-cp/sql/sql_list.h:655
 
  #8  0x0000555556113b48 in order_window_funcs_by_window_specs (win_func_list=0x7fff24015308) at /home/psergey/dev-git2/10.6-cp/sql/sql_window.cc:678
 
  #9  0x0000555556116005 in Window_funcs_computation::setup (this=0x7fff2412de80, thd=0x7fff24000d78, window_funcs=0x7fff24015308, tab=0x7fff2411d5f8) at /home/psergey/dev-git2/10.6-cp/sql/sql_window.cc:3099

it fails this assertion:

  DBUG_ASSERT(item1->type() == Item::FIELD_ITEM &&
 
              item2->type() == Item::FIELD_ITEM);

Comment by Sergei Petrunia [ 2021-09-17 ]

it fails it because

(gdb) p item1
 
  $13 = (Item_temptable_field *) 0x7fff2412cf00
 
(gdb) p item2
 
  $15 = (Item_func_if *) 0x7fff24018ac0

Item_temptable_field is a kind of Item_field, Item_func_if is not.

The optimizer is trying to compare ORDER BY clauses of these two window functions:

(gdb) p dbug_print_item(win_func1)
 
  $22 = 0x555557a43ca0 <dbug_item_print_buf> 
"dense_rank() over ( order by thread desc)"
 
 
 
(gdb) p dbug_print_item(win_func2)
 
  $23 = 0x555557a43ca0 <dbug_item_print_buf> 
  "dense_rank() over ( order by if(tmp_field,coalesce(tmp_field,0),-2147483648) desc,thread desc)"

The second one comes from here:

  IF(c.parent = 0, COALESCE(SUM(cv.value), 0), -2147483648) AS thread_score,
 
 
 
  DENSE_RANK() OVER(ORDER BY thread_score DESC, thread DESC) AS score_rank,

Comment by Sergei Petrunia [ 2021-09-17 ]

Simplifying the query: this smaller query still crashes in the same way:

SELECT
 
  DENSE_RANK() OVER (ORDER BY thread DESC) AS order_rank,
 
 
 
  IF(c.parent = 0, c.id, c.parent) AS thread,
 
  IF(c.parent = 0, COALESCE(SUM(cv.value), 0), -2147483648) AS thread_score,
 
  -- We have to put this DENSE_RANK() here. If we put it up top with the
 
  -- others, MariaDB 10.3 crashes. Maybe it's because we need to have it
 
  -- after the field it uses.
 
  DENSE_RANK() OVER(ORDER BY thread_score DESC, thread DESC) AS score_rank
 
FROM 
  comment c LEFT JOIN comment_vote cv ON c.id = cv.id
 
WHERE 
  c.entity_type = 5
 
  AND c.entity_id = '22415'
 
  AND c.status = 1
 
GROUP BY c.id;

Comment by Sergei Petrunia [ 2021-09-17 ]

Running this query creates GROUP-BY temporary table with this call:

create_tmp_table(
 
  fields= List<Item>{
 
     "c.`id`"
 
     "sum(cv.`value`)"
 
     "c.parent = 0"
 
     "dense_rank() over ( order by if(c.parent = 0,c.`id`,c.parent) desc)"
 
     "if(c.parent = 0,c.`id`,c.parent)"
 
     "if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648)"
 
     "dense_rank() over ( order by if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648) desc)"
 
  }
 
  group = { "c.`id`" }
 
  ...
 
)

Note this Item is in the list of fields:

if(c.parent = 0,coalesce(sum(cv.`value`),0),-2147483648)"

... but the window function's ORDER BY clause is apparently not using it?

Comment by Sergei Petrunia [ 2021-09-17 ]

Comparing ORDER BY references for both window functions:

(gdb) p ord1->item
 
  $100 = (Item **) 0x7fff1c07c1f8
 
(gdb) p ord2->item
 
  $102 = (Item **) 0x7fff1c07c200
 
(gdb) p ord2->item - ord1->item
 
  $104 = 1

They both refer to the "ref_pointer_array". The elements of that array should be switched to point to temp.table fields... Which was done for one window function but not for the other...

The switch is done in change_to_use_tmp_fields (Thanks sanja_byelkin) ...

The lines that prevent the switch are:

    if ((item->with_sum_func() && item->type() != Item::SUM_FUNC_ITEM) ||
 
        item->with_window_func())

makes sense...

A guess: is this really correct that this query is using one temp. table and not two?

Comment by Sergei Petrunia [ 2021-09-17 ]

... Well, a "naive" attempt to get this run with two temp tables wasn't successful.

The other route is: why does window function's ORDER BY criteria need to be a temp table Field (and not a function of it)?

The expression (quoting from above): 

(gdb) p dbug_print_item(win_func2)
 
  $23 = 0x555557a43ca0 <dbug_item_print_buf> 
  "dense_rank() over ( order by if(tmp_field,coalesce(tmp_field,0),-2147483648) desc,thread desc)"

can be computed at window function computation phase...

Comment by Sergei Petrunia [ 2021-09-17 ]

Idea of the fix

diff --git a/sql/sql_window.cc b/sql/sql_window.cc
 
index 17920519b41..2d26dd13fd4 100644
 
--- a/sql/sql_window.cc
 
+++ b/sql/sql_window.cc
 
@@ -430,10 +430,7 @@ int compare_order_elements(ORDER *ord1, ORDER *ord2)
 
     return CMP_EQ;
 
   Item *item1= (*ord1->item)->real_item();
 
   Item *item2= (*ord2->item)->real_item();
 
-  DBUG_ASSERT(item1->type() == Item::FIELD_ITEM &&
 
-              item2->type() == Item::FIELD_ITEM); 
-  int cmp= ((Item_field *) item1)->field->field_index -
 
-           ((Item_field *) item2)->field->field_index;
 
+  int cmp= (char*)item1 - (char*) item2;
 
   if (cmp == 0)
 
   {
 
     if (ord1->direction == ord2->direction)

needs to be polished.

Comment by Sergei Petrunia [ 2022-05-04 ]

Closed by fix for MDEV-19398

Generated at Thu Feb 08 09:46:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.