[MDEV-31073] Server crash, assertion `table != 0 && view->field_translation != 0' failure with ROWNUM and view Created: 2023-04-18  Updated: 2023-04-28  Resolved: 2023-04-28

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.11.3, 11.0.2, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Relates
relates to MDEV-31143 Crash for query using ROWNUM() over v... Closed

 Description    

CREATE TABLE t (f INT);
 
INSERT INTO t VALUES (1),(2);
 
CREATE VIEW v AS SELECT * FROM t;
 
UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1;
 
 
 
# Cleanup
 
DROP VIEW v;
 
DROP TABLE t;

Reproducible with MyISAM, Aria, InnoDB on 10.6+, debug and non-debug as shown below.
The test case is not applicable to earlier versions due to the use of ROWNUM which I couldn't get rid of.

The failure started happening on 10.6 after this merge:

commit 5bada1246de48ef4a18fa30388f06719c971c3d7
 
Merge: 375991a531f fb72dfbf7fb
 
Author: Marko Mäkelä
 
Date:   Tue Apr 11 16:15:19 2023 +0300
 
 
 
    Merge 10.5 into 10.6

I couldn't isolate the exact guilty commit from it (couldn't built with them rolled back individually), but it's a very small merge, there can't be many suspects.

10.6 non-debug 8f87023d

 
#2  <signal handler called>
 
#3  heap_info (info=0x0, x=0x7f51ce58da80, flag=18) at /data/src/10.6/storage/heap/hp_info.c:34
 
#4  0x00005599f734a7d4 in ha_heap::info (this=0x7f51bc19bf60, flag=18) at /data/src/10.6/storage/heap/ha_heap.cc:370
 
#5  0x00005599f6f58d5c in mysql_update (thd=thd@entry=0x7f51bc000c68, table_list=<optimized out>, fields=..., values=..., conds=<optimized out>, order_num=<optimized out>, order=<optimized out>, limit=1, ignore=<optimized out>, found_return=<optimized out>, updated_return=<optimized out>) at /data/src/10.6/sql/sql_update.cc:572
 
#6  0x00005599f6e99cc2 in mysql_execute_command (thd=0x7f51bc000c68, is_called_from_prepared_stmt=<optimized out>) at /data/src/10.6/sql/sql_parse.cc:4413
 
#7  0x00005599f6e9c725 in mysql_parse (thd=0x7f51bc000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /data/src/10.6/sql/sql_parse.cc:8036
 
#8  0x00005599f6e9ea05 in dispatch_command (command=COM_QUERY, thd=0x7f51bc000c68, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /data/src/10.6/sql/sql_parse.cc:1993
 
#9  0x00005599f6e9fcf7 in do_command (thd=0x7f51bc000c68, blocking=blocking@entry=true) at /data/src/10.6/sql/sql_parse.cc:1409
 
#10 0x00005599f6f9be27 in do_handle_one_connection (connect=<optimized out>, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
 
#11 0x00005599f6f9c1bd in handle_one_connection (arg=arg@entry=0x5599f8fd8878) at /data/src/10.6/sql/sql_connect.cc:1318
 
#12 0x00005599f72c9e57 in pfs_spawn_thread (arg=0x5599f8f92c68) at /data/src/10.6/storage/perfschema/pfs.cc:2201
 
#13 0x00007f51d3aa7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#14 0x00007f51d3b2866c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

10.6 debug 8f87023d

 
mariadbd: /data/src/10.6/sql/sql_view.cc:2099: bool check_key_in_view(THD*, TABLE_LIST*): Assertion `table != 0 && view->field_translation != 0' failed.
 
230418 11:57:37 [ERROR] mysqld got signal 6 ;
 
 
 
#9  0x00007ff9e0653df2 in __GI___assert_fail (assertion=0x56499b8a8df8 "table != 0 && view->field_translation != 0", file=0x56499b8a8528 "/data/src/10.6/sql/sql_view.cc", line=2099, function=0x56499b8a8e28 "bool check_key_in_view(THD*, TABLE_LIST*)") at ./assert/assert.c:101
 
#10 0x000056499abc76b9 in check_key_in_view (thd=0x7ff9c0000dc8, view=0x7ff9c0015760) at /data/src/10.6/sql/sql_view.cc:2099
 
#11 0x000056499abb7428 in mysql_update (thd=0x7ff9c0000dc8, table_list=0x7ff9c0015760, fields=..., values=..., conds=0x7ff9c0016188, order_num=0, order=0x0, limit=1, ignore=false, found_return=0x7ff9dc15fe00, updated_return=0x7ff9dc15fef0) at /data/src/10.6/sql/sql_update.cc:506
 
#12 0x000056499aa71edd in mysql_execute_command (thd=0x7ff9c0000dc8, is_called_from_prepared_stmt=false) at /data/src/10.6/sql/sql_parse.cc:4413
 
#13 0x000056499aa7e5e8 in mysql_parse (thd=0x7ff9c0000dc8, rawbuf=0x7ff9c0015660 "UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1", length=47, parser_state=0x7ff9dc160380) at /data/src/10.6/sql/sql_parse.cc:8036
 
#14 0x000056499aa6a0c9 in dispatch_command (command=COM_QUERY, thd=0x7ff9c0000dc8, packet=0x7ff9c000b8f9 "UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1", packet_length=47, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1896
 
#15 0x000056499aa68a22 in do_command (thd=0x7ff9c0000dc8, blocking=true) at /data/src/10.6/sql/sql_parse.cc:1409
 
#16 0x000056499ac39e94 in do_handle_one_connection (connect=0x56499e51ddc8, put_in_cache=true) at /data/src/10.6/sql/sql_connect.cc:1416
 
#17 0x000056499ac39c09 in handle_one_connection (arg=0x56499e4f6468) at /data/src/10.6/sql/sql_connect.cc:1318
 
#18 0x000056499b1908ee in pfs_spawn_thread (arg=0x56499e4dd5b8) at /data/src/10.6/storage/perfschema/pfs.cc:2201
 
#19 0x00007ff9e06a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#20 0x00007ff9e072866c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81



 Comments   
Comment by Elena Stepanova [ 2023-04-18 ]

Apparently can also represent as a crash in ha_maria::info or ha_maria::extra.

Comment by Elena Stepanova [ 2023-04-18 ]

Another assertion which belongs here:

10.11

 
mysqld: /home/vsts/src/sql/sql_insert.cc:1456: bool check_view_insertability(THD*, TABLE_LIST*): Assertion `view->table != 0 && view->field_translation != 0' failed.

Comment by Elena Stepanova [ 2023-04-18 ]

I won't be adding any more stack traces, they seem to be all over the place. Until this bug is fixed, crashes involving ROWNUM will be assumed to be related to it.

Comment by Oleksandr Byelkin [ 2023-04-20 ]

It looks like incorrect merge by above mentioned merge of this ccec9b1de95a66b7597bc30e0a60bd61866f225d

     /* A subquery might be forced to be materialized due to a side-effect. */
 
-    if (!is_materialized_derived() && first_select->is_mergeable() &&
 
-        optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_MERGE) &&
 
+    if (!is_materialized_derived() && unit->can_be_merged() &&
 
+        (optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_MERGE) || is_view()) &&
 
         !thd->lex->can_not_use_merged() &&
 
-        !(thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
 
-          thd->lex->sql_command == SQLCOM_DELETE_MULTI) &&
 
+        !((thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
 
+           thd->lex->sql_command == SQLCOM_DELETE_MULTI) && !is_view()) &&
 
         !is_recursive_with_table())

(at least it is suspiciouse)

Comment by Oleksandr Byelkin [ 2023-04-20 ]

So, here matherialisation decided due to rownum() presence and later no one expected to update "matherialized" view.

Also the question is how rownum() supposed to work over a view in update...

Comment by Oleksandr Byelkin [ 2023-04-21 ]

ccec9b1de95a66b7597bc30e0a60bd61866f225d reverted condition that is why the condition now aplicable to views.

@@ -9233,15 +9235,15 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
 
     set_derived();
 
   }
 
 
-  if (!is_view() &&
 
+  if (is_view() ||
 
       !derived_table_optimization_done(this))
 
   {
 
     /* A subquery might be forced to be materialized due to a side-effect. */

questions are:

1) why it was inverted not removed if we make it the same for derived and views

2) now call of thd->lex->can_not_use_merged(1) with parameter true probably is nt correct

Comment by Oleksandr Byelkin [ 2023-04-26 ]

the problem was too relaxed condition for special case of SELECT * FROM (<limited select>) WHERE rownum() <=nnn

Comment by Oleksandr Byelkin [ 2023-04-26 ] 

commit 521cc7ad137333586620a3102d94072b4abe75f1 (HEAD -> bb-10.6-MDEV-31073, origin/bb-10.6-MDEV-31073)
 
Author: Oleksandr Byelkin <sanja@mariadb.com>
 
Date:   Fri Apr 21 10:55:14 2023 +0200
 
 
 
    MDEV-31073 Server crash, assertion `table != 0 && view->field_translation != 0' failure with ROWNUM and view
 
    
    Now the same rule applied to vews and derived tables so we should
 
    allow merge of views (and derived) in queries with rownum (because
 
    it do not change results, only makes query plans better)

Comment by Igor Babaev [ 2023-04-28 ]

Ok to push after replacing the method SELECT::LEX::is_query_topmost_select() with the method 

bool SELECT::LEX::is_query_topmost(THD *thd) { get_master() == &thd->lex->unit }

and fixing the comment for the patch.

Generated at Thu Feb 08 10:21:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.