[MDEV-31750] Assertion `0' failed in Item::val_native upon query with GROUP BY from Spider table Created: 2023-07-20  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: spider-da

Issue Links:
Relates
relates to MDEV-23838 Possibly wrong result or Assertion `0... Closed

 Description   

Extracted from MDEV-23838, as it turned out to be a separate issue after all.

Remember that the path to init_spider.inc may have to be changed, depending on the build.

--source plugin/spider/spider/include/init_spider.inc
 
--source include/have_partition.inc
 
 
 
SET spider_same_server_link= on;
 
eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
 
 
 
CREATE TABLE t1 (a TIME, b INT);
 
INSERT INTO t1 VALUES ('10:00:00',1),('20:00:00',2);
 
CREATE TABLE t2 (a TIME, b INT);
 
INSERT INTO t2 VALUES ('11:00:00',3),('12:00:00',2);
 
 
 
CREATE TABLE t_spider (a TIME, b INT) ENGINE=SPIDER PARTITION BY HASH(b) (
 
  PARTITION p1 COMMENT = "wrapper 'mysql', srv 's', table 't1'",
 
  PARTITION p2 COMMENT = "wrapper 'mysql', srv 's', table 't2'"  
);
 
 
 
SELECT b, MAX(a) FROM t_spider GROUP BY b;
 
 
 
# Cleanup
 
DROP TABLE t_spider, t1, t2;
 
 
 
--source plugin/spider/spider/include/deinit_spider.inc

10.4 30f3db3c

 
Version: '10.4.31-MariaDB-debug-log'  socket: '/mnt8t/bld/10.4-asan/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
 
mysqld: /data/src/10.4/sql/item.h:1355: virtual bool Item::val_native(THD*, Native*): Assertion `0' failed.
 
230720 11:48:38 [ERROR] mysqld got signal 6 ;
 
 
 
#8  0x00007f71a9225395 in __assert_fail_base (fmt=0x7f71a9399a70 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55e6cb8b31e0 "0", file=file@entry=0x55e6cb8b30a0 "/data/src/10.4/sql/item.h", line=line@entry=1355, function=function@entry=0x55e6cb8b3400 "virtual bool Item::val_native(THD*, Native*)") at ./assert/assert.c:92
 
#9  0x00007f71a9233df2 in __GI___assert_fail (assertion=0x55e6cb8b31e0 "0", file=0x55e6cb8b30a0 "/data/src/10.4/sql/item.h", line=1355, function=0x55e6cb8b3400 "virtual bool Item::val_native(THD*, Native*)") at ./assert/assert.c:101
 
#10 0x000055e6c95f0db8 in Item::val_native (this=0x62100008e928, thd=0x62b00005b208, to=0x62b000064ba8) at /data/src/10.4/sql/item.h:1355
 
#11 0x000055e6ca2c9e8a in Arg_comparator::min_max_update_field_native (this=0x62b000064ae0, thd=0x62b00005b208, field=0x61900025db88, item=0x62100008e928, cmp_sign=-1) at /data/src/10.4/sql/item_sum.cc:3119
 
#12 0x000055e6ca2ca589 in Item_sum_min_max::min_max_update_native_field (this=0x62b000062a18) at /data/src/10.4/sql/item_sum.cc:3142
 
#13 0x000055e6ca2c9aee in Item_sum_min_max::update_field (this=0x62b000062a18) at /data/src/10.4/sql/item_sum.cc:3085
 
#14 0x000055e6c9a11320 in update_tmptable_sum_func (func_ptr=0x62b000064c60, tmp_table=0x62300001c128) at /data/src/10.4/sql/sql_select.cc:26088
 
#15 0x000055e6c99f7109 in end_update (join=0x62b000064058, join_tab=0x62b000065b10, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:22443
 
#16 0x000055e6c9a2b863 in AGGR_OP::put_record (this=0x62b0000668f0, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:29601
 
#17 0x000055e6c9a3acc1 in AGGR_OP::put_record (this=0x62b0000668f0) at /data/src/10.4/sql/sql_select.h:1084
 
#18 0x000055e6c99e93c9 in sub_select_postjoin_aggr (join=0x62b000064058, join_tab=0x62b000065b10, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20596
 
#19 0x000055e6c99ebcbd in evaluate_join_record (join=0x62b000064058, join_tab=0x62b000065768, error=0) at /data/src/10.4/sql/sql_select.cc:21116
 
#20 0x000055e6c99eac12 in sub_select (join=0x62b000064058, join_tab=0x62b000065768, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20928
 
#21 0x000055e6c99e8448 in do_select (join=0x62b000064058, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:20412
 
#22 0x000055e6c99772d7 in JOIN::exec_inner (this=0x62b000064058) at /data/src/10.4/sql/sql_select.cc:4605
 
#23 0x000055e6c99748de in JOIN::exec (this=0x62b000064058) at /data/src/10.4/sql/sql_select.cc:4387
 
#24 0x000055e6c9978970 in mysql_select (thd=0x62b00005b208, tables=0x62b000062c10, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x62b000063458, having=0x0, proc_param=0x0, select_options=2147748608, result=0x62b000064028, unit=0x62b00005f140, select_lex=0x62b0000622e8) at /data/src/10.4/sql/sql_select.cc:4826
 
#25 0x000055e6c99495af in handle_select (thd=0x62b00005b208, lex=0x62b00005f080, result=0x62b000064028, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:442
 
#26 0x000055e6c98b9361 in execute_sqlcom_select (thd=0x62b00005b208, all_tables=0x62b000062c10) at /data/src/10.4/sql/sql_parse.cc:6473
 
#27 0x000055e6c98a6876 in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:3976
 
#28 0x000055e6c98c25b1 in mysql_parse (thd=0x62b00005b208, rawbuf=0x62b000062228 "SELECT b, MAX(a) FROM t_spider GROUP BY b", length=41, parser_state=0x7f71a144e860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:8010
 
#29 0x000055e6c989887c in dispatch_command (command=COM_QUERY, thd=0x62b00005b208, packet=0x629000230209 "", packet_length=41, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857
 
#30 0x000055e6c98953eb in do_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:1378
 
#31 0x000055e6c9c9407a in do_handle_one_connection (connect=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1420
 
#32 0x000055e6c9c93991 in handle_one_connection (arg=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1324
 
#33 0x000055e6ca902d5a in pfs_spawn_thread (arg=0x615000003508) at /data/src/10.4/storage/perfschema/pfs.cc:1869
 
#34 0x00007f71a9287fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#35 0x00007f71a93085bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible on all existing versions.
No obvious problem on a non-debug build.

 Comments   
Comment by Alexander Barkov [ 2023-07-20 ]

The problem:

  • Item_sum_min_max::fix_length_and_dec() detects the data type of the argument as TIME and configures itself to perform calculations using val_native().
  • Spider calls Item_sum_min_max::direct_add() and replaces args[0] from something of a TIME data type to Item_string which is not of a TIME data type.
  • Arg_comparator::min_max_update_field_native() calls args[0]->val_native() expecting a binary packed TIME. But there's already Item_string, which knows nothing about TIME, and which does not even support val_native().

A possible solution would be to modify the Spider code to create Item_time_literal instead of Item_string if the argument is of the TIME data type.

But we need a more abstract approach, because the same problem is also repeatable with the INET6 (and most likely with the UUID) data type:

--source ../include/init_spider.inc
 
--source include/have_partition.inc
 
SET spider_same_server_link= on;
 
 
 
--eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_1_MYPORT);
 
 
 
CREATE TABLE t1 (a INET6, b INT);
 
INSERT INTO t1 VALUES ('10::',1),('20::',2);
 
CREATE TABLE t2 (a INET6, b INT);
 
INSERT INTO t2 VALUES ('11::',3),('12::',2);
 
 
 
CREATE TABLE t_spider (a INET6, b INT) ENGINE=SPIDER PARTITION BY HASH(b) (
 
  PARTITION p1 COMMENT = "wrapper 'mysql', srv 's', table 't1'",
 
  PARTITION p2 COMMENT = "wrapper 'mysql', srv 's', table 't2'"  
);
 
 
 
SELECT b, MAX(a) FROM t_spider GROUP BY b;
 
DROP TABLE t_spider, t1, t2;

It seems we need a new virtual method in Type_handler, which makes a constant Item getting a value from a non-constant Item. Something like:

virtual Item *create_constant_item(THD *thd, Item *value) const;

The return type can possibly be Item_literal instead of just Item.

Note, we already have this method:

  virtual Item_literal *create_literal_item(THD *thd,
 
                                            const char *str, size_t length,
 
                                            CHARSET_INFO *cs,
 
                                            bool send_error) const

However:

  • It's implemented only for the TIME, DATETIME, DATE data types for now.
  • Also I'm afraid it won't always work here, because some data types can loose important information during a SOME_DATA_TYPE_VALUE -> STRING -> SOME_DATA_TYPE_VALUE roundtrip.
Comment by Yuchen Pei [ 2023-07-21 ]

mtr case

--echo
 
--echo MDEV-31750 Assertion `0' failed in Item::val_native upon query with GROUP BY from Spider table
 
--echo
 
 
 
--source include/have_partition.inc
 
--disable_query_log
 
--disable_result_log
 
--source ../../t/test_init.inc
 
--enable_result_log
 
--enable_query_log
 
 
 
--let $srv=srv_mdev_31750
 
evalp CREATE SERVER $srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');
 
 
 
CREATE TABLE t1 (a TIME, b INT);
 
INSERT INTO t1 VALUES ('10:00:00',1),('20:00:00',2);
 
CREATE TABLE t2 (a TIME, b INT);
 
INSERT INTO t2 VALUES ('11:00:00',3),('12:00:00',2);
 
 
 
eval CREATE TABLE t_spider (a TIME, b INT) ENGINE=SPIDER PARTITION BY HASH(b) (
 
  PARTITION p1 COMMENT = "wrapper 'mysql', srv '$srv', table 't1'",
 
  PARTITION p2 COMMENT = "wrapper 'mysql', srv '$srv', table 't2'"
 
);
 
 
 
SELECT b, MAX(a) FROM t_spider GROUP BY b;
 
 
 
DROP TABLE t_spider, t1, t2;
 
 
 
CREATE TABLE t1 (a INET6, b INT);
 
INSERT INTO t1 VALUES ('10::',1),('20::',2);
 
CREATE TABLE t2 (a INET6, b INT);
 
INSERT INTO t2 VALUES ('11::',3),('12::',2);
 
 
 
eval CREATE TABLE t_spider (a INET6, b INT) ENGINE=SPIDER PARTITION BY HASH(b) (
 
  PARTITION p1 COMMENT = "wrapper 'mysql', srv '$srv', table 't1'",
 
  PARTITION p2 COMMENT = "wrapper 'mysql', srv '$srv', table 't2'"
 
);
 
 
 
SELECT b, MAX(a) FROM t_spider GROUP BY b;
 
DROP TABLE t_spider, t1, t2;
 
 
 
DROP SERVER srv;
 
 
 
--disable_query_log
 
--disable_result_log
 
--source ../../t/test_deinit.inc
 
--enable_result_log
 
--enable_query_log

Comment by Yuchen Pei [ 2023-07-21 ]

Thanks bar. I've added the label spider-da given it seems to be
caused by direct aggregate. I also tested it on top of my change to
MDEV-29502 where I disable DA when using the group by handler GBH, and
it fails as expected, because a GBH is not created for partitioned
tables, for which DA is a legitimate usecase to reduce unnecessary
queries.

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