Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
11.3.0, 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL)
-
None
-
Ubuntu 20.04
Description
Run these queries in release build:
CREATE TABLE t0 ( c51 TEXT DEFAULT ( ATAN ( 109 ) ) ) ; |
INSERT INTO t0 VALUES ( -19 ) , ( 122 ) ; |
ALTER TABLE t0 MODIFY COLUMN c51 INT NOT NULL ; |
INSERT INTO t0 VALUES ( -83 ) , ( ATAN ( -89 LIKE EXISTS ( SELECT ROW_NUMBER ( ) OVER ( PARTITION BY c51 ORDER BY CASE c51 WHEN -107 THEN COUNT( DISTINCT c51 , + TRIM( TRAILING c51 FROM '/{;sxMhm&X$8fg7_ga#RG+7,>%)qs`b-Z7_\\><_k\'ML' ) NOT IN ( RAND ( ) NOT BETWEEN 69 AND -122 ) ) - - COS ( t0 . c51 ) ELSE 39 END IS TRUE ) NOT IN ( 107 , 15 , 57 ) AS c1 ) ) ) ; |
Will trigger Segmentation fault. GDB info:
Thread 16 "mariadbd" received signal SIGSEGV, Segmentation fault.
|
[Switching to Thread 0x7fffd242e300 (LWP 3369)]
|
0x00000000013316dd in Field::type_std_attributes (this=0x6190000a3458) at /home/wx/mariadb-11.3.0/sql/field.h:902
|
902 return Type_std_attributes(type_numeric_attributes(), dtcollation());
|
|
|
#0 0x00000000013316dd in Field::type_std_attributes (this=0x619000159258) at /home/wx/mariadb-11.3.0/sql/field.h:902
|
#1 Item_field::set_field (this=0x6290000bc7e0, field_par=<optimized out>) at /home/wx/mariadb-11.3.0/sql/item.cc:3141
|
#2 0x000000000133122a in Item_field::Item_field (this=0x6290000bc7e0, thd=<optimized out>, f=<optimized out>) at /home/wx/mariadb-11.3.0/sql/item.cc:3046
|
#3 0x00000000015ef5ab in Item_sum::get_tmp_table_item (this=<optimized out>, thd=0x62b00016c218) at /home/wx/mariadb-11.3.0/sql/item_sum.cc:563
|
#4 0x0000000000c2f1a9 in change_refs_to_tmp_fields (thd=<optimized out>, ref_pointer_array=..., res_selected_fields=..., res_all_fields=..., elements=1, all_fields=...) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:28691
|
#5 JOIN::make_aggr_tables_info (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:3798
|
#6 0x0000000000bfc660 in JOIN::optimize_stage2 (this=0x6290000ba0c8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:3438
|
#7 0x0000000000c13911 in JOIN::optimize_inner (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2650
|
#8 0x0000000000bfc156 in JOIN::optimize (this=0x6290000ba0c8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
|
#9 0x0000000000ab5421 in st_select_lex::optimize_unflattened_subqueries (this=<optimized out>, const_only=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_lex.cc:4916
|
#10 0x0000000000a688a6 in mysql_insert (thd=<optimized out>, table_list=0x6290000915e0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=<optimized out>, ignore=<optimized out>, result=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_insert.cc:875
|
#11 0x0000000000b36566 in mysql_execute_command (thd=0x62b00016c218, is_called_from_prepared_stmt=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:4417
|
#12 0x0000000000b1fe79 in mysql_parse (thd=thd@entry=0x62b00016c218, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7fffd242ca80) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
|
#13 0x0000000000b19069 in dispatch_command (command=<optimized out>, thd=0x62b00016c218, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893
|
#14 0x0000000000b20b71 in do_command (thd=0x62b00016c218, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
|
#15 0x0000000000f03476 in do_handle_one_connection (connect=<optimized out>, put_in_cache=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
|
#16 0x0000000000f02eb9 in handle_one_connection (arg=arg@entry=0x608001c51cb8) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
|
#17 0x0000000001a00c1b in pfs_spawn_thread (arg=0x617000005498) at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
|
#18 0x00007ffff79f7609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
|
#19 0x00007ffff770f133 in clone () from /lib/x86_64-linux-gnu/libc.so.6
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
This is probably related to MDEV-32410
CREATE TABLE t0 ( a int not null ) ; |
INSERT INTO t0 VALUES ( EXISTS ( SELECT avg(3) OVER ( ORDER BY COUNT( DISTINCT a , hex(a))) )) ; |
on 10.4 - ERROR 42S22: Unknown column 'a' in 'order clause'
but 10.5-11.2:
Version: '10.5.23-MariaDB-debug-log'
|
231103 15:43:55 [ERROR] mysqld got signal 11 ;
|
|
|
Server version: 10.5.23-MariaDB-debug-log source revision: b06ac9a8cd2146e89270cc2150d306d8ed1b33fb
|
|
|
sql/signal_handler.cc:241(handle_fatal_signal)[0x5617ed729dd8]
|
sigaction.c:0(__restore_rt)[0x7f962565b420]
|
sql/field.h:905(Field::type_std_attributes() const)[0x5617ed7e4370]
|
sql/item.cc:3103(Item_field::set_field(Field*))[0x5617ed79c922]
|
sql/item.cc:3012(Item_field::Item_field(THD*, Field*))[0x5617ed79b73d]
|
sql/item.h:3680(Item_temptable_field::Item_temptable_field(THD*, Field*))[0x5617ed09ac15]
|
sql/item_sum.cc:540(Item_sum::get_tmp_table_item(THD*))[0x5617ed9b1d99]
|
sql/sql_select.cc:26311(change_refs_to_tmp_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, List<Item>&, unsigned int, List<Item>&))[0x5617ed076463]
|
sql/sql_select.cc:3507(JOIN::make_aggr_tables_info())[0x5617ecfcb84d]
|
sql/sql_select.cc:3146(JOIN::optimize_stage2())[0x5617ecfc70d8]
|
sql/sql_select.cc:2389(JOIN::optimize_inner())[0x5617ecfbf37f]
|
sql/sql_select.cc:1721(JOIN::optimize())[0x5617ecfb854d]
|
sql/sql_lex.cc:4848(st_select_lex::optimize_unflattened_subqueries(bool))[0x5617ece854d8]
|
sql/sql_insert.cc:850(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x5617ece361a7]
|
sql/sql_parse.cc:4641(mysql_execute_command(THD*))[0x5617ecf02275]
|
sql/sql_parse.cc:8120(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5617ecf1a9bf]
|
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5617ecef03ed]
|
sql/sql_parse.cc:1375(do_command(THD*))[0x5617eceecd55]
|
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5617ed34b9a3]
|
sql/sql_connect.cc:1320(handle_one_connection)[0x5617ed34b307]
|
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5617edfd7b02]
|
nptl/pthread_create.c:478(start_thread)[0x7f962564f609]
|
|
|
Query (0x62b0000852a8): INSERT INTO t0 VALUES ( EXISTS ( SELECT avg(3) OVER ( ORDER BY COUNT( DISTINCT a , hex(a))) ))
|
MySQL commit seems to fix the issue:
commit 2e3dc09087c24798c90e05163ed3d931f6b93db3
|
Author: Benny Wang <benny.wang@oracle.com>
|
Date: Wed May 6 11:55:17 2015 +0200
|
|
|
Fixed Bug#20145024: WRONG RESULT FOR COUNT DISTINCT QUERY IN DERIVED TABLE
|
|
This bug is because there is logic error in Item_sum::get_tmp_table_item. It
|
should not set 'args' to pointer to the result_field of Item_sum but to its
|
own.
|
|
Before fixed bug#18766378, the Item_sum->result_field can be reset to NULL.
|
This is why the testcase of bug#20145024 went well in mysql-trunk. However,
|
after fixed bug#18766378, Item_sum->result_field can't be reset during
|
create_tmp_table. After change_refs_to_tmp_fields, the 'args' are repointed
|
to Aggregator_distinct::result_field because of the wrong logic. This results
|
in this bug.
|
|
Solution: Remove the Item_sum::get_tmp_table_item and use the default one.
|
This is because all the args of Item_sum are set automatically during
|
create_tmp_table. We don't need do this any more like what
|
Item_sum::get_tmp_table_item did.
|
|
sanja, can you please review bb-10.5-MDEV-32411? It is related to the code you committed 21 years ago...
For the release notes: "Resolved a crash which could occur for some statements with window functions employing an aggregation function in their ORDER BY part"
After the applied patch we still have:
MariaDB [test]> CREATE TABLE t1 (a int DEFAULT 2);
|
Query OK, 0 rows affected (0.023 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES (1), (2);
|
Query OK, 2 rows affected (0.005 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4);
|
Query OK, 1 row affected (0.004 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
+------+
|
3 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER ();
|
Query OK, 1 row affected (0.006 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
+------+
|
4 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4)));
|
Query OK, 1 row affected (0.005 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
+------+
|
5 rows in set (0.001 sec)
|
So far so good: everything is as expected. Yet for this query we have something quite unexpected:
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER ()));
|
Query OK, 1 row affected (0.004 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
+------+
|
6 rows in set (0.002 sec)
|
though we have:
MariaDB [test]> SELECT avg(4) OVER ();
|
+----------------+
|
| avg(4) OVER () |
|
+----------------+
|
| 4.0000 |
|
+----------------+
|
1 row in set (0.001 sec)
|
Let's go on. Here we don't see t1.a in selects:
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER (ORDER BY t1.a);
|
ERROR 1109 (42S02): Unknown table 't1' in order clause
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) ORDER BY t1.a;
|
ERROR 1109 (42S02): Unknown table 't1' in order clause
|
Yet if we wrap SELECT in VALUES we start seeing t1.a:
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) ORDER BY t1.a));
|
Query OK, 1 row affected (0.004 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
+------+
|
7 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER (ORDER BY t1.a)));
|
Query OK, 1 row affected (0.005 sec)
|
though the result for the last insert is unexpected :
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
+------+
|
8 rows in set (0.002 sec)
|
Let's continue.
MariaDB [test]> CREATE TABLE t2 (b int);
|
Query OK, 0 rows affected (0.022 sec)
|
MariaDB [test]> INSERT INTO t2 VALUES (2);
|
Query OK, 1 row affected (0.008 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER () FROM t2;
|
Query OK, 1 row affected (0.005 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
+------+
|
9 rows in set (0.009 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER (ORDER BY t1.a) FROM t2;
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'order clause'
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER () FROM t2));
|
Query OK, 1 row affected (0.007 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
+------+
|
10 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a)));
|
ERROR 1054 (42S22): Unknown column 'a' in 'order clause'
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a)))));
|
Query OK, 1 row affected (0.007 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
+------+
|
11 rows in set (0.002 sec)
|
Again for the last INSERT we see an unexpected result.
|
MariaDB [test]> INSERT INTO t1 VALUES(EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a)))));
|
Query OK, 1 row affected (0.007 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 1 |
|
+------+
|
12 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES(EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))));
|
Query OK, 1 row affected (0.006 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 1 |
|
| 1 |
|
+------+
|
13 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))));
|
Query OK, 1 row affected (0.003 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 1 |
|
| 1 |
|
| NULL |
|
+------+
|
14 rows in set (0.001 sec)
|
Above once more.
MariaDB [test]> INSERT INTO t1 SELECT * FROM t2 WHERE (SELECT avg(t1.a) FROM t1 t) > 0;
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'field list'
|
MariaDB [test]> INSERT INTO t1 VALUES(( SELECT * FROM t2 WHERE (SELECT avg(t1.a) FROM t1 t) > 0));
|
Query OK, 1 row affected (0.007 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 1 |
|
| 1 |
|
| NULL |
|
| 2 |
|
+------+
|
16 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (VALUES((SELECT avg(3) FROM t2))) dt) > 0));
|
Query OK, 1 row affected (0.006 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 1 |
|
| 1 |
|
| NULL |
|
| 2 |
|
| 2 |
|
+------+
|
17 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (VALUES((SELECT avg(t1.a) FROM t2))) dt) > 0));
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'field list'
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (SELECT avg(t1.a) FROM t2) dt) > 0));
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'field list'
|
For the build just before the pushed patch we have:
MariaDB [test]> CREATE TABLE t1 (a int DEFAULT 2);
|
Query OK, 0 rows affected (0.014 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES (1), (2);
|
Query OK, 2 rows affected (0.007 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4);
|
Query OK, 1 row affected (0.004 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
+------+
|
3 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER ();
|
Query OK, 1 row affected (0.003 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
+------+
|
4 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4)));
|
Query OK, 1 row affected (0.004 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
+------+
|
5 rows in set (0.003 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER ()));
|
Query OK, 1 row affected (0.005 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
+------+
|
6 rows in set (0.002 sec)
|
MariaDB [test]> SELECT avg(4) OVER ();
|
+----------------+
|
| avg(4) OVER () |
|
+----------------+
|
| 4.0000 |
|
+----------------+
|
1 row in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER (ORDER BY t1.a);
|
ERROR 1109 (42S02): Unknown table 't1' in order clause
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) ORDER BY t1.a;
|
ERROR 1109 (42S02): Unknown table 't1' in order clause
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) ORDER BY t1.a));
|
Query OK, 1 row affected (0.005 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
+------+
|
7 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER (ORDER BY t1.a)));
|
Query OK, 1 row affected (0.005 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
+------+
|
8 rows in set (0.002 sec)
|
MariaDB [test]> CREATE TABLE t2 (b int);
|
Query OK, 0 rows affected (0.018 sec)
|
MariaDB [test]> INSERT INTO t2 VALUES (2);
|
Query OK, 1 row affected (0.006 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER () FROM t2;
|
Query OK, 1 row affected (0.005 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
+------+
|
9 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER (ORDER BY t1.a) FROM t2;
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'order clause'
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER () FROM t2));
|
Query OK, 1 row affected (0.006 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
+------+
|
10 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a)));
|
ERROR 1054 (42S22): Unknown column 'a' in 'order clause'
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a)))));
|
Query OK, 1 row affected (0.005 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
+------+
|
11 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES(EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a)))));
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
MariaDB [test]> INSERT INTO t1 VALUES(EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))));
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))));
|
Query OK, 1 row affected (0.015 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| NULL |
|
+------+
|
12 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 SELECT * FROM t2 WHERE (SELECT avg(t1.a) FROM t1 t) > 0;
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'field list'
|
MariaDB [test]> INSERT INTO t1 VALUES(( SELECT * FROM t2 WHERE (SELECT avg(t1.a) FROM t1 t) > 0));
|
Query OK, 1 row affected (0.005 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| NULL |
|
| 2 |
|
+------+
|
13 rows in set (0.002 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (VALUES((SELECT avg(3) FROM t2))) dt) > 0));
|
Query OK, 1 row affected (0.004 sec)
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 4 |
|
| 4 |
|
| 4 |
|
| NULL |
|
| 4 |
|
| NULL |
|
| 4 |
|
| 4 |
|
| NULL |
|
| NULL |
|
| 2 |
|
| 2 |
|
+------+
|
14 rows in set (0.001 sec)
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (VALUES((SELECT avg(t1.a) FROM t2))) dt) > 0));
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'field list'
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (SELECT avg(t1.a) FROM t2) dt) > 0));
|
ERROR 1054 (42S22): Unknown column 't1.a' in 'field list'
|
The crash for the query is eliminated, however it was found that the query itself is not valid. I filed MDEV-35846 for addressing that.
Before the patch the same crash can be reproduced with the following test case as well:
CREATE TABLE t1 (a int DEFAULT 2); |
INSERT INTO t1 VALUES (1), (2); |
SELECT * FROM t1 WHERE EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))); |
The query in the last test case uses degenerated subquery with a window function with ORDER BY list that is eliminated. If we use a FROM clause for the subquery the query is executed without any problems returning the expected result:
MariaDB [test]> CREATE TABLE t2 (b int);
|
Query OK, 0 rows affected (0.019 sec)
|
|
|
MariaDB [test]> INSERT INTO t2 VALUES (4);
|
Query OK, 1 row affected (0.013 sec)
|
|
|
MariaDB [test]> SELECT * FROM t1 WHERE EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a))) FROM t2);
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.006 sec)
|
If we set a breakpoint in the function JOIN::make_aggr_tables_info() at the code:
/* Change sum_fields reference to calculated fields in tmp_table */
|
items1= ref_ptr_array_slice(2);
|
if ((sort_and_group || curr_tab->table->group ||
|
tmp_table_param.precomputed_group_by) &&
|
!implicit_grouping_without_tables)
|
{
|
if (change_to_use_tmp_fields(thd, items1,
|
tmp_fields_list1, tmp_all_fields1,
|
fields_list.elements, all_fields))
|
DBUG_RETURN(true);
|
}
|
else
|
{
|
if (change_refs_to_tmp_fields(thd, items1,
|
tmp_fields_list1, tmp_all_fields1,
|
fields_list.elements, all_fields))
|
DBUG_RETURN(true);
|
}
|
we see that for the query
SELECT * FROM t1 WHERE EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))); |
the 'else' branch with a call of change_refs_to_tmp_fields() is used while for the query
SELECT * FROM t1 WHERE EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a))) FROM t2); |
the 'then' branch is used with a call of change_to_use_tmp_fields()
Let's use the call of change_to_use_tmp_fields() for the first query with the following diff:
if ((sort_and_group || curr_tab->table->group ||
|
tmp_table_param.precomputed_group_by) &&
|
- !implicit_grouping_without_tables)
|
+ (!implicit_grouping_without_tables ||
|
+ (implicit_grouping_with_window_funcs &&
|
+ !select_lex->table_list.elements)))
|
{
|
if (change_to_use_tmp_fields(thd, items1,
|
After having applied this patch I have:
MariaDB [test]> SELECT * FROM t1 WHERE EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a))));
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.003 sec)
|
This is the expected result.
However for the query
SELECT * FROM t1 WHERE (SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))) = 3;
|
as well as for the query
SELECT * FROM t1 WHERE (SELECT avg(3) OVER ()) = 3; |
we don't have expected results:
MariaDB [test]> SELECT * FROM t1 WHERE (SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))) = 3;
|
Empty set (0.001 sec)
|
MariaDB [test]> SELECT * FROM t1 WHERE (SELECT avg(3) OVER ()) = 3;
|
Empty set (0.000 sec)
|
Indeed
MariaDB [test]> SELECT avg(3) OVER ();
|
+----------------+
|
| avg(3) OVER () |
|
+----------------+
|
| 3.0000 |
|
+----------------+
|
1 row in set (0.001 sec)
|
This because of another bug concerning degenerated subqueries with window functions noticed in the previous comments. This bug is reported in MDEV-35869. It makes sense to fix MDEV-35869 first.
The fix is pushed and released, so this issue is closed with specific fix versions. A followup is moved into MDEV-36021. Please, feel free to add more content to MDEV-36021 description
10.9-11.2, on 10.4-10.6 - ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OVER ( PARTITION BY c51 ORDER BY CASE c51 WHEN -107 THEN COUNT( DISTINCT c51 ...' at line 1
Version: '10.9.8-MariaDB-debug-log'231103 14:42:56 [ERROR] mysqld got signal 11 ;Server version: 10.9.8-MariaDB-debug-log source revision: 3e0009dc3a771e4dbf2fa4a4cf87e750453fb2ebsql/signal_handler.cc:241(handle_fatal_signal)[0x5649687c6c1c]sigaction.c:0(__restore_rt)[0x7f9cc6a76420]sql/field.h:906(Field::type_std_attributes() const)[0x56496887e47e]sql/item.cc:3129(Item_field::set_field(Field*))[0x564968838f56]sql/item.cc:3039(Item_field::Item_field(THD*, Field*))[0x564968837d98]sql/item_sum.cc:563(Item_sum::get_tmp_table_item(THD*))[0x564968a96894]sql/sql_select.cc:26901(change_refs_to_tmp_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, List<Item>&, unsigned int, List<Item>&))[0x564968096708]sql/sql_select.cc:3722(JOIN::make_aggr_tables_info())[0x564967fea150]sql/sql_select.cc:3362(JOIN::optimize_stage2())[0x564967fe5906]sql/sql_select.cc:2580(JOIN::optimize_inner())[0x564967fdd8e6]sql/sql_select.cc:1893(JOIN::optimize())[0x564967fd6639]sql/sql_lex.cc:4876(st_select_lex::optimize_unflattened_subqueries(bool))[0x564967e5edbd]sql/sql_insert.cc:866(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x564967e0e69d]sql/sql_parse.cc:4578(mysql_execute_command(THD*, bool))[0x564967edc529]sql/sql_parse.cc:8060(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x564967ef50d2]sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x564967ecacaf]sql/sql_parse.cc:1407(do_command(THD*, bool))[0x564967ec7a08]sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x56496838b597]sql/sql_connect.cc:1320(handle_one_connection)[0x56496838aef4]perfschema/pfs.cc:2203(pfs_spawn_thread)[0x564968fb9c78]nptl/pthread_create.c:478(start_thread)[0x7f9cc6a6a609]Query (0x6290001092a8): INSERT INTO t0 VALUES ( -83 ) , ( ATAN ( -89 LIKE EXISTS ( SELECT ROW_NUMBER ( ) OVER ( PARTITION BY c51 ORDER BY CASE c51 WHEN -107 THEN COUNT( DISTINCT c51 , + TRIM( TRAILING c51 FROM '/{;sxMhm&X$8fg7_ga#RG+7,>%)qs`b-Z7_\\><_k\'ML' ) NOT IN ( RAND ( ) NOT BETWEEN 69 AND -122 ) ) - - COS ( t0 . c51 ) ELSE 39 END IS TRUE ) NOT IN ( 107 , 15 , 57 ) AS c1 ) ) )