MySQL Bugs: #51980: mysqld service crashes with a simple COUNT(DISTINCT) query over a view

Bug #51980	mysqld service crashes with a simple COUNT(DISTINCT) query over a view
Submitted:	12 Mar 2010 9:44	Modified:	8 May 2010 22:13
Reporter:	Joel TURCHINI	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.5.1-m2-community, 5.6.99, 6.0.14	OS:	Any (MS Windows w7 64b, Linux)
Assigned to:	Øystein Grøvlen	CPU Architecture:	Any
Tags:	mysqld, regression

Description:
Hi,

My mysqld service crashes when I run a simple but specific query on a view (apart from this issue, all works fine).

How to reproduce:

create a table like this (maybe it crashes with a smaller definition, but I haven't tested yet):
CREATE TABLE `table_1` (
  `col_1` int(10) unsigned NOT NULL DEFAULT '0',
  `col_2` int(10) unsigned DEFAULT NULL,
  `col_3` datetime DEFAULT NULL,
  `col_4` datetime DEFAULT NULL,
  `col_5` int(10) unsigned NOT NULL,
  `col_6` varchar(10) NOT NULL,
  `col_7` varchar(10) DEFAULT NULL,
  `col_8` int(10) unsigned NOT NULL,
  `col_9` int(10) unsigned NOT NULL
)

Create a simple view on this table (with all columns):
CREATE VIEW vw_table_1 as select col_1, col2,... from table_1

You can add (or not) add a row to table_1 (to have data).

If you run:
SELECT COUNT(DISTINCT col_1) FROM table_1
all works fine.

If you run:
SELECT COUNT(DISTINCT col_1) FROM vw_table_1
mysqld crashes...

Here are error log extract:
100312 10:08:18 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=327155712
read_buffer_size=65536
max_used_connections=1
max_threads=160
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 372130 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x48aab010
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
000000014019AFA9    mysqld.exe!add_group_and_distinct_keys()[sql_select.cc:4146]
000000014019BF60    mysqld.exe!make_join_statistics()[sql_select.cc:2918]
000000014019CAA4    mysqld.exe!JOIN::optimize()[sql_select.cc:983]
000000014019F225    mysqld.exe!mysql_select()[sql_select.cc:2455]
000000014019F696    mysqld.exe!handle_select()[sql_select.cc:272]
0000000140066020    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5195]
0000000140068A4D    mysqld.exe!mysql_execute_command()[sql_parse.cc:2288]
000000014006D4C6    mysqld.exe!mysql_parse()[sql_parse.cc:6216]
000000014006E116    mysqld.exe!dispatch_command()[sql_parse.cc:1242]
000000014006EE1B    mysqld.exe!do_command()[sql_parse.cc:873]
0000000140096007    mysqld.exe!handle_one_connection()[sql_connect.cc:1154]
00000001403424CE    mysqld.exe!pthread_start()[my_winthread.c:63]
00000001403B37C7    mysqld.exe!_callthreadstartex()[threadex.c:348]
00000001403B389F    mysqld.exe!_threadstartex()[threadex.c:326]
0000000076CCF56D    kernel32.dll!BaseThreadInitThunk()
0000000076E03281    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000048AB5460=SELECT COUNT(DISTINCT col_1) FROM vw_table_1
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

I try another query on real data:
SELECT produitventeid FROM vw_produitvente GROUP BY produitventeid
and mysqld crashed also, but it works with the given sample (table_1).

Trace in the error log is quite the same, except for the stack:
000000014020249A    mysqld.exe!get_best_group_min_max()[opt_range.cc:9676]
0000000140204B12    mysqld.exe!SQL_SELECT::test_quick_select()[opt_range.cc:2385]
00000001401815A9    mysqld.exe!get_quick_record_count()[sql_select.cc:2517]
000000014019BFE9    mysqld.exe!make_join_statistics()[sql_select.cc:2929]
000000014019CAA4    mysqld.exe!JOIN::optimize()[sql_select.cc:983]
000000014019F225    mysqld.exe!mysql_select()[sql_select.cc:2455]
000000014019F696    mysqld.exe!handle_select()[sql_select.cc:272]
0000000140066020    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5195]
0000000140068A4D    mysqld.exe!mysql_execute_command()[sql_parse.cc:2288]
000000014006D4C6    mysqld.exe!mysql_parse()[sql_parse.cc:6216]
000000014006E116    mysqld.exe!dispatch_command()[sql_parse.cc:1242]
000000014006EE1B    mysqld.exe!do_command()[sql_parse.cc:873]
0000000140096007    mysqld.exe!handle_one_connection()[sql_connect.cc:1154]
00000001403424CE    mysqld.exe!pthread_start()[my_winthread.c:63]
00000001403B37C7    mysqld.exe!_callthreadstartex()[threadex.c:348]
00000001403B389F    mysqld.exe!_threadstartex()[threadex.c:326]
0000000076CCF56D    kernel32.dll!BaseThreadInitThunk()
0000000076E03281    ntdll.dll!RtlUserThreadStart()

Here are my workstation details:
mySQL version: 5.5.1-m2-community (64b version), InnoDB database/engine
OS: W7 64b
harware: Dell E6400 / 4GB

Maybe I do something wrong, but what?
Thx in advance.

How to repeat:
How to reproduce:

create a table like this (maybe it crashes with a smaller definition, but I haven't tested yet):
CREATE TABLE `table_1` (
  `col_1` int(10) unsigned NOT NULL DEFAULT '0',
  `col_2` int(10) unsigned DEFAULT NULL,
  `col_3` datetime DEFAULT NULL,
  `col_4` datetime DEFAULT NULL,
  `col_5` int(10) unsigned NOT NULL,
  `col_6` varchar(10) NOT NULL,
  `col_7` varchar(10) DEFAULT NULL,
  `col_8` int(10) unsigned NOT NULL,
  `col_9` int(10) unsigned NOT NULL
)

Create a simple view on this table (with all columns):
CREATE VIEW vw_table_1 as select col_1, col2,... from table_1

You can add (or not) add a row to table_1 (to have data).

If you run:
SELECT COUNT(DISTINCT col_1) FROM table_1
all works fine.

If you run:
SELECT COUNT(DISTINCT col_1) FROM vw_table_1
mysqld crashes...

Thank you for the probloem report. Please, check with a newer version, 5.5.2-m2, and inform about the results. 

With current code I do not see any crash:

openxs@ubuntu:/home2/openxs/dbs/trunk$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.3-m3-debug Source distribution

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

mysql> CREATE TABLE `table_1` (
    ->   `col_1` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `col_2` int(10) unsigned DEFAULT NULL,
    ->   `col_3` datetime DEFAULT NULL,
    ->   `col_4` datetime DEFAULT NULL,
    ->   `col_5` int(10) unsigned NOT NULL,
    ->   `col_6` varchar(10) NOT NULL,
    ->   `col_7` varchar(10) DEFAULT NULL,
    ->   `col_8` int(10) unsigned NOT NULL,
    ->   `col_9` int(10) unsigned NOT NULL
    -> )
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE VIEW vw_table_1 as select col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9 from table_1
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(DISTINCT col_1) FROM table_1
    -> ;
+-----------------------+
| COUNT(DISTINCT col_1) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT col_1) FROM vw_table_1;
+-----------------------+
| COUNT(DISTINCT col_1) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

Hi,

I upgrape mySQL to 5.5.2, and have the same issue...

100315  0:14:49 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use Windows interlocked functions
100315  0:14:49  InnoDB: highest supported file format is Barracuda.
100315  0:14:50 InnoDB Plugin 1.0.6 started; log sequence number 1976920412
100315  0:14:50 [Note] Event Scheduler: Loaded 0 events
100315  0:14:50 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.2-m2-community'  socket: ''  port: 3306  MySQL Community Server (GPL)
100315  0:18:30 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=327155712
read_buffer_size=65536
max_used_connections=1
max_threads=160
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 372130 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x48ebaed0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
000000014019B809    mysqld.exe!add_group_and_distinct_keys()[sql_select.cc:4160]
000000014019C7C0    mysqld.exe!make_join_statistics()[sql_select.cc:2932]
000000014019D30A    mysqld.exe!JOIN::optimize()[sql_select.cc:997]
000000014019FA95    mysqld.exe!mysql_select()[sql_select.cc:2469]
000000014019FF06    mysqld.exe!handle_select()[sql_select.cc:272]
0000000140066560    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5195]
0000000140068F8D    mysqld.exe!mysql_execute_command()[sql_parse.cc:2290]
000000014006D956    mysqld.exe!mysql_parse()[sql_parse.cc:6216]
000000014006E5A6    mysqld.exe!dispatch_command()[sql_parse.cc:1244]
000000014006F2AB    mysqld.exe!do_command()[sql_parse.cc:875]
00000001400964B7    mysqld.exe!handle_one_connection()[sql_connect.cc:1154]
0000000140342FDE    mysqld.exe!pthread_start()[my_winthread.c:63]
00000001403B4467    mysqld.exe!_callthreadstartex()[threadex.c:348]
00000001403B453F    mysqld.exe!_threadstartex()[threadex.c:326]
0000000077AEF56D    kernel32.dll!BaseThreadInitThunk()
0000000077C23281    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000048EC5490=select count(distinct col_1) from vw_table_1
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

I note you made a try with a linux version of mySQL: does it works on a windows one (64b on W7) on your side?

Thanks for your answer,
Joe

Thank you for the feedback.

Verified as described. Crash only repeatable if use storage engine InnoDB.

Test case for MTR:

--source include/have_innodb.inc

CREATE TABLE `table_1` (
  `col_1` int(10) unsigned NOT NULL DEFAULT '0',
  `col_2` int(10) unsigned DEFAULT NULL,
  `col_3` datetime DEFAULT NULL,
  `col_4` datetime DEFAULT NULL,
  `col_5` int(10) unsigned NOT NULL,
  `col_6` varchar(10) NOT NULL,
  `col_7` varchar(10) DEFAULT NULL,
  `col_8` int(10) unsigned NOT NULL,
  `col_9` int(10) unsigned NOT NULL
) engine=innodb;

CREATE VIEW vw_table_1 as select col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9 from table_1;

SELECT COUNT(DISTINCT col_1) FROM table_1;
SELECT COUNT(DISTINCT col_1) FROM vw_table_1;

Manyi,

sorry: forgot to set "regression" tag. Bug is not repeatable with 5.1 and 5.0

mysql-6.0-codebase-bugfixing fails as well

This regression was introduced by revid:joro@sun.com-20090928072125-cbx5j0v2oe5d7av6

Commit comment:
Ported WL#3220 to mysql-next-mr.

Reduced test case:

--source include/have_innodb.inc
CREATE TABLE t1 (i INTEGER) ENGINE=innodb;
CREATE VIEW v1 AS SELECT * FROM t1; 
SELECT COUNT(DISTINCT i) FROM v1;

This change in is_indexed_agg_distinct() fixes the test case:
-        out_args->push_back((Item_field *) expr);
+        out_args->push_back((Item_field *) expr->real_item());

The issue is also reproducible with MyIsam if table contains at least two records.
(With an empty table, const table optimization is used for MyIsam, and the 
problematic code will not be executed.)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103994

3006 oystein.grovlen@sun.com	2010-03-22
      Bug#51980 mysqld service crashes with a simple COUNT(DISTINCT) query over a view
      
      Problem: Segmentation fault in add_group_and_distinct_keys() when accessing
      field of what is assumed to be an Item_field object.
      
      Cause: In case of views, the item added to list by is_indexed_agg_distinct() 
      was not of type Item_field, but Item_ref.
      
      Resolution:  Add the real Item_field object, the one referred to by 
      Item_ref object, to the list, instead.
     @ mysql-test/r/count_distinct.result
        Results for test case for Bug#51980.
     @ mysql-test/t/count_distinct.test
        Test case for Bug#51980.
        Table needs to contain at least two rows to avoid const table optimization.
     @ sql/sql_select.cc
        Make sure it is the actual Item_field object that is pushed to the out_args
        list of is_indexed_agg_distinct(), and not Item_ref objects.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105469

3010 oystein.grovlen@sun.com	2010-03-22
      Bug#51980 mysqld service crashes with a simple COUNT(DISTINCT) query over a view
      
      Problem: Segmentation fault in add_group_and_distinct_keys() when accessing
      field of what is assumed to be an Item_field object.
      
      Cause: In case of views, the item added to list by is_indexed_agg_distinct() 
      was not of type Item_field, but Item_ref.
      
      Resolution:  Add the real Item_field object, the one referred to by 
      Item_ref object, to the list, instead.
     @ mysql-test/r/count_distinct.result
        Results for test case for Bug#51980.
     @ mysql-test/t/count_distinct.test
        Test case for Bug#51980.
        Table needs to contain at least two rows to avoid const table optimization.
     @ sql/sql_select.cc
        Make sure it is the actual Item_field object that is pushed to the out_args
        list of is_indexed_agg_distinct(), and not Item_ref objects.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105479

3010 oystein.grovlen@sun.com	2010-04-13
      Bug#51980 mysqld service crashes with a simple COUNT(DISTINCT) query over a view
      
      Problem: Segmentation fault in add_group_and_distinct_keys() when accessing
      field of what is assumed to be an Item_field object.
      
      Cause: In case of views, the item added to list by is_indexed_agg_distinct() 
      was not of type Item_field, but Item_ref.
      
      Resolution:  Add the real Item_field object, the one referred to by 
      Item_ref object, to the list, instead.
     @ mysql-test/r/count_distinct.result
        Results for test case for Bug#51980.
     @ mysql-test/t/count_distinct.test
        Test case for Bug#51980.
        Table needs to contain at least two rows to avoid const table optimization.
     @ sql/sql_select.cc
        Make sure it is the actual Item_field object that is pushed to the out_args
        list of is_indexed_agg_distinct(), and not Item_ref objects.

Patch pushed into mysql-trunk-bugfixing with revision-id oystein.grovlen@sun.com-20100413093828-izfu3gsjltwgjrr0

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

A COUNT(DISTINCT) query on a view could cause a server crash.