Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5450

Assertion `cached_field_ type == MYSQL_TYPE_STRING || ltime.time_type == MYSQL_TIMESTAMP_NONE || mysql_type_to_time_type(cached_field_type) == ltime.time_type' fails with IF, ISNULL, ADDDATE

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.6
    • 10.0.8
    • None
    • None

    Description

      CREATE TABLE t1 (a DATETIME, b DATE) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (NULL, '2012-12-21');
      		 
      SELECT IF( 1, ADDDATE( IFNULL( a, b ), 0 ), 1 ) FROM t1;
      		 

      10.0/sql/item_timefunc.cc:1516: virtual String* Item_temporal_hybrid_func::val_str_ascii(String*): Assertion `cached_field_type == MYSQL_TYPE_STRING || ltime.time_type == MYSQL_TIMESTAMP_NONE || mysql_type_to_time_type(cached_field_type) == ltime.time_type' failed.
      		 
      131215  2:13:32 [ERROR] mysqld got signal 6 ;
      		 

      #6  0x00007fc0187c1621 in *__GI___assert_fail (assertion=0x10b5c08 "cached_field_type == MYSQL_TYPE_STRING || ltime.time_type == MYSQL_TIMESTAMP_NONE || mysql_type_to_time_type(cached_field_type) == ltime.time_type", file=<optimized out>, line=1516, function=0x10b6e20 "virtual String* Item_temporal_hybrid_func::val_str_ascii(String*)") at assert.c:81
      		 
      #7  0x0000000000904fb3 in Item_temporal_hybrid_func::val_str_ascii (this=0x7fc00fb575b0, str=0x7fc00fb57758) at 10.0/sql/item_timefunc.cc:1514
      		 
      #8  0x00000000008cc3f4 in Item_func::val_str_from_val_str_ascii (this=0x7fc00fb575b0, str=0x7fc00fb57758, str2=0x7fc00fb57680) at 10.0/sql/item_strfunc.cc:88
      		 
      #9  0x000000000090b34e in Item_temporal_hybrid_func::val_str (this=0x7fc00fb575b0, str=0x7fc00fb57758) at 10.0/sql/item_timefunc.h:553
      		 
      #10 0x0000000000882cf4 in Item_func_if::str_op (this=0x7fc00fb57740, str=0x7fc00fb57758) at 10.0/sql/item_cmpfunc.cc:2729
      		 
      #11 0x00000000008a99d0 in Item_func_hybrid_result_type::val_str (this=0x7fc00fb57740, str=0x7fc019fd2f50) at 10.0/sql/item_func.cc:940
      		 
      #12 0x000000000086a019 in Item::send (this=0x7fc00fb57740, protocol=0x7fc011bec5f8, buffer=0x7fc019fd2f50) at 10.0/sql/item.cc:6464
      		 
      #13 0x00000000005bfbdc in Protocol::send_result_set_row (this=0x7fc011bec5f8, row_items=0x7fc011bf0528) at 10.0/sql/protocol.cc:900
      		 
      #14 0x000000000062b3bd in select_send::send_data (this=0x7fc00fb57f68, items=...) at 10.0/sql/sql_class.cc:2549
      		 
      #15 0x00000000006c5503 in end_send (join=0x7fc00fb57f88, join_tab=0x0, end_of_records=false) at 10.0/sql/sql_select.cc:18447
      		 
      #16 0x00000000006c2201 in do_select (join=0x7fc00fb57f88, fields=0x7fc011bf0528, table=0x0, procedure=0x0) at 10.0/sql/sql_select.cc:16952
      		 
      #17 0x000000000069fec2 in JOIN::exec_inner (this=0x7fc00fb57f88) at 10.0/sql/sql_select.cc:3064
      		 
      #18 0x000000000069d3e6 in JOIN::exec (this=0x7fc00fb57f88) at 10.0/sql/sql_select.cc:2354
      		 
      #19 0x00000000006a0755 in mysql_select (thd=0x7fc011bec070, rref_pointer_array=0x7fc011bf0688, tables=0x7fc00fb578e8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc00fb57f68, unit=0x7fc011befd38, select_lex=0x7fc011bf0418) at 10.0/sql/sql_select.cc:3292
      		 
      #20 0x0000000000696e43 in handle_select (thd=0x7fc011bec070, lex=0x7fc011befc78, result=0x7fc00fb57f68, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:371
      		 
      #21 0x000000000066bfa8 in execute_sqlcom_select (thd=0x7fc011bec070, all_tables=0x7fc00fb578e8) at 10.0/sql/sql_parse.cc:5221
      		 
      #22 0x0000000000664468 in mysql_execute_command (thd=0x7fc011bec070) at 10.0/sql/sql_parse.cc:2552
      		 
      #23 0x000000000066e728 in mysql_parse (thd=0x7fc011bec070, rawbuf=0x7fc00fb57088 "SELECT IF( 1, ADDDATE( IFNULL( a, b ), 0 ), 1 ) FROM t1", length=55, parser_state=0x7fc019fd4630) at 10.0/sql/sql_parse.cc:6365
      		 
      #24 0x000000000066166a in dispatch_command (command=COM_QUERY, thd=0x7fc011bec070, packet=0x7fc01273d071 "", packet_length=55) at 10.0/sql/sql_parse.cc:1296
      		 
      #25 0x0000000000660a0c in do_command (thd=0x7fc011bec070) at 10.0/sql/sql_parse.cc:993
      		 
      #26 0x0000000000779955 in do_handle_one_connection (thd_arg=0x7fc011bec070) at 10.0/sql/sql_connect.cc:1379
      		 
      #27 0x00000000007796a8 in handle_one_connection (arg=0x7fc011bec070) at 10.0/sql/sql_connect.cc:1293
      		 
      #28 0x0000000000a9aac5 in pfs_spawn_thread (arg=0x7fc012ff5df0) at 10.0/storage/perfschema/pfs.cc:1853
      		 
      #29 0x00007fc019d21b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
      		 
      #30 0x00007fc018870a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
      		 

      revision-id: bar@mnogosearch.org-20131213131810-eejuqpcvgcd8x41b
      		 
      revno: 3945
      		 
      branch-nick: 10.0

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov added a comment -

          The problem is also repeatable with this SQL script:

          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a DATETIME, b DATE) ENGINE=MyISAM;
          INSERT INTO t1 VALUES (NULL, '2012-12-21');
          SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;

          bar Alexander Barkov added a comment - The problem is also repeatable with this SQL script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATETIME, b DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL, '2012-12-21'); SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;
          bar Alexander Barkov added a comment -

          Also repeatable using the TIME column type for b:

          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM;
          INSERT INTO t1 VALUES (NULL, '2012-12-21');
          SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;

          bar Alexander Barkov added a comment - Also repeatable using the TIME column type for b: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL, '2012-12-21'); SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1;
          bar Alexander Barkov added a comment -

          Repeatable using COALESCE:

          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM;
          INSERT INTO t1 VALUES (NULL, '2012-12-21');
          SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;

          bar Alexander Barkov added a comment - Repeatable using COALESCE: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL, '2012-12-21'); SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1;
          bar Alexander Barkov added a comment -

          Repeatable using CASE:
          DROP TABLE IF EXISTS t1;
          CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM;
          INSERT INTO t1 VALUES (NULL, '2012-12-21');
          SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;

          bar Alexander Barkov added a comment - Repeatable using CASE: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL, '2012-12-21'); SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
          bar Alexander Barkov added a comment - - edited

          The problem described by some of the additional tests with "b TIME"
          (i.e. not the originally reported test with "b DATE")
          is actually repeatable with 5.3 and 5.5, but with a wrong result instead of the crash:

          DROP TABLE IF EXISTS t1, t2;
          		 
          CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM;
          		 
          INSERT INTO t1 VALUES (NULL, '2012-12-21');
          		 
          SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1;
          		 
          CREATE TABLE t2 AS SELECT ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) FROM t1;
          		 
          SELECT * FROM t2;

          The first SELECT returns:

          +--------------------------------------------------------+
          		 
          | CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) |
          		 
          +--------------------------------------------------------+
          		 
          | 00:20:12                                               |
          		 
          +--------------------------------------------------------+

          The second SELECT returns:

          +---------------------+
          		 
          | addtime             |
          		 
          +---------------------+
          		 
          | 0000-00-00 00:20:12 |
          		 
          +---------------------+

          None of them are correct.
          The expected result is NULL, as in this query:

          mysql> SELECT ADDTIME(TIMESTAMP(TIME('00:20:12')),0);
          		 
          +----------------------------------------+
          		 
          | ADDTIME(TIMESTAMP(TIME('00:20:12')),0) |
          		 
          +----------------------------------------+
          		 
          | NULL                                   |
          		 
          +----------------------------------------+
          		 
          1 row in set (2.21 sec)

          where '00:20:12' is the value of the TIME column t1.b
          after inserting the string literal '2012-12-21' into it.

          bar Alexander Barkov added a comment - - edited The problem described by some of the additional tests with "b TIME" (i.e. not the originally reported test with "b DATE") is actually repeatable with 5.3 and 5.5, but with a wrong result instead of the crash: DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a DATETIME, b TIME) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL, '2012-12-21'); SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; CREATE TABLE t2 AS SELECT ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) FROM t1; SELECT * FROM t2; The first SELECT returns: +--------------------------------------------------------+ | CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) | +--------------------------------------------------------+ | 00:20:12 | +--------------------------------------------------------+ The second SELECT returns: +---------------------+ | addtime | +---------------------+ | 0000-00-00 00:20:12 | +---------------------+ None of them are correct. The expected result is NULL, as in this query: mysql> SELECT ADDTIME(TIMESTAMP(TIME('00:20:12')),0); +----------------------------------------+ | ADDTIME(TIMESTAMP(TIME('00:20:12')),0) | +----------------------------------------+ | NULL | +----------------------------------------+ 1 row in set (2.21 sec) where '00:20:12' is the value of the TIME column t1.b after inserting the string literal '2012-12-21' into it.
          bar Alexander Barkov added a comment -

          Pushed into 10.0-base (10.0.8)

          bar Alexander Barkov added a comment - Pushed into 10.0-base (10.0.8)
          bar Alexander Barkov added a comment -

          A correction: pushed into 10.0 (10.0.8)

          bar Alexander Barkov added a comment - A correction: pushed into 10.0 (10.0.8)

          People

            bar Alexander Barkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.