MySQL Bugs: #19490: View on a date column with calculated INTERVAL crashes the server

Bug #19490	View on a date column with calculated INTERVAL crashes the server
Submitted:	2 May 2006 20:17	Modified:	15 May 2006 19:42
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.21 and 5.1.10	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
A view with a GROUP BY clause, where one of the grouping columns is a value which is being calculated from a date and an interval coming from a function, will crash the server.

 use test;
Database changed

 select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.21-standard-log |
+---------------------+
1 row in set (0.00 sec)

 drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

 drop view if exists v1;
Query OK, 0 rows affected (0.03 sec)

 create table t1 (
    ->     id int not null primary key,
    ->     d datetime
    -> );
Query OK, 0 rows affected (0.09 sec)

 create view v1 as
    -> select id, date(d) + interval time_to_sec(d) second as t, count(*)
    -> from t1
    -> group by id, t;
Query OK, 0 rows affected (0.00 sec)

 show create view v1\G
ERROR 2013 (HY000): Lost connection to MySQL server during query

Notice that this error show only in the latest versions (5.0.21 and 5.1.10). It is NOT present in the previous versions (5.0.20 and 5.1.9).

How to repeat:
use test;

select version();
drop table if exists t1;
drop view if exists v1;

create table t1 (
    id int not null primary key,
    d datetime
);

create view v1 as
select id, date(d) + interval time_to_sec(d) second as t, count(*)
from t1
group by id, t;

show create view v1\G

Suggested fix:
none.

could not reproduce this with current  bk sources, now trying 5.0.21 release source

I can confirm this crask with Windows 5.0.21 released server. Will
test with source.

Thank you for the bug report.

miguel@hegel:~/dbs/mysql-5.0> bk changes | head
ChangeSet@1.2117, 2006-05-02 21:47:53+02:00, pekka@mysql.com
  pekka:get - push push push

ChangeSet@1.2112.1.1, 2006-05-02 13:42:35-04:00, cmiller@zippy.(none)
  An update to as-yet unused new feature of snprintf, which was added to bring 
  our sprintf()-alike in sync with our fprintf()-alike features.

ChangeSet@1.2114, 2006-05-02 09:19:54+02:00, knielsen@mysql.com
  Fix a bunch of non-Linux compile failures.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> 
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.22-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop view if exists v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.10-beta-debug | 
+-------------------+
1 row in set (0.01 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop view if exists v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> create table t1 (
    ->     id int not null primary key,
    ->     d datetime
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create view v1 as
    -> select id, date(d) + interval time_to_sec(d) second as t, count(*)
    -> from t1
    -> group by id, t;
Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> create table t1 (
    ->     id int not null primary key,
    ->     d datetime
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> create view v1 as
    -> select id, date(d) + interval time_to_sec(d) second as t, count(*)
    -> from t1
    -> group by id, t;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show create view v1\G
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

[New Thread 1129405360 (LWP 30196)]
060504 11:44:49 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.22-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1129606064 (LWP 30198)]
mysqld: item_timefunc.cc:1103: virtual longlong Item_func_time_to_sec::val_int(): Assertion `fixed == 1' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1129606064 (LWP 30198)]
0xffffe410 in __kernel_vsyscall ()
(gdb) bt full
#0  0xffffe410 in __kernel_vsyscall ()
No symbol table info available.
#1  0x4021d541 in raise () from /lib/tls/libc.so.6
No symbol table info available.
#2  0x4021edbb in abort () from /lib/tls/libc.so.6
No symbol table info available.
#3  0x40216925 in __assert_fail () from /lib/tls/libc.so.6
No symbol table info available.
#4  0x081a5914 in Item_func_time_to_sec::val_int (this=0x8e91dc0) at item_timefunc.cc:1103
        ltime = {year = 142497856, month = 1129597512, day = 0, hour = 149360392, minute = 149493936, second = 149494400, second_part = 0, 
  neg = 100 'd', time_type = 142379392}
        seconds = 602947168207391064
        __PRETTY_FUNCTION__ = "virtual longlong Item_func_time_to_sec::val_int()"
#5  0x081a5a04 in get_interval_value (args=0x8e91dc0, int_type=INTERVAL_SECOND, str_value=0x43544a1c, interval=0x435449f0)

I managed to build a simpler test case where this problem could be demonstrated:

mysql> CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
Query OK, 0 rows affected (0.27 sec)

mysql> INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
    ->                       (3,1), (5,1), (8,9), (2,2), (0,9);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t2 VALUES
    ->  (1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),
    ->  (5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),
    ->  (0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);
Query OK, 18 rows affected (0.00 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql>
mysql> EXPLAIN
    -> SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
+----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY,b     | b    | 5       | NULL      |    3 | Using where |
|  1 | SIMPLE      | t2    | ref   | c             | c    | 5       | test.t1.a |    2 | Using where |
+----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY,b     | b    | 5       | NULL |    3 | Using where |
|  1 | SIMPLE      | t2    | ALL   | c             | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Please disregard the previous comment. It was for bug #18940.

The following simple query also can cause an assertion abort:

SELECT * FROM v1;

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/6329

ChangeSet
  1.2133 06/05/12 18:24:38 igor@rurik.mysql.com +3 -0
  Fixed bug #19490. The bug that caused server crash manifested
  itself when executing queries referring to a view with GROUP BY
  an expression containing non-constant interval.
  It happened because Item_date_add_interval::eq neglected the
  fact that the method can be applied to an expression of the form
      date(col) + interval time_to_sec(col) second
  at the time when col could not be evaluated yet.
  An attempt to evaluate time_to_sec(col) in this method resulted
  in a crash.

The fix will appear in 5.0.22 and 5.1.10

Noted in 5.0.22, 5.1.10 changelogs.

Selecting from a view that used <literal>GROUP BY</literal> on
a non-constant temporal interval (such as
<literal>DATE(<replaceable>col</replaceable>) + INTERVAL
TIME_TO_SEC(<replaceable>col</replaceable>) SECOND</literal>
could cause a server crash.

Bug: http://bugs.mysql.com/bug.php?id=19832 was marked as
duplicate of this one.