[MDEV-28615] Crash caused by multi-table UPDATE over derived with hanging CTE Created: 2022-05-19  Updated: 2023-12-15  Resolved: 2023-11-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.35, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Critical
Reporter: Shihao Wen Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: crash, fuzzer
Environment:

ubuntu 18.04

Attachments: HTML File 41_stack    
Issue Links:
Duplicate
is duplicated by MDEV-32429 Heap-Use-After-Free at /mariadb-11.3.... Closed

 Description   

poc:

CREATE TABLE v1269 ( v1270 VARCHAR ( 1 ) , v1271 INT , v1272 INT ) ;
 
 CREATE TABLE v1273 ( v1274 BOOLEAN NOT NULL , v1275 INT , v1276 INT ) ;
 
 INSERT INTO v1269 ( v1271 ) VALUES ( v1271 ) ;
 
 UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ( 17138038.000000 AND v1274 = 78 ) ) = 0 AND v1274 = -128 ) % 45 , ( 79 = 13 OR v1276 > 'x' ) FROM v1273 WHERE v1275 - v1276 ) AS v1277 NATURAL JOIN ( WITH v1279 AS ( SELECT v1276 FROM ( SELECT NOT v1276 <= 'x' , v1276 FROM v1273 GROUP BY v1274 ) AS v1278 ) SELECT DISTINCT v1270 , ( v1270 = 5 OR v1272 > 'x' ) FROM v1269 ) AS v1280 NATURAL JOIN v1269 WHERE v1270 = v1274 ) AS v1281 FROM v1273 ) AS v1282 NATURAL JOIN v1269 AS v1283 NATURAL JOIN v1273 ORDER BY v1271 ) AND v1270 = -1 FROM v1269 ) AND 'x' ) = 12 AND v1271 = 64 ) % 0 , ( v1271 = 37 OR v1270 > 'x' ) FROM v1269 WHERE v1271 = -1 AND ( v1271 = 85 OR v1270 = 0 OR v1270 = 45 ) ) AS v1284 NATURAL JOIN v1269 SET v1271 = -1 WHERE v1270 = 62 ;
 
 INSERT INTO v1273 ( v1275 ) VALUES ( ( ( SELECT ARRAY [ 16 , 255 , -1 ] ) ) [ 93 ] ) , ( 255 ) ;
 
 SELECT COUNT ( v1270 ) OVER v1285 , NTILE ( v1271 ) OVER v1285 FROM v1269 WINDOW v1285 AS ( PARTITION BY v1271 ORDER BY v1272 DESC ) ;

output:
mysqld: /sql/handler.cc:2853: int handler::ha_rnd_next(uchar*): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed.

The full error log is in the attachment.

 Comments   
Comment by Daniel Black [ 2022-05-19 ]

Using a 10.3 image from ~18hrs ago

testing with container

 
# vi /tmp/m/t.sql
 
# # include sql
 
# podman run --env MARIADB_DATABASE=test --env MARIADB_USER=test --env MARIADB_PASSWORD=test -e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1  -v /tmp/m:/docker-entrypoint-initdb.d/:z --rm  quay.io/mariadb-foundation/mariadb-devel:10.3

10.3-c9b5a05341d7342db5f369493ea200b5fb9db243

 
2022-05-19 06:55:18+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.3.35+maria~focal started.
 
2022-05-19 06:55:18+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
 
2022-05-19 06:55:18+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.3.35+maria~focal started.
 
2022-05-19 06:55:18+00:00 [Note] [Entrypoint]: Initializing database files
 
 
 
 
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
 
To do so, start the server, then issue the following command:
 
 
 
'/usr/bin/mysql_secure_installation'
 
 
 
which will also give you the option of removing the test
 
databases and anonymous user created by default.  This is
 
strongly recommended for production servers.
 
 
 
See the MariaDB Knowledgebase at http://mariadb.com/kb
 
 
 
Please report any problems at http://mariadb.org/jira
 
 
 
The latest information about MariaDB is available at http://mariadb.org/.
 
 
 
Consider joining MariaDB's strong and vibrant community:
 
https://mariadb.org/get-involved/
 
 
 
2022-05-19 06:55:21+00:00 [Note] [Entrypoint]: Database files initialized
 
2022-05-19 06:55:21+00:00 [Note] [Entrypoint]: Starting temporary server
 
2022-05-19 06:55:21+00:00 [Note] [Entrypoint]: Waiting for server startup
 
2022-05-19  6:55:21 0 [Note] mysqld (mysqld 10.3.35-MariaDB-1:10.3.35+maria~focal) starting as process 112 ...
 
2022-05-19  6:55:21 0 [Note] InnoDB: Using Linux native AIO
 
2022-05-19  6:55:21 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
2022-05-19  6:55:21 0 [Note] InnoDB: Uses event mutexes
 
2022-05-19  6:55:21 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2022-05-19  6:55:21 0 [Note] InnoDB: Number of pools: 1
 
2022-05-19  6:55:21 0 [Note] InnoDB: Using SSE2 crc32 instructions
 
2022-05-19  6:55:21 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
 
2022-05-19  6:55:21 0 [Note] InnoDB: Completed initialization of buffer pool
 
2022-05-19  6:55:21 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
2022-05-19  6:55:21 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
2022-05-19  6:55:21 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2022-05-19  6:55:21 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2022-05-19  6:55:21 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2022-05-19  6:55:21 0 [Note] InnoDB: 10.3.35 started; log sequence number 1625443; transaction id 20
 
2022-05-19  6:55:21 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2022-05-19  6:55:21 0 [Warning] 'user' entry 'root@def0c412bfec' ignored in --skip-name-resolve mode.
 
2022-05-19  6:55:21 0 [Warning] 'proxies_priv' entry '@% root@def0c412bfec' ignored in --skip-name-resolve mode.
 
2022-05-19  6:55:21 0 [Note] Reading of all Master_info entries succeeded
 
2022-05-19  6:55:21 0 [Note] Added new Master_info '' to hash table
 
2022-05-19  6:55:21 0 [Note] mysqld: ready for connections.
 
Version: '10.3.35-MariaDB-1:10.3.35+maria~focal'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  mariadb.org binary distribution
 
2022-05-19 06:55:22+00:00 [Note] [Entrypoint]: Temporary server started.
 
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
 
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
 
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
 
2022-05-19 06:55:22+00:00 [Note] [Entrypoint]: Securing system users (equivalent to running mysql_secure_installation)
 
2022-05-19 06:55:22+00:00 [Note] [Entrypoint]: Creating database test
 
2022-05-19 06:55:22+00:00 [Note] [Entrypoint]: Creating user test
 
2022-05-19 06:55:22+00:00 [Note] [Entrypoint]: Giving user test access to schema test
 
 
 
2022-05-19 06:55:22+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/m.sql
 
ERROR 4078 (HY000) at line 7: Illegal parameter data types row and int for operation '='
 
2022-05-19  6:55:22 14 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active
 
2022-05-19  6:55:22 14 [Warning] MariaDB is closing a connection that has an active InnoDB transaction.  0 row modifications will roll back.
 
 
 
$ podman run --rm  quay.io/mariadb-foundation/mariadb-devel:10.3 cat /manifest.txt
 
org.opencontainers.image.authors=MariaDB Foundation
 
 
 
org.opencontainers.image.documentation=https://hub.docker.com/_/mariadb
 
 
 
org.opencontainers.image.source=https://github.com/MariaDB/mariadb-docker/tree/8e5ec939a7d6bf203805987b055f1ac0b90fabfc/10.3
 
 
 
org.opencontainers.image.licenses=GPL-2.0
 
 
 
org.opencontainers.image.title=MariaDB Server 10.3 CI build
 
 
 
org.opencontainers.image.description=This is not a Release.
 
Build of the MariaDB Server from CI as of commit c9b5a05341d7342db5f369493ea200b5fb9db243
 
 
 
org.opencontainers.image.version=10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243
 
 
 
org.opencontainers.image.revision=c9b5a05341d7342db5f369493ea200b5fb9db243

So this is erroring on the UPDATE can I assume this is fixed?

Comment by Shihao Wen [ 2022-05-19 ]
我的标题

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.35-MariaDB-debug Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database test2;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use test2;
Database changed
MariaDB [test2]> CREATE TABLE v1269 ( v1270 VARCHAR ( 1 ) , v1271 INT , v1272 INT ) ;
Query OK, 0 rows affected (0.020 sec)

MariaDB [test2]> CREATE TABLE v1273 ( v1274 BOOLEAN NOT NULL , v1275 INT , v1276 INT ) ;
Query OK, 0 rows affected (0.019 sec)

MariaDB [test2]> INSERT INTO v1269 ( v1271 ) VALUES ( v1271 ) ;
Query OK, 1 row affected (0.007 sec)

MariaDB [test2]> UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ( 17138038.000000 AND v1274 = 78 ) ) = 0 AND v1274 = -128 ) % 45 , ( 79 = 13 OR v1276 > 'x' ) FROM v1273 WHERE v1275 - v1276 ) AS v1277 NATURAL JOIN ( WITH v1279 AS ( SELECT v1276 FROM ( SELECT NOT v1276 <= 'x' , v1276 FROM v1273 GROUP BY v1274 ) AS v1278 ) SELECT DISTINCT v1270 , ( v1270 = 5 OR v1272 > 'x' ) FROM v1269 ) AS v1280 NATURAL JOIN v1269 WHERE v1270 = v1274 ) AS v1281 FROM v1273 ) AS v1282 NATURAL JOIN v1269 AS v1283 NATURAL JOIN v1273 ORDER BY v1271 ) AN
D v1270 = -1 FROM v1269 ) AND 'x' ) = 12 AND v1271 = 64 ) % 0 , ( v1271 = 37 OR v1270 > 'x' ) FROM v1269 WHERE v1271 = -1 AND ( v1271 = 85 OR v1270 = 0 OR v1270 = 45 ) ) AS v1284 NATURAL JOIN v1269 SET v1271 = -1 WHERE v1270 = 62 ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
MariaDB [test2]>

I use the mariadb cloned from github at 2022.5.6，is the mariadb updated since then?

Comment by Daniel Black [ 2022-05-19 ]

I didn't test on a debug build.

Confirmed:

10.3-40d9dbb28f43708b498a4d62f61dc34fd87eb9b9-debug

 
(gdb) bt full
 
#0  0x00007f3cee2a260c in __pthread_kill_implementation () from /lib64/libc.so.6
 
No symbol table info available.
 
#1  0x00007f3cee255dc6 in raise () from /lib64/libc.so.6
 
No symbol table info available.
 
#2  0x00007f3cee228833 in abort () from /lib64/libc.so.6
 
No symbol table info available.
 
#3  0x00007f3cee22875b in __assert_fail_base.cold () from /lib64/libc.so.6
 
No symbol table info available.
 
#4  0x00007f3cee24ed16 in __assert_fail () from /lib64/libc.so.6
 
No symbol table info available.
 
#5  0x0000000000b2afe0 in handler::ha_rnd_next (this=0x7f3c80059438, buf=0x7f3c80058fe0 "\377") at /home/dan/repos/mariadb-server-10.3/sql/handler.cc:2852
 
        result = 0
 
        _db_stack_frame_ = {func = 0x15903ef "sub_select", file = 0x158e7fe "/home/dan/repos/mariadb-server-10.3/sql/sql_select.cc", level = 2147483668, line = -1, prev = 0x7f3cee4674e0}
 
#6  0x0000000000d04400 in rr_sequential (info=0x7f3c800c3f68) at /home/dan/repos/mariadb-server-10.3/sql/records.cc:485
 
        tmp = 0
 
#7  0x00000000006deda6 in READ_RECORD::read_record (this=0x7f3c800c3f68) at /home/dan/repos/mariadb-server-10.3/sql/records.h:70
 
No locals.
 
#8  0x00000000008125f4 in join_init_read_record (tab=0x7f3c800c3ea0) at /home/dan/repos/mariadb-server-10.3/sql/sql_select.cc:20827
 
No locals.
 
#9  0x000000000082f6ff in sub_select (join=0x7f3c8003ce90, join_tab=0x7f3c800c3ea0, end_of_records=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_select.cc:19882
 
        _db_stack_frame_ = {func = 0x15921bd "do_select", file = 0x158e7fe "/home/dan/repos/mariadb-server-10.3/sql/sql_select.cc", level = 2147483667, line = -1, prev = 0x7f3cee467630}
 
        error = 32572
 
        rc = NESTED_LOOP_OK
 
        info = 0x7f3c800c3f68
 
        skip_over = false
 
#10 0x00000000008163bd in do_select (join=0x7f3c8003ce90, procedure=0x0) at /home/dan/repos/mariadb-server-10.3/sql/sql_select.cc:19423
 
        join_tab = 0x7f3c800c3ea0
 
        rc = 0
 
        error = NESTED_LOOP_OK
 
        _db_stack_frame_ = {func = 0x158f2b2 "JOIN::exec_inner", file = 0x158e7fe "/home/dan/repos/mariadb-server-10.3/sql/sql_select.cc", level = 2147483666, line = -1, prev = 0x7f3cee4677d8}
 
#11 0x000000000081517b in JOIN::exec_inner (this=0x7f3c8003ce90) at /home/dan/repos/mariadb-server-10.3/sql/sql_select.cc:4151
 
        columns_list = 0x7f3c80012a80
 
        _db_stack_frame_ = {func = 0x16f6f75 "subselect_single_select_engine::exec", file = 0x16f56ca "/home/dan/repos/mariadb-server-10.3/sql/item_subselect.cc", level = 2147483665, line = -1, prev = 0x7f3cee467af0}
 
#12 0x00000000008142d7 in JOIN::exec (this=0x7f3c8003ce90) at /home/dan/repos/mariadb-server-10.3/sql/sql_select.cc:3945
 
No locals.
 
#13 0x0000000000c32c1e in subselect_single_select_engine::exec (this=0x7f3c80037c28) at /home/dan/repos/mariadb-server-10.3/sql/item_subselect.cc:4023
 
        changed_tabs = {0x7f3c80012a80, 0x10000, 0x18078b9, 0x0, 0x7f3cee467920, 0x1508b42 <DoTrace+130>, 0x0, 0x8000000080000cb0, 0x100010000, 0x7f3c80000cd0, 0x7f3cee467b70, 0x150914b <_db_return_+203>, 0x18078b9, 0xe00000000, 0x7f3cee467970, 0x7f3c80000cd0, 0xf00000000, 0x7f3cee467ba8, 0x100010000, 0x7f3c80000cd0, 0x7f3cee467bc0, 0x150914b <_db_return_+203>, 0x0, 0xeee4680c0, 0x7f3c8001dae0, 0x7f3c80000cd0, 0xfee4679d0, 0x7f3cee467bf8, 0x0, 0x10000, 0x18078b9, 0x0, 0x7f3cee467a00, 0x1508b42 <DoTrace+130>, 0x0, 0x0, 0x100010000, 0x0, 0x7f3cee467c50, 0x1509100 <_db_return_+128>, 0x0, 0xbee467b60, 0x195021c, 0x7f3c80000cd0, 0xcee467c20, 0x7f3c80012d98, 0x7f3cee4678f0, 0x7f3c80037430, 0x0, 0x14de3b2 <_my_thread_var+18>, 0x7f3cee467a90, 0x14deed7 <my_thread_var_dbug+39>, 0x7f3c00000000, 0x14de3b2 <_my_thread_var+18>, 0x7f3cee467ab0, 0x14deed7 <my_thread_var_dbug+39>, 0x7f3cee467ac0, 0x7f3c80000cb0, 0x7f3c80000b60, 0x7f3c80000cb0, 0x7f3cee467ae0}
 
        last_changed_tab = 0x7f3cee4678d0
 
        _db_stack_frame_ = {func = 0x16f592e "Item_subselect::exec", file = 0x16f56ca "/home/dan/repos/mariadb-server-10.3/sql/item_subselect.cc", level = 2147483664, line = -1, prev = 0x7f3cee467b98}
 
        save_where = 0x157c8bc "field list"
 
        save_select = 0x7f3c80011c50
 
#14 0x0000000000c217ec in Item_subselect::exec (this=0x7f3c80037aa0) at /home/dan/repos/mariadb-server-10.3/sql/item_subselect.cc:791
 
        org_engine = 0x7f3c80037c28
 
        _db_stack_frame_ = {func = 0x1578442 "setup_fields", file = 0x1576fc4 "/home/dan/repos/mariadb-server-10.3/sql/sql_base.cc", level = 2147483663, line = -1, prev = 0x7f3cee468900}
 
        res = 128
 
#15 0x0000000000c2402b in Item_singlerow_subselect::val_real (this=0x7f3c80037aa0) at /home/dan/repos/mariadb-server-10.3/sql/item_subselect.cc:1394
 
No locals.
 
#16 0x0000000000b7bd47 in Item_func_between::val_int_cmp_real (this=0x7f3c80037d00) at /home/dan/repos/mariadb-server-10.3/sql/item_cmpfunc.cc:2238
 
        value = 0
 
        a = 6.9118708109601364e-310
 
        b = 5.1270224666145962e-317
 
#17 0x00000000009dc8a9 in Type_handler_real_result::Item_func_between_val_int (this=0x1cb9780 <type_handler_double>, func=0x7f3c80037d00) at /home/dan/repos/mariadb-server-10.3/sql/sql_type.cc:3814
 
No locals.
 
#18 0x0000000000cfaf77 in Item_func_between::val_int (this=0x7f3c80037d00) at /home/dan/repos/mariadb-server-10.3/sql/item_cmpfunc.h:904
 
No locals.
 
#19 0x0000000000655e9b in Item::update_null_value (this=0x7f3c80037d00) at /home/dan/repos/mariadb-server-10.3/sql/item.h:1628
 
No locals.
 
#20 0x0000000000735190 in Item_func::is_null (this=0x7f3c80037d00) at /home/dan/repos/mariadb-server-10.3/sql/item_func.h:184
 
No locals.
 
#21 0x0000000000bfc9ad in Item_row::fix_fields (this=0x7f3c80037e90, thd=0x7f3c80000d90, ref=0x7f3c80038078) at /home/dan/repos/mariadb-server-10.3/sql/item_row.cc:59
 
        item = 0x7f3c80037d00
 
        arg = 0x7f3c80037f20
 
        arg_end = 0x7f3c80037f28
 
#22 0x0000000000664bd2 in Item::fix_fields_if_needed (this=0x7f3c80037e90, thd=0x7f3c80000d90, ref=0x7f3c80038078) at /home/dan/repos/mariadb-server-10.3/sql/item.h:829
 
No locals.
 
#23 0x0000000000bbdf9e in Item_func::fix_fields (this=0x7f3c80037fe8, thd=0x7f3c80000d90, ref=0x7f3c80038580) at /home/dan/repos/mariadb-server-10.3/sql/item_func.cc:352
 
        item = 0x19b2151
 
        arg = 0x7f3c80038078
 
        arg_end = 0x7f3c80038088
 
        buff = "`\204F\356<\177\000\000\262\343M\001\000\000\000\000ЁF\356<\177\000\000\327\356M\001\000\000\000\000`\204F\356<\177\000\000\000\000\001\000\000\000\000\000\274\231W\001", '\000' <repeats 13 times>, "\202F\356<\177\000\000B\213P\001", '\000' <repeats 12 times>, "\260\f\000\200\000\000\000\200\000\000\001\000\001\000\000\000\320\f\000\200<\177\000\000P\204F\356<\177\000\000K\221P\001", '\000' <repeats 16 times>, "\020\000\000\000@\204F\356<\177\000\000\320\f\000\200<\177\000\000P\204F\356\021\000\000\000\300\205F\356<\177\000\000\210\207F\356<\177\000\000\000\000\000\000\000\000\000\000\220\207F\356<\177\000\000N"...
 
#24 0x0000000000664bd2 in Item::fix_fields_if_needed (this=0x7f3c80037fe8, thd=0x7f3c80000d90, ref=0x7f3c80038580) at /home/dan/repos/mariadb-server-10.3/sql/item.h:829
 
No locals.
 
#25 0x00000000006640e9 in Item::fix_fields_if_needed_for_scalar (this=0x7f3c80037fe8, thd=0x7f3c80000d90, ref=0x7f3c80038580) at /home/dan/repos/mariadb-server-10.3/sql/item.h:833
 
No locals.
 
#26 0x00000000006fdb85 in Item::fix_fields_if_needed_for_bool (this=0x7f3c80037fe8, thd=0x7f3c80000d90, ref=0x7f3c80038580) at /home/dan/repos/mariadb-server-10.3/sql/item.h:837
 
No locals.
 
#27 0x0000000000b84a3a in Item_cond::fix_fields (this=0x7f3c80038468, thd=0x7f3c80000d90, ref=0x7f3c800386c0) at /home/dan/repos/mariadb-server-10.3/sql/item_cmpfunc.cc:4628
 
        type = Item::FUNC_ITEM
 
        li = {<base_list_iterator> = {list = 0x7f3c80038528, el = 0x7f3c80038578, prev = 0x7f3c80038528, current = 0x7f3c80038578}, <No data fields>}
 
        item = 0x7f3c80037fe8
 
        buff = "8h\000\000\000\000\000"
 
        is_and_cond = true
 
#28 0x0000000000664bd2 in Item::fix_fields_if_needed (this=0x7f3c80038468, thd=0x7f3c80000d90, ref=0x7f3c800386c0) at /home/dan/repos/mariadb-server-10.3/sql/item.h:829
 
No locals.
 
#29 0x0000000000bbdf9e in Item_func::fix_fields (this=0x7f3c80038630, thd=0x7f3c80000d90, ref=0x7f3c80038708) at /home/dan/repos/mariadb-server-10.3/sql/item_func.cc:352
 
        item = 0x14de3b2 <_my_thread_var+18>
 
        arg = 0x7f3c800386c0
 
        arg_end = 0x7f3c800386d0
 
        buff = "`\v\000\200<\177\000\000\260\f\000\200<\177\000\000\000\206F\356<\177\000\000\232[P\001\000\000\000\000\320\f\000\200<\177\000\000\260\f\000\200<\177\000\000\320\f\000\200<\177\000\000\320\f\000\200<\177\000\000\060\206F\356<\177\000\000ēP\001\000\000\000\000\002\000\000\000\001\000\000\000\320\f\000\200<\177\000\000\035\377W\001\000\000\000\000\000\a\301\344\247\035=\343ІF\356<\177\000\000u\235L\001\000\000\000\000p\206F\356<\177\000\000B\213P\001\r\000\000\000\020\000\000\000\000\000\000\000\320\f\000\200<\177\000\000\000\000\001\000\001\000\000\000B\204W\001\000\000\000\000\304oW\001\000\000\000\000\017\000\000\200\031\001\000\000\000\211F\356<\177\000\000\020\000\000\000\000\000\000\000"...
 
#30 0x0000000000664bd2 in Item::fix_fields_if_needed (this=0x7f3c80038630, thd=0x7f3c80000d90, ref=0x7f3c80038708) at /home/dan/repos/mariadb-server-10.3/sql/item.h:829
 
No locals.
 
#31 0x00000000006640e9 in Item::fix_fields_if_needed_for_scalar (this=0x7f3c80038630, thd=0x7f3c80000d90, ref=0x7f3c80038708) at /home/dan/repos/mariadb-server-10.3/sql/item.h:833
 
No locals.
 
#32 0x00000000006f4ed4 in setup_fields (thd=0x7f3c80000d90, ref_pointer_array={m_array = 0x7f3c8003c5f8, m_size = 55}, fields=@0x7f3c80011d78: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f3c80038700, last = 0x7f3c80038f40, elements = 2}, <No data fields>}, column_usage=MARK_COLUMNS_READ, sum_func_list=0x7f3c8003c3b0, pre_fix=0x7f3c80011d90, allow_sum_func=true) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:7542
 
        item = 0x7f3c80038630
 
        saved_column_usage = MARK_COLUMNS_READ
 
        save_allow_sum_func = {map = 0}
 
        it = {<base_list_iterator> = {list = 0x7f3c80011d78, el = 0x7f3c80038700, prev = 0x7f3c80011d78, current = 0x7f3c80038700}, <No data fields>}
 
        save_is_item_list_lookup = true
 
        make_pre_fix = true
 
        _db_stack_frame_ = {func = 0x158e9f2 "JOIN::prepare", file = 0x158e7fe "/home/dan/repos/mariadb-server-10.3/sql/sql_select.cc", level = 2147483662, line = -1, prev = 0x7f3cee468d18}
 
        li = {<base_list_iterator> = {list = 0x7f3c80005ad0, el = 0x1e9a738 <end_of_list>, prev = 0x7f3c80005ad0, current = 0x1e9a738 <end_of_list>}, <No data fields>}
 
        var = 0x0
 
        ref = {m_array = 0x7f3c8003c5f8, m_size = 55}
 
#33 0x00000000007effe5 in JOIN::prepare (this=0x7f3c8003c090, tables_init=0x7f3c80038fe0, wild_num=0, conds_init=0x7f3c8003a3c8, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f3c80011c50, unit_arg=0x7f3c80012090) at /home/dan/repos/mariadb-server-10.3/sql/sql_select.cc:1152
 
        _db_stack_frame_ = {func = 0x159d4b6 "st_select_lex_unit::prepare_join", file = 0x159d2fc "/home/dan/repos/mariadb-server-10.3/sql/sql_union.cc", level = 2147483661, line = -1, prev = 0x7f3cee468ec8}
 
        tbl = 0x0
 
        li = {<base_list_iterator> = {list = 0x7f3c80011e68, el = 0x1e9a738 <end_of_list>, prev = 0x0, current = 0x0}, <No data fields>}
 
        real_og_num = 0
 
        save_place = NO_MATTER
 
        with_clause = 0x7f3cee468c68
 
        with_elem = 0x15093c4 <_db_pargs_+20>
 
        res = 32572
 
#34 0x00000000008c79fc in st_select_lex_unit::prepare_join (this=0x7f3c80012090, thd_arg=0x7f3c80000d90, sl=0x7f3c80011c50, tmp_result=0x7f3c8003bfa8, additional_options=0, is_union_select=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_union.cc:647
 
        _db_stack_frame_ = {func = 0x159d6b0 "st_select_lex_unit::prepare", file = 0x159d2fc "/home/dan/repos/mariadb-server-10.3/sql/sql_union.cc", level = 2147483660, line = -1, prev = 0x7f3cee469388}
 
        derived = 0x7f3c8003ac18
 
        can_skip_order_by = false
 
        join = 0x7f3c8003c090
 
#35 0x00000000008c28f1 in st_select_lex_unit::prepare (this=0x7f3c80012090, derived_arg=0x7f3c8003ac18, sel_result=0x7f3c8003bfa8, additional_options=0) at /home/dan/repos/mariadb-server-10.3/sql/sql_union.cc:993
 
        lex_select_save = 0x7f3c800053d8
 
        sl = 0x7f3c80011c50
 
        first_sl = 0x7f3c80011c50
 
        is_recursive = false
 
        is_rec_result_table_created = false
 
        union_part_count = 0
 
        tmp_result = 0x7f3c8003bfa8
 
        is_union_select = false
 
        have_except = false
 
        have_intersect = false
 
        instantiate_tmp_table = false
 
        single_tvc = false
 
        _db_stack_frame_ = {func = 0x1580020 "mysql_derived_prepare", file = 0x157fe6b "/home/dan/repos/mariadb-server-10.3/sql/sql_derived.cc", level = 2147483659, line = -1, prev = 0x7f3cee4695e0}
 
#36 0x000000000073e71b in mysql_derived_prepare (thd=0x7f3c80000d90, lex=0x7f3c80004b80, derived=0x7f3c8003ac18) at /home/dan/repos/mariadb-server-10.3/sql/sql_derived.cc:793
 
        unit = 0x7f3c80012090
 
        res = false
 
        _db_stack_frame_ = {func = 0x157ff22 "mysql_derived_merge_for_insert", file = 0x157fe6b "/home/dan/repos/mariadb-server-10.3/sql/sql_derived.cc", level = 2147483658, line = -1, prev = 0x7f3cee4696a0}
 
        first_select = 0x7f3c80011c50
 
#37 0x000000000073fe59 in mysql_derived_merge_for_insert (thd=0x7f3c80000d90, lex=0x7f3c80004b80, derived=0x7f3c8003ac18) at /home/dan/repos/mariadb-server-10.3/sql/sql_derived.cc:497
 
        _db_stack_frame_ = {func = 0x157fe56 "mysql_handle_derived", file = 0x157fe6b "/home/dan/repos/mariadb-server-10.3/sql/sql_derived.cc", level = 2147483657, line = 497, prev = 0x7f3cee469758}
 
#38 0x0000000000741190 in mysql_handle_derived (lex=0x7f3c80004b80, phases=16) at /home/dan/repos/mariadb-server-10.3/sql/sql_derived.cc:119
 
        allowed_phases = 247 '\367'
 
        cursor = 0x7f3c8003ac18
 
        sl = 0x7f3c800053d8
 
        phase_flag = 16
 
        phase = 4
 
        res = false
 
        _db_stack_frame_ = {func = 0x159dea6 "Multiupdate_prelocking_strategy::handle_end", file = 0x159dba4 "/home/dan/repos/mariadb-server-10.3/sql/sql_update.cc", level = 2147483656, line = -1, prev = 0x7f3cee4698d8}
 
#39 0x00000000008d0806 in Multiupdate_prelocking_strategy::handle_end (this=0x7f3cee469c98, thd=0x7f3c80000d90) at /home/dan/repos/mariadb-server-10.3/sql/sql_update.cc:1572
 
        _db_stack_frame_ = {func = 0x15774ed "open_tables", file = 0x1576fc4 "/home/dan/repos/mariadb-server-10.3/sql/sql_base.cc", level = 2147483655, line = -1, prev = 0x7f3cee469ab8}
 
        lex = 0x7f3c80004b80
 
        select_lex = 0x7f3c800053d8
 
        table_list = 0x7f3c8003ac18
 
        tl = 0x102a80000cb0
 
        fields = 0x7f3cee4698d0
 
        tables_for_update = 139897822252208
 
        ti = {<base_list_iterator> = {list = 0x14deed7 <my_thread_var_dbug+39>, el = 0x7f3cee4698a0, prev = 0x7f3c80000cb0, current = 0x7f3c80000b60}, <No data fields>}
 
        using_lock_tables = false
 
#40 0x00000000006eb1b7 in open_tables (thd=0x7f3c80000d90, options=@0x7f3c80005db0: {m_options = DDL_options_st::OPT_NONE}, start=0x7f3cee469c78, counter=0x7f3cee469c6c, flags=0, prelocking_strategy=0x7f3cee469c98) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.cc:4317
 
        table_to_open = 0x7f3c8003b280
 
        sroutine_to_open = 0x7f3c80004c18
 
        tables = 0x0
 
        ot_ctx = {m_thd = 0x7f3c80000d90, m_failed_table = 0x0, m_start_of_statement_svp = {m_stmt_ticket = 0x0, m_trans_ticket = 0x0}, m_timeout = 86400, m_flags = 0, m_action = Open_table_context::OT_NO_ACTION, m_has_locks = false, m_has_protection_against_grl = true}
 
        error = false
 
        some_routine_modifies_data = false
 
        has_prelocking_list = false
 
        _db_stack_frame_ = {func = 0x159df75 "mysql_multi_update_prepare", file = 0x159dba4 "/home/dan/repos/mariadb-server-10.3/sql/sql_update.cc", level = 2147483654, line = -1, prev = 0x7f3cee469c48}
 
#41 0x00000000008d1dba in open_tables (thd=0x7f3c80000d90, tables=0x7f3cee469c78, counter=0x7f3cee469c6c, flags=0, prelocking_strategy=0x7f3cee469c98) at /home/dan/repos/mariadb-server-10.3/sql/sql_base.h:250
 
No locals.
 
#42 0x00000000008d199a in mysql_multi_update_prepare (thd=0x7f3c80000d90) at /home/dan/repos/mariadb-server-10.3/sql/sql_update.cc:1729
 
        lex = 0x7f3c80004b80
 
        table_list = 0x7f3c8003ac18
 
        tl = 0xffffffffffffffff
 
        prelocking_strategy = {<DML_prelocking_strategy> = {<Prelocking_strategy> = {_vptr$Prelocking_strategy = 0x1b94350 <vtable for Multiupdate_prelocking_strategy+16>}, <No data fields>}, done = true, has_prelocking_list = false}
 
        table_count = 11
 
        _db_stack_frame_ = {func = 0x1586ec8 "mysql_execute_command", file = 0x1586748 "/home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc", level = 2147483653, line = -1, prev = 0x7f3cee46a850}
 
        ti = {<base_list_iterator> = {list = 0xe33d1da7e4c10700, el = 0x7f3cee469cb0, prev = 0x7ab0e9 <multi_update_precheck(THD*, TABLE_LIST*)+985>, current = 0x7f3c00000000}, <No data fields>}
 
#43 0x000000000079fe5b in mysql_execute_command (thd=0x7f3c80000d90) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:4381
 
        res = 0
 
        up_result = 0
 
        lex = 0x7f3c80004b80
 
        select_lex = 0x7f3c800053d8
 
        first_table = 0x7f3c8003ac18
 
        all_tables = 0x7f3c8003ac18
 
        unit = 0x7f3c80004c40
 
        have_table_map_for_update = false
 
        rpl_filter = 0x7f3cee46a8a0
 
        _db_stack_frame_ = {func = 0x1587a81 "mysql_parse", file = 0x1586748 "/home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc", level = 2147483652, line = -1, prev = 0x7f3cee46b7e8}
 
        orig_binlog_format = BINLOG_FORMAT_MIXED
 
        orig_current_stmt_binlog_format = BINLOG_FORMAT_STMT
 
#44 0x0000000000797dc5 in mysql_parse (thd=0x7f3c80000d90, rawbuf=0x7f3c800114d8 "UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ( "..., length=898, parser_state=0x7f3cee46bd88, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:7870
 
        found_semicolon = 0x0
 
        error = 32572
 
        lex = 0x7f3c80004b80
 
        err = false
 
        _db_stack_frame_ = {func = 0x1586a01 "dispatch_command", file = 0x1586748 "/home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc", level = 2147483651, line = -1, prev = 0x7f3cee46bf40}
 
#45 0x0000000000791fa0 in dispatch_command (command=COM_QUERY, thd=0x7f3c80000d90, packet=0x7f3c80008e31 "UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ( "..., packet_length=898, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:1852
 
        packet_end = 0x7f3c8001185a ""
 
        parser_state = {m_lip = {lookahead_token = -1, lookahead_yylval = 0x0, m_thd = 0x7f3c80000d90, m_ptr = 0x7f3c8001185b "\004", m_tok_start = 0x7f3c8001185b "\004", m_tok_end = 0x7f3c8001185b "\004", m_end_of_query = 0x7f3c8001185a "", m_tok_start_prev = 0x7f3c8001185a "", m_buf = 0x7f3c800114d8 "UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ( "..., m_buf_length = 898, m_echo = true, m_echo_saved = false, m_cpp_buf = 0x7f3c800118b8 "UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ( "..., m_cpp_ptr = 0x7f3c80011c3a "", m_cpp_tok_start = 0x7f3c80011c3a "", m_cpp_tok_start_prev = 0x7f3c80011c3a "", m_cpp_tok_end = 0x7f3c80011c3a "", m_body_utf8 = 0x0, m_body_utf8_ptr = 0x7f3cee46be40 "", m_cpp_utf8_processed_ptr = 0x0, next_state = MY_LEX_END, found_semicolon = 0x0, ignore_space = false, stmt_prepare_mode = false, multi_statements = true, yylineno = 1, m_digest = 0x0, in_comment = NO_COMMENT, in_comment_saved = (unknown: 0x7f3c), m_cpp_text_start = 0x7f3c80011c38 "62", m_cpp_text_end = 0x7f3c80011c3a "", m_underscore_cs = 0x0}, m_yacc = {yacc_yyss = 0x0, yacc_yyvs = 0x0, m_set_signal_info = {m_item = {0x0 <repeats 12 times>}}, m_lock_type = TL_READ_DEFAULT, m_mdl_type = MDL_SHARED_READ}, m_digest_psi = 0x0}
 
        net = 0x7f3c80001098
 
        error = false
 
        do_end_of_statement = true
 
        _db_stack_frame_ = {func = 0x15867ec "do_command", file = 0x1586748 "/home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc", level = 2147483650, line = -1, prev = 0x7f3cee46ccf0}
 
        drop_more_results = false
 
#46 0x0000000000795e64 in do_command (thd=0x7f3c80000d90) at /home/dan/repos/mariadb-server-10.3/sql/sql_parse.cc:1398
 
        return_value = true
 
        packet = 0x7f3c80008e30 "\003UPDATE ( SELECT DISTINCT ( ( 66 , 'x' NOT BETWEEN ( SELECT DISTINCT EXISTS ( SELECT DISTINCT v1270 FROM v1269 UNION SELECT v1274 FROM ( SELECT DISTINCT ( SELECT v1270 FROM ( SELECT DISTINCT ( ( NOT ("...
 
        packet_length = 899
 
        net = 0x7f3c80001098
 
        command = COM_QUERY
 
        _db_stack_frame_ = {func = 0x180e13e "?func", file = 0x180e144 "?file", level = 2147483649, line = -1, prev = 0x0}
 
#47 0x000000000094981f in do_handle_one_connection (connect=0x488d160) at /home/dan/repos/mariadb-server-10.3/sql/sql_connect.cc:1403
 
        create_user = true
 
        thr_create_utime = 59333890381
 
        thd = 0x7f3c80000d90
 
#48 0x00000000009495ea in handle_one_connection (arg=0x488d160) at /home/dan/repos/mariadb-server-10.3/sql/sql_connect.cc:1308
 
        connect = 0x488d160
 
#49 0x00007f3cee2a08ca in start_thread () from /lib64/libc.so.6
 
No symbol table info available.
 
#50 0x00007f3cee240500 in clone3 () from /lib64/libc.so.6
 
 
 
 
 
#5  0x0000000000b2afe0 in handler::ha_rnd_next (this=0x7f3c80059438, buf=0x7f3c80058fe0 "\377") at /home/dan/repos/mariadb-server-10.3/sql/handler.cc:2852
 
2852	  DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE ||
 
(gdb) p table_share->tmp_table
 
$1 = NO_TMP_TABLE
 
(gdb) p m_lock_type
 
$2 = 2
 
note constant F_UNLCK=3

Comment by Alice Sherepa [ 2022-05-19 ]

with Myisam and Aria:

CREATE TABLE t1 ( a int);
 
 
 
UPDATE 
(SELECT (5 , ( WITH cte AS (SELECT 1) SELECT 1 FROM t1) )) dt 
NATURAL JOIN t1 
SET a = 1 ;

bb-10.2-release 0ba528fe56f6c637d9fbc9d177a

 
mysqld: /10.2/sql/handler.cc:2661: int handler::ha_rnd_next(uchar*): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed.
 
220519  9:40:07 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.2.44-MariaDB-debug-log
 
 
 
linux/raise.c:51(__GI_raise)[0x7f627a7817bb]
 
stdlib/abort.c:81(__GI_abort)[0x7f627a76c535]
 
sql/handler.cc:2662(handler::ha_rnd_next(unsigned char*))[0x557cee11a9e1]
 
sql/handler.cc:2899(handler::read_first_row(unsigned char*, unsigned int))[0x557cee11fbcf]
 
sql/sql_class.h:6016(handler::ha_read_first_row(unsigned char*, unsigned int))[0x557cedc14c4e]
 
sql/sql_select.cc:19448(join_read_system(st_join_table*))[0x557cedbcf477]
 
sql/sql_select.cc:19344(join_read_const_table(THD*, st_join_table*, st_position*))[0x557cedbce3b3]
 
sql/sql_select.cc:4175(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x557cedb63af1]
 
sql/sql_select.cc:1597(JOIN::optimize_inner())[0x557cedb4a1f7]
 
sql/sql_select.cc:1127(JOIN::optimize())[0x557cedb45504]
 
sql/item_subselect.cc:3937(subselect_single_select_engine::exec())[0x557cee3189db]
 
sql/item_subselect.cc:771(Item_subselect::exec())[0x557cee2f8aa8]
 
sql/item_subselect.cc:1409(Item_singlerow_subselect::val_int())[0x557cee2fe682]
 
sql/item.h:1575(Item::update_null_value())[0x557ced871c32]
 
sql/item_subselect.h:190(Item_subselect::is_null())[0x557cee32c04d]
 
sql/item_row.cc:59(Item_row::fix_fields(THD*, Item**))[0x557cee2ad83d]
 
sql/sql_base.cc:7274(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_mark_columns, List<Item>*, List<Item>*, bool))[0x557ced988645]
 
sql/sql_select.cc:807(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x557cedb4138c]
 
sql/sql_union.cc:597(st_select_lex_unit::prepare(THD*, select_result*, unsigned long))[0x557cedd276b2]
 
sql/sql_derived.cc:764(mysql_derived_prepare(THD*, LEX*, TABLE_LIST*))[0x557ceda1af7f]
 
sql/sql_derived.cc:498(mysql_derived_merge_for_insert(THD*, LEX*, TABLE_LIST*))[0x557ceda19301]
 
sql/sql_derived.cc:119(mysql_handle_derived(LEX*, unsigned int))[0x557ceda17395]
 
sql/sql_update.cc:1386(Multiupdate_prelocking_strategy::handle_end(THD*))[0x557cedd3f4bb]
 
sql/sql_base.cc:4173(open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x557ced975ad8]
 
sql/sql_base.h:251(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x557cedd341cf]
 
sql/sql_update.cc:1545(mysql_multi_update_prepare(THD*))[0x557cedd4074c]
 
sql/sql_parse.cc:4094(mysql_execute_command(THD*))[0x557ceda9ec6e]
 
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x557cedab98cc]
 
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x557ceda90919]
 
sql/sql_parse.cc:1381(do_command(THD*))[0x557ceda8d34a]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x557cede3a222]
 
sql/sql_connect.cc:1242(handle_one_connection)[0x557cede39ae3]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x557cef2d5796]
 
nptl/pthread_create.c:487(start_thread)[0x7f627b49bfa3]
 
x86_64/clone.S:97(clone)[0x7f627a842eff]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x62b000000290): UPDATE 
(SELECT (5 , ( WITH cte AS (SELECT 1) SELECT 1 FROM t1) )) dt 
NATURAL JOIN t1 
SET a = 1

and with InnoDB:

--source include/have_innodb.inc
 
CREATE TABLE t1 ( a int)engine=innodb;
 
 
 
UPDATE 
(SELECT (5 , ( WITH cte AS (SELECT 1) SELECT 1 FROM t1) )) dt 
NATURAL JOIN t1 
SET a = 1 ;

bb-10.2-release 0ba528fe56f6c637d9fbc9d177a

 
mysqld: /10.2/sql/handler.cc:2661: int handler::ha_rnd_next(uchar*): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed.
 
220519  9:43:27 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.2.44-MariaDB-debug-log
 
 
 
linux/raise.c:51(__GI_raise)[0x7f25a63f37bb]
 
stdlib/abort.c:81(__GI_abort)[0x7f25a63de535]
 
sql/handler.cc:2662(handler::ha_rnd_next(unsigned char*))[0x56118297d9e1]
 
sql/records.cc:492(rr_sequential(READ_RECORD*))[0x561182d42c94]
 
sql/sql_select.cc:19842(join_init_read_record(st_join_table*))[0x5611824357f4]
 
sql/sql_select.cc:18907(sub_select(JOIN*, st_join_table*, bool))[0x56118242e3c9]
 
sql/sql_select.cc:18453(do_select(JOIN*, Procedure*))[0x56118242c054]
 
sql/sql_select.cc:3651(JOIN::exec_inner())[0x5611823c2924]
 
sql/sql_select.cc:3447(JOIN::exec())[0x5611823c0496]
 
sql/item_subselect.cc:4023(subselect_single_select_engine::exec())[0x561182b7cbbd]
 
sql/item_subselect.cc:771(Item_subselect::exec())[0x561182b5baa8]
 
sql/item_subselect.cc:1409(Item_singlerow_subselect::val_int())[0x561182b61682]
 
sql/item.h:1575(Item::update_null_value())[0x5611820d4c32]
 
sql/item_subselect.h:190(Item_subselect::is_null())[0x561182b8f04d]
 
sql/item_row.cc:59(Item_row::fix_fields(THD*, Item**))[0x561182b1083d]
 
sql/sql_base.cc:7274(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_mark_columns, List<Item>*, List<Item>*, bool))[0x5611821eb645]
 
sql/sql_select.cc:807(JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5611823a438c]
 
sql/sql_union.cc:597(st_select_lex_unit::prepare(THD*, select_result*, unsigned long))[0x56118258a6b2]
 
sql/sql_derived.cc:764(mysql_derived_prepare(THD*, LEX*, TABLE_LIST*))[0x56118227df7f]
 
sql/sql_derived.cc:498(mysql_derived_merge_for_insert(THD*, LEX*, TABLE_LIST*))[0x56118227c301]
 
sql/sql_derived.cc:119(mysql_handle_derived(LEX*, unsigned int))[0x56118227a395]
 
sql/sql_update.cc:1386(Multiupdate_prelocking_strategy::handle_end(THD*))[0x5611825a24bb]
 
sql/sql_base.cc:4173(open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x5611821d8ad8]
 
sql/sql_base.h:251(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x5611825971cf]
 
sql/sql_update.cc:1545(mysql_multi_update_prepare(THD*))[0x5611825a374c]
 
sql/sql_parse.cc:4094(mysql_execute_command(THD*))[0x561182301c6e]
 
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56118231c8cc]
 
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5611822f3919]
 
sql/sql_parse.cc:1381(do_command(THD*))[0x5611822f034a]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x56118269d222]
 
sql/sql_connect.cc:1242(handle_one_connection)[0x56118269cae3]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x561183b38796]
 
nptl/pthread_create.c:487(start_thread)[0x7f25a710dfa3]
 
x86_64/clone.S:97(clone)[0x7f25a64b4eff]
 
 
 
Query (0x62b000000290): UPDATE 
(SELECT (5 , ( WITH cte AS (SELECT 1) SELECT 1 FROM t1) )) dt 
NATURAL JOIN t1 
SET a = 1
 
 

Comment by Alice Sherepa [ 2023-10-10 ]

please check also test from MDEV-32429

CREATE TABLE x ( x TEXT ) ;
 
INSERT INTO x ( x ) VALUES ( 1 ) ;
 
UPDATE x SET x = ( SELECT 1.000000 WHERE - 'x' >= x IS NOT NULL = ( CASE x IN ( SELECT x FROM x WHERE x BETWEEN 1 AND 1 GROUP BY x , x HAVING CASE WHEN - 'x' >= x IS NOT NULL = ( ( WITH RECURSIVE x ( x ) AS ( SELECT 1 UNION SELECT 1 - x FROM x INTERSECT SELECT * FROM x ) SELECT x FROM x WHERE 1 = x ) < x AND x < 'x' ) THEN 'x' ELSE x END ) WHEN x < ( WITH x ( x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x , x ) AS ( WITH x AS ( SELECT ( SELECT x FROM x WHERE 1 = x ) AS x FROM x GROUP BY - CASE x WHEN CASE 1 WHEN 1 THEN 1 / 1 WHEN 1 THEN 1 ELSE 1 / ( WITH x ( x ) AS ( WITH x ( x ) AS ( SELECT 1 UNION SELECT 1 - x FROM x ) SELECT 1 UNION SELECT x + 1 FROM x ) SELECT DISTINCT * FROM x UNION SELECT * FROM x ORDER BY x ) END THEN FALSE < ( WITH x ( x ) AS ( SELECT 1 EXCEPT SELECT x + 1 FROM x ) SELECT * FROM x WHERE x > 1 AND x = CASE WHEN x >= 1 THEN ( - ( SELECT 1.000000 BETWEEN ( x = 1 AND x < ( WITH x ( x ) AS ( WITH x AS ( SELECT 'x' AS x ) SELECT 1 UNION SELECT 1 - x FROM x ) SELECT * FROM x WHERE ( x , x ) NOT IN ( SELECT TRUE , x FROM x ) ) ) AND 1 FROM x WHERE 1 = x ) >= x IS NOT NULL = ( 1 < x AND x < 'x' ) IS NOT NULL ) ELSE x END NOT LIKE 1 + 1 + 1 + 1 + 1 + 1 + 1 + ( SELECT ( SELECT x FROM x WHERE 1 = x ) AS x FROM x WHERE ( SELECT 1 FROM x ORDER BY x < 1 OR ( x > 1 AND CASE WHEN 1 = 1 THEN 1 / 1 WHEN 1 = 1 THEN 1 ELSE 1 / 1 END < 1 ) ) != ( x IN ( SELECT DISTINCT x AS x FROM x WHERE x = x + 1 ORDER BY ( WITH x ( x ) AS ( SELECT 1 UNION SELECT 1 - x FROM x WHERE ( x = 1 ) OR ( x = 1 ) OR ( x BETWEEN 1 AND 1 ) OR ( x = 1 ) ) SELECT x ORDER BY 1 ) ) ) >= 1 WINDOW x AS ( PARTITION BY x ORDER BY x DESC ) ) UNION SELECT * FROM x ) BETWEEN 1 AND 1 ELSE CASE WHEN x % 1 != 1 THEN x END END > 1 WINDOW x AS ( PARTITION BY x ORDER BY x DESC ) ) SELECT ( SELECT 1.000000 FROM ( SELECT x FROM x GROUP BY x ) AS x WHERE - 'x' >= x IS NOT NULL = ( x IN ( x NOT LIKE ( SELECT x ) + x IS NOT NULL ) AND x NOT IN ( 1 , 1 ) ) ) IS NULL FROM x ) SELECT 1 FROM x ) THEN 'x' WHEN 1 >= x LIKE ( SELECT x FROM ( SELECT ( SELECT x FROM x WHERE 1 = x ) AS x FROM x WINDOW x AS ( PARTITION BY x ORDER BY x DESC ) ORDER BY x , x ) AS x WHERE ( x = 'x' OR x = 'x' ) AND x IS NOT NULL GROUP BY x ) THEN 1 END < x AND x < 'x' ) ) WHERE x = 1 ;

Comment by Igor Babaev [ 2023-10-25 ]

Given the table t1 built with the commands

CREATE TABLE t1 (a int) ENGINE=MYISAM;
 
INSERT INTO t1 VALUES (3), (7), (1);

the crash can also be reproduced with any of the following UPDATE statements.

UPDATE   
  (SELECT (5, (WITH cte AS (SELECT 1) SELECT a FROM t1))) dt
 
  JOIN t1 t 
  ON t.a=dt.a
 
SET t.a = 1;
 
 
 
 
 
UPDATE   
  (SELECT a FROM t1 
     WHERE (5, (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4)) <= (5,a)) dt
 
  JOIN t1 t 
  ON t.a=dt.a
 
SET t.a = 1;

The first statement is expected to return the error message:

ERROR 1241 (21000): Operand should contain 1 column(s)

The second statement is expected to update the record of t1 where a=7:

MariaDB [test]> SELECT * FROM t1;
 
+------+
 
| a    |
 
+------+
 
|    3 |
 
|    1 |
 
|    1 |
 
+------+

Comment by Igor Babaev [ 2023-10-26 ]

The following patch fixes the problem.

diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
 
index 2e67259..9c8e3c0 100644
 
--- a/sql/sql_cte.cc
 
+++ b/sql/sql_cte.cc
 
@@ -1241,14 +1241,14 @@ bool With_element::prepare_unreferenced(THD *thd)
 
        sl= sl->next_select())
 
     sl->context.outer_context= 0;
 
 
+  uint8 save_context_analysys_only= thd->lex->context_analysis_only;
 
   thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
 
   if (!spec->prepared &&
 
       (spec->prepare(spec->derived, 0, 0) ||
 
        rename_columns_of_derived_unit(thd, spec) ||
 
        check_duplicate_names(thd, first_sl->item_list, 1)))
 
     rc= true;
 
-
 
-  thd->lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED;
 
+  thd->lex->context_analysis_only= save_context_analysys_only;
 
   return rc;
 
 }

As we can see the code of the function With_element::prepare_unreferenced() before the patch did not properly restored the value of thd->lex->context_analysis_only. As a result it became 0 after the call of this function. This function is called when JOIN::prepare() is called for the subquery 

   (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4)

This happens when Item_row::fix_fields() calls fix_fields() for its second argument.
Note that after the call of With_element::prepare_unreferenced() remains 0. As a result the subquery is considered as a constant and Item_row::fix_fields() tries to execute it calling the virtual function is_null() for it.
It causes an assertion failure because the call of Item_row::fix_fields() happens during the invocation of Multiupdate_prelocking_strategy::handle_end() that calls mysql_derived_prepare for the derived table dt when proper locks for used tables has not been acquired yet.
With this patch the value of thd->lex->context_analysis_only is restored to CONTEXT_ANALYSIS_ONLY_DERIVED
that is set in the function mysql_multi_update_prepare().

Comment by Igor Babaev [ 2023-10-27 ]

Please review. The patch is trivial.

Comment by Oleksandr Byelkin [ 2023-11-01 ]

OK to push

Comment by Igor Babaev [ 2023-11-01 ]

A fix for this bug was pushed into 10.4. It has to be merged upstream as it is.

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