Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
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
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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
the problem was too relaxed condition for special case of SELECT * FROM (<limited select>) WHERE rownum() <=nnn
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)
|
|
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.
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...