bug: Convert DATETIME to TIME type error

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

CONVERT(expr,type) function return result set error,
the wrong result is as follows:

mysql> SELECT CONVERT(c, TIME) FROM t1;
+------------------+
| CONVERT(c, TIME) |
+------------------+
| NULL             |
| 00:00:00         |
| 13:13:13         |
| 00:00:00         |
| 15:15:15         |
+------------------+
5 rows in set (0.12 sec)

Expected behavior

mysql> SELECT CONVERT(c, TIME) FROM t1;
+------------------+
| CONVERT(c, TIME) |
+------------------+
| 00:00:00         |
| 00:00:00         |
| 13:13:13         |
| 00:00:00         |
| 15:15:15         |
+------------------+
5 rows in set (0.01 sec)

How To Reproduce

set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (a CHAR(1), b INT, c DATETIME, d DOUBLE);
INSERT INTO t1 VALUES ('', NULL, '0-0-0', NULL),
('a', 12, '1212-12-12', 1.19691E+100),
('b', 13, '1313-3-13 13:13:13', 2.1961E+18),
('c', 14, '1414-4-14', 0.16191),
('d', 15, '2015-5-15 15:15:15', 1.971917);

SELECT CONVERT(c, TIME) FROM t1;

Environment

[root@HAST04 ~]# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 7f69d33
        Last commit time: Date:   Tue Nov 22 06:31:04 2022 +0000
        Build time: Date: Tue Nov 22 16:53:27 CST 2022

[root@HAST04 ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[lujiashun]

ACK

[lujiashun]

(gdb) bt
#0  Item::Item (this=0x7f5704000b60) at /data/codebase/stonedb57/stonedb/sql/item.cc:595
#1  0x00005605e33e7023 in Item_result_field::Item_result_field (this=0x7f57048f4400, pos=...) at /data/codebase/stonedb57/stonedb/sql/item.h:4069
#2  0x00005605e33e7137 in Item_func::Item_func (this=0x7f57048f4400, pos=..., a=0x7f57048f4348) at /data/codebase/stonedb57/stonedb/sql/item_func.h:108
#3  0x00005605e33ee491 in Item_temporal_func::Item_temporal_func (this=0x7f57048f4400, pos=..., a=0x7f57048f4348) at /data/codebase/stonedb57/stonedb/sql/item_timefunc.h:537
#4  0x00005605e33ee7fb in Item_time_func::Item_time_func (this=0x7f57048f4400, pos=..., a=0x7f57048f4348) at /data/codebase/stonedb57/stonedb/sql/item_timefunc.h:770
#5  0x00005605e33eef1a in Item_time_typecast::Item_time_typecast (this=0x7f57048f4400, pos=..., a=0x7f57048f4348, dec_arg=0 '\000')
    at /data/codebase/stonedb57/stonedb/sql/item_timefunc.h:1526
#6  0x00005605e33e26f9 in create_func_cast (thd=0x7f5704000e10, pos=..., a=0x7f57048f4348, type=0x7f5c2c866540) at /data/codebase/stonedb57/stonedb/sql/item_create.cc:8002
#7  0x00005605e3be1e5e in MYSQLparse (YYTHD=0x7f5704000e10) at /data/codebase/stonedb57/stonedb/sql/sql_yacc.yy:9609
#8  0x00005605e3a81cfc in parse_sql (thd=0x7f5704000e10, parser_state=0x7f5c2c867630, creation_ctx=0x0) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:7212
#9  0x00005605e3a7e3b3 in mysql_parse (thd=0x7f5704000e10, parser_state=0x7f5c2c867630) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:5535
#10 0x00005605e3a733b3 in dispatch_command (thd=0x7f5704000e10, com_data=0x7f5c2c867ee0, command=COM_QUERY) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:1495
#11 0x00005605e3a7222a in do_command (thd=0x7f5704000e10) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:1034
#12 0x00005605e3bbab5c in handle_connection (arg=0x5605eb28c810) at /data/codebase/stonedb57/stonedb/sql/conn_handler/connection_handler_per_thread.cc:313
#13 0x00005605e4306dbd in pfs_spawn_thread (arg=0x5605eb0053e0) at /data/codebase/stonedb57/stonedb/storage/perfschema/pfs.cc:2197
#14 0x00007f5c3343f609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#15 0x00007f5c33213133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

[lujiashun]

(gdb) bt
#0  Item_time_typecast::get_time (
    this=0x5605e45a8083 <std::shared_ptr<Tianmu::core::ValueOrNull>::shared_ptr<std::allocator<Tianmu::core::ValueOrNull>>(std::_Sp_alloc_shared_tag<std::allocator<Tianmu::core::ValueOrNull> >)+39>, ltime=0x7f5c2c865540) at /data/codebase/stonedb57/stonedb/sql/item_timefunc.cc:2671
#1  0x00005605e3381444 in Item::val_string_from_time (this=0x7f57048f4400, str=0x7f5c2c865620) at /data/codebase/stonedb57/stonedb/sql/item.cc:326
#2  0x00005605e392882f in Item_time_func::val_str (this=0x7f57048f4400, str=0x7f5c2c865620) at /data/codebase/stonedb57/stonedb/sql/item_timefunc.h:796
#3  0x00005605e47ae21a in Tianmu::core::MysqlExpression::ItemString2ValueOrNull (item=0x7f57048f4400, maxstrlen=17, a_type=Tianmu::common::ColumnType::TIME)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/core/mysql_expression.cpp:528
#4  0x00005605e47add28 in Tianmu::core::MysqlExpression::Evaluate (this=0x7f57048fc7b0) at /data/codebase/stonedb57/stonedb/storage/tianmu/core/mysql_expression.cpp:465
#5  0x00005605e457c350 in Tianmu::vcolumn::ExpressionColumn::IsNullImpl (this=0x7f5704009b60, mit=...)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/vc/expr_column.cpp:127
#6  0x00005605e4462bdc in Tianmu::vcolumn::VirtualColumnBase::IsNull (this=0x7f5704009b60, mit=...)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/vc/virtual_column_base.h:112
#7  0x00005605e44780bd in Tianmu::core::TempTable::SendResult (this=0x7f57048f76b0, limit=5, offset=0, sender=..., pagewise=false)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/core/temp_table_low.cpp:485
#8  0x00005605e4476f9e in Tianmu::core::TempTable::FillMaterializedBuffers (this=0x7f57048f76b0, local_limit=5, local_offset=0, sender=0x7f57048f6410, pagewise=false)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/core/temp_table_low.cpp:315
#9  0x00005605e445efc7 in Tianmu::core::TempTable::Materialize (this=0x7f57048f76b0, in_subq=false, sender=0x7f57048f6410, lazy=false)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/core/temp_table.cpp:2042
#10 0x00005605e43e0f9c in Tianmu::core::Engine::Execute (this=0x5605e667d860, thd=0x7f5704000e10, lex=0x7f5704003138, result_output=0x7f57048f4f00, unit_for_union=0x0)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/core/engine_execute.cpp:482
#11 0x00005605e43dfbcf in Tianmu::core::Engine::HandleSelect (this=0x5605e667d860, thd=0x7f5704000e10, lex=0x7f5704003138, result=@0x7f5c2c866488: 0x7f57048f4f00,
    setup_tables_done_option=0, res=@0x7f5c2c86647c: 32599, optimize_after_tianmu=@0x7f5c2c866484: 1, tianmu_free_join=@0x7f5c2c866480: 1, with_insert=0)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/core/engine_execute.cpp:238
#12 0x00005605e44f8e84 in Tianmu::handler::ha_my_tianmu_query (thd=0x7f5704000e10, lex=0x7f5704003138, result_output=@0x7f5c2c866488: 0x7f57048f4f00,
    setup_tables_done_option=0, res=@0x7f5c2c86647c: 32599, optimize_after_tianmu=@0x7f5c2c866484: 1, tianmu_free_join=@0x7f5c2c866480: 1, with_insert=0)
    at /data/codebase/stonedb57/stonedb/storage/tianmu/handler/ha_my_tianmu.cpp:88
#13 0x00005605e3a7d7e6 in execute_sqlcom_select (thd=0x7f5704000e10, all_tables=0x7f57048f48b0) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:5217
#14 0x00005605e3a76811 in mysql_execute_command (thd=0x7f5704000e10, first_level=true) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:2856
#15 0x00005605e3a7e874 in mysql_parse (thd=0x7f5704000e10, parser_state=0x7f5c2c867630) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:5655
#16 0x00005605e3a733b3 in dispatch_command (thd=0x7f5704000e10, com_data=0x7f5c2c867ee0, command=COM_QUERY) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:1495
#17 0x00005605e3a7222a in do_command (thd=0x7f5704000e10) at /data/codebase/stonedb57/stonedb/sql/sql_parse.cc:1034
#18 0x00005605e3bbab5c in handle_connection (arg=0x5605eb28c810) at /data/codebase/stonedb57/stonedb/sql/conn_handler/connection_handler_per_thread.cc:313
#19 0x00005605e4306dbd in pfs_spawn_thread (arg=0x5605eb0053e0) at /data/codebase/stonedb57/stonedb/storage/perfschema/pfs.cc:2197
#20 0x00007f5c3343f609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#21 0x00007f5c33213133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

[lujiashun]

buf->x == 0 is considered as null

bool Item_tianmufield::get_time(MYSQL_TIME *ltime) {
  if ((null_value = buf->null) ||
      ((tianmu_type.attrtype == common::ColumnType::DATETIME || tianmu_type.attrtype == common::ColumnType::DATE) &&
       buf->x == 0))  // zero date is illegal
    return 1;         // like in Item_field::get_time - return 1 on null value.
  FeedValue();
  return ivalue->get_time(ltime);
}

[lujiashun]

Consider zero date is legal , not consider the sql_mode NO_ZERO_DATE/NO_ZERO_IN_DATE. Because NO_ZERO_DATE/NO_ZERO_IN_DATE is deprecated; expect it to be removed in a future release of MySQL as a
separate mode name and its effect included in the effects of strict SQL mode.