Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.6, 10.6.23
-
None
Description
CREATE DATABASE IF NOT EXISTS simple_db; |
|
|
CREATE TABLE simple_db.B ( |
pk INTEGER AUTO_INCREMENT, |
col_int_nokey INTEGER, |
col_int_key INTEGER, |
|
|
col_date_key DATE, |
col_date_nokey DATE, |
|
|
col_time_key TIME, |
col_time_nokey TIME, |
|
|
col_datetime_key DATETIME,
|
col_datetime_nokey DATETIME,
|
|
|
col_varchar_key VARCHAR(1), |
col_varchar_nokey VARCHAR(1), |
|
|
PRIMARY KEY (pk DESC), |
KEY (col_int_key DESC), |
KEY (col_date_key), |
KEY (col_time_key DESC), |
KEY (col_datetime_key), |
KEY (col_varchar_key, col_int_key DESC) |
)
|
;
|
|
|
INSERT /*! IGNORE */ INTO simple_db.B ( |
col_int_key, col_int_nokey,
|
col_date_key, col_date_nokey,
|
col_time_key, col_time_nokey,
|
col_datetime_key, col_datetime_nokey,
|
col_varchar_key, col_varchar_nokey
|
) VALUES (NULL, 4, '2018-11-11', '2018-11-11', '11:32:46.001324', '11:32:46.001324', NULL, NULL, 'w', 'w') |
;
|
|
|
CREATE TABLE simple_db.C ( |
pk INTEGER AUTO_INCREMENT, |
col_int_nokey INTEGER, |
col_int_key INTEGER, |
|
|
col_date_key DATE, |
col_date_nokey DATE, |
|
|
col_time_key TIME, |
col_time_nokey TIME, |
|
|
col_datetime_key DATETIME,
|
col_datetime_nokey DATETIME,
|
|
|
col_varchar_key VARCHAR(1), |
col_varchar_nokey VARCHAR(1), |
|
|
PRIMARY KEY (pk DESC), |
KEY (col_int_key DESC), |
KEY (col_date_key), |
KEY (col_time_key ASC), |
KEY (col_datetime_key ASC), |
KEY (col_varchar_key ASC, col_int_key DESC) |
)
|
;
|
|
|
INSERT /*! IGNORE */ INTO simple_db.C ( |
col_int_key, col_int_nokey,
|
col_date_key, col_date_nokey,
|
col_time_key, col_time_nokey,
|
col_datetime_key, col_datetime_nokey,
|
col_varchar_key, col_varchar_nokey
|
) VALUES (8, 5, '1909-08-29', '1909-08-29', '09:05:06.000000', '09:05:06.000000', '1938-03-11 22:47:41.050581', '1938-03-11 22:47:41.050581', 'o', 'o'),(9, 1, '1947-08-13', '1947-08-13', '17:30:41.047951', '17:30:41.047951', '2023-11-08 19:23:25.058555', '2023-11-08 19:23:25.058555', 'd', 'd') |
;
|
|
|
CREATE TABLE simple_db.BB ( |
pk INTEGER AUTO_INCREMENT, |
col_int_nokey INTEGER, |
col_int_key INTEGER, |
|
|
col_date_key DATE, |
col_date_nokey DATE, |
|
|
col_time_key TIME, |
col_time_nokey TIME, |
|
|
col_datetime_key DATETIME,
|
col_datetime_nokey DATETIME,
|
|
|
col_varchar_key VARCHAR(1), |
col_varchar_nokey VARCHAR(1), |
|
|
PRIMARY KEY (pk), |
KEY (col_int_key ASC), |
KEY (col_date_key DESC), |
KEY (col_time_key DESC), |
KEY (col_datetime_key DESC), |
KEY (col_varchar_key DESC, col_int_key) |
) AUTO_INCREMENT=10
|
;
|
|
|
INSERT /*! IGNORE */ INTO simple_db.BB ( |
col_int_key, col_int_nokey,
|
col_date_key, col_date_nokey,
|
col_time_key, col_time_nokey,
|
col_datetime_key, col_datetime_nokey,
|
col_varchar_key, col_varchar_nokey
|
) VALUES (NULL, 7, '1900-01-01', '1900-01-01', '04:41:24.024850', '04:41:24.024850', '1952-11-26 11:28:47.036493', '1952-11-26 11:28:47.036493', 'o', 'o'),(7, 6, '1984-12-10', '1984-12-10', '19:31:02.052544', '19:31:02.052544', '1900-01-01 00:00:00', '1900-01-01 00:00:00', NULL, NULL),(7, 1, '2009-07-28', '2009-07-28', NULL, NULL, '1919-10-04 09:50:32.019954', '1919-10-04 09:50:32.019954', NULL, NULL),(41, 78, '2023-02-18', '2023-02-18', '22:57:26.063041', '22:57:26.063041', '2027-02-19 00:00:00', '2027-02-19 00:00:00', 'f', 'f'),(241, 10, '1937-12-24', '1937-12-24', '16:28:17.048651', '16:28:17.048651', NULL, NULL, 'w', 'w'),(233, NULL, NULL, NULL, '22:32:35.063833', '22:32:35.063833', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'v', 'v'),(24, 254, '2017-12-05', '2017-12-05', '22:13:42.024198', '22:13:42.024198', NULL, NULL, 'u', 'u'),(3, 6, '1981-08-13', '1981-08-13', '07:25:54.021901', '07:25:54.021901', '2037-11-01 09:00:01.039770', '2037-11-01 09:00:01.039770', 'r', 'r'),(NULL, 7, '1984-03-29', '1984-03-29', '05:12:00.055757', '05:12:00.055757', '2036-10-30 18:52:35.060527', '2036-10-30 18:52:35.060527', 'e', 'e'),(4, 8, NULL, NULL, '16:09:58.027013', '16:09:58.027013', NULL, NULL, 'a', 'a') |
;
|
|
|
CREATE TABLE simple_db.CC ( |
pk INTEGER AUTO_INCREMENT, |
col_int_nokey INTEGER, |
col_int_key INTEGER, |
|
|
col_date_key DATE, |
col_date_nokey DATE, |
|
|
col_time_key TIME, |
col_time_nokey TIME, |
|
|
col_datetime_key DATETIME,
|
col_datetime_nokey DATETIME,
|
|
|
col_varchar_key VARCHAR(1), |
col_varchar_nokey VARCHAR(1), |
|
|
PRIMARY KEY (pk ASC), |
KEY (col_int_key ASC), |
KEY (col_date_key ASC), |
KEY (col_time_key), |
KEY (col_datetime_key ASC), |
KEY (col_varchar_key, col_int_key DESC) |
) AUTO_INCREMENT=10
|
;
|
|
|
INSERT /*! IGNORE */ INTO simple_db.CC ( |
col_int_key, col_int_nokey,
|
col_date_key, col_date_nokey,
|
col_time_key, col_time_nokey,
|
col_datetime_key, col_datetime_nokey,
|
col_varchar_key, col_varchar_nokey
|
) VALUES (9, 4, '1958-06-19', '1958-06-19', '22:25:38.057558', '22:25:38.057558', '1939-07-13 14:23:53.054657', '1939-07-13 14:23:53.054657', 'l', 'l'),(NULL, 0, '1900-01-01', '1900-01-01', '20:35:10.052673', '20:35:10.052673', '1979-04-18 00:00:00', '1979-04-18 00:00:00', 'm', 'm'),(9, 3, '1936-12-02', '1936-12-02', '00:57:12.028476', '00:57:12.028476', '2007-08-15 00:00:00', '2007-08-15 00:00:00', 'z', 'z'),(8, 2, '1945-03-28', '1945-03-28', '11:33:39.004432', '11:33:39.004432', '2038-05-21 03:17:27.007551', '2038-05-21 03:17:27.007551', 'r', 'r'),(2, 1, '2038-10-29', '2038-10-29', '00:52:10.005230', '00:52:10.005230', '1914-12-13 08:54:16.019670', '1914-12-13 08:54:16.019670', 'p', 'p'),(82, 90, '2012-03-07', '2012-03-07', '21:57:20.009943', '21:57:20.009943', '2005-11-27 02:54:42.031124', '2005-11-27 02:54:42.031124', NULL, NULL),(3, NULL, '1900-01-01', '1900-01-01', '13:24:32.043599', '13:24:32.043599', '1927-11-12 22:13:24.021511', '1927-11-12 22:13:24.021511', 'z', 'z'),(5, 1, '1916-10-09', '1916-10-09', '00:00:00', '00:00:00', '1970-08-21 08:02:38.019574', '1970-08-21 08:02:38.019574', 'o', 'o'),(NULL, 6, NULL, NULL, '21:47:40.003845', '21:47:40.003845', '1979-04-25 11:56:35.024210', '1979-04-25 11:56:35.024210', 'i', 'i'),(9, 5, '1999-07-14', '1999-07-14', '23:29:05.059011', '23:29:05.059011', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'g', 'g'),(0, 165, '1945-09-04', '1945-09-04', '00:00:00', '00:00:00', '1967-06-15 12:20:54.027873', '1967-06-15 12:20:54.027873', 'h', 'h'),(6, 7, '1900-01-01', '1900-01-01', '00:00:00', '00:00:00', '1936-02-04 00:00:00', '1936-02-04 00:00:00', NULL, NULL),(0, NULL, '1900-01-01', '1900-01-01', NULL, NULL, '2037-07-04 22:46:28.053844', '2037-07-04 22:46:28.053844', 'f', 'f'),(6, 0, '1965-12-05', '1965-12-05', '04:57:56.013365', '04:57:56.013365', NULL, NULL, 'q', 'q'),(7, 0, '1999-10-25', '1999-10-25', '12:07:35.011589', '12:07:35.011589', '1990-05-17 05:51:58.027333', '1990-05-17 05:51:58.027333', 'd', 'd'),(1, 0, '1900-01-01', '1900-01-01', '14:32:52.051666', '14:32:52.051666', NULL, NULL, 'l', 'l'),(172, 181, '1902-10-14', '1902-10-14', '01:06:21.028735', '01:06:21.028735', '1906-09-29 22:53:18.012407', '1906-09-29 22:53:18.012407', NULL, NULL),(6, 3, '1918-08-08', '1918-08-08', '21:57:44.018972', '21:57:44.018972', '2026-08-04 15:10:08.036188', '2026-08-04 15:10:08.036188', 'p', 'p'),(9, 1, '1973-06-07', '1973-06-07', '11:01:51.011650', '11:01:51.011650', '2010-03-20 00:00:00', '2010-03-20 00:00:00', 'o', 'o'),(2, 3, '1958-11-18', '1958-11-18', '13:45:36.060251', '13:45:36.060251', '1917-12-23 03:24:14.008191', '1917-12-23 03:24:14.008191', NULL, NULL) |
;
|
|
|
USE simple_db; |
|
|
SELECT /* `view_E` */ alias1.`col_date_nokey` AS field1, MAX( alias1.`col_int_nokey` ) AS field2 FROM ( CC AS alias2 RIGHT JOIN ( SELECT SQ1_alias1.* FROM BB AS SQ1_alias1 ) AS alias1 ON (alias2.`pk` = alias1.`col_int_key` AND alias1.`col_varchar_nokey` != ( SELECT MAX( DISTINCT SQ2_alias1.`col_varchar_nokey` ) AS SQ2_field1 FROM B AS SQ2_alias1 ) ) ) WHERE alias1.`col_varchar_nokey` = ( SELECT MAX( SQ3_alias1.`col_varchar_key` ) AS SQ3_field1 FROM ( BB AS SQ3_alias1 STRAIGHT_JOIN C AS SQ3_alias2 ON (SQ3_alias2.`col_varchar_key` = SQ3_alias1.`col_varchar_key` AND ( 6, 6 ) IN ( SELECT MAX( C_SQ1_alias1.`col_varchar_nokey` ) AS C_SQ1_field1, MAX( C_SQ1_alias1.`col_varchar_nokey` ) AS C_SQ1_field2 FROM ( CC AS C_SQ1_alias1 INNER JOIN CC AS C_SQ1_alias2 ON (C_SQ1_alias2.`pk` = C_SQ1_alias1.`col_int_key` ) ) WHERE C_SQ1_alias2.`pk` != C_SQ1_alias1.`col_int_key` ) ) ) ) GROUP BY field1 ORDER BY field1, field2; |
Version: '10.6.23-MariaDB-debug-log' socket: '/share8t/bld/10.6-bug/mysql-test/var/tmp/mysqld.1.sock' port: 19000 Source distribution
|
mariadbd: /10.6.23/sql/item_subselect.cc:791: virtual bool Item_subselect::exec(): Assertion `!eliminated' failed.
|
|
|
#8 0x00007f00c1445395 in __assert_fail_base (fmt=0x7f00c15b9a90 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x5572e4cf29e8 "!eliminated", file=file@entry=0x5572e4cf2798 "/10.6.23/sql/item_subselect.cc", line=line@entry=791, function=function@entry=0x5572e4cf29c0 "virtual bool Item_subselect::exec()") at ./assert/assert.c:92
|
#9 0x00007f00c1453eb2 in __GI___assert_fail (assertion=0x5572e4cf29e8 "!eliminated", file=0x5572e4cf2798 "/10.6.23/sql/item_subselect.cc", line=791, function=0x5572e4cf29c0 "virtual bool Item_subselect::exec()") at ./assert/assert.c:101
|
#10 0x00005572e40b5eb8 in Item_subselect::exec (this=0x7f00a007efc0) at /10.6.23/sql/item_subselect.cc:791
|
#11 0x00005572e40b8536 in Item_singlerow_subselect::val_str (this=0x7f00a007efc0, str=0x7f00a0271948) at /10.6.23/sql/item_subselect.cc:1501
|
#12 0x00005572e400bca6 in Arg_comparator::compare_string (this=0x7f00a02717f8) at /10.6.23/sql/item_cmpfunc.cc:822
|
#13 0x00005572e4025cbc in Arg_comparator::compare (this=0x7f00a02717f8) at /10.6.23/sql/item_cmpfunc.h:117
|
#14 0x00005572e400f79b in Item_func_eq::val_bool (this=0x7f00a0271748) at /10.6.23/sql/item_cmpfunc.cc:1851
|
#15 0x00005572e3c7901f in evaluate_join_record (join=0x7f00a0080240, join_tab=0x7f00a02709e0, error=0) at /10.6.23/sql/sql_select.cc:22497
|
#16 0x00005572e3c78bae in sub_select (join=0x7f00a0080240, join_tab=0x7f00a02709e0, end_of_records=false) at /10.6.23/sql/sql_select.cc:22399
|
#17 0x00005572e3c77e7e in do_select (join=0x7f00a0080240, procedure=0x0) at /10.6.23/sql/sql_select.cc:21922
|
#18 0x00005572e3c47869 in JOIN::exec_inner (this=0x7f00a0080240) at /10.6.23/sql/sql_select.cc:4939
|
#19 0x00005572e3c468a7 in JOIN::exec (this=0x7f00a0080240) at /10.6.23/sql/sql_select.cc:4717
|
#20 0x00005572e3c4823a in mysql_select (thd=0x7f00a0000dc8, tables=0x7f00a00167e8, fields=..., conds=0x7f00a007f188, og_num=3, order=0x7f00a007fe90, group=0x7f00a007f4d0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f00a0080218, unit=0x7f00a00051b0, select_lex=0x7f00a0015e68) at /10.6.23/sql/sql_select.cc:5196
|
#21 0x00005572e3c3646b in handle_select (thd=0x7f00a0000dc8, lex=0x7f00a00050e8, result=0x7f00a0080218, setup_tables_done_option=0) at /10.6.23/sql/sql_select.cc:573
|
#22 0x00005572e3bf1e50 in execute_sqlcom_select (thd=0x7f00a0000dc8, all_tables=0x7f00a00167e8) at /10.6.23/sql/sql_parse.cc:6422
|
#23 0x00005572e3be8b79 in mysql_execute_command (thd=0x7f00a0000dc8, is_called_from_prepared_stmt=false) at /10.6.23/sql/sql_parse.cc:4013
|
#24 0x00005572e3bf6fac in mysql_parse (thd=0x7f00a0000dc8, rawbuf=0x7f00a00156e0 "SELECT /* `view_E` */ alias1.`col_date_nokey` AS field1, MAX( alias1.`col_int_nokey` ) AS field2 FROM ( CC AS alias2 RIGHT JOIN ( SELECT SQ1_alias1.* FROM BB AS SQ1_alias1 ) AS alias1 ON (alias2.`pk` "..., length=916, parser_state=0x7f00b7581380) at /10.6.23/sql/sql_parse.cc:8200
|
#25 0x00005572e3be23b3 in dispatch_command (command=COM_QUERY, thd=0x7f00a0000dc8, packet=0x7f00a000baf9 "", packet_length=916, blocking=true) at /10.6.23/sql/sql_parse.cc:1908
|
#26 0x00005572e3be0d0c in do_command (thd=0x7f00a0000dc8, blocking=true) at /10.6.23/sql/sql_parse.cc:1421
|
#27 0x00005572e3dbe7a4 in do_handle_one_connection (connect=0x557306885518, put_in_cache=true) at /10.6.23/sql/sql_connect.cc:1386
|
#28 0x00005572e3dbe525 in handle_one_connection (arg=0x5573067b26c8) at /10.6.23/sql/sql_connect.cc:1298
|
#29 0x00005572e432684e in pfs_spawn_thread (arg=0x557306885088) at /10.6.23/storage/perfschema/pfs.cc:2201
|
#30 0x00007f00c14a81c4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
|
#31 0x00007f00c152885c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
|