Description:
execute sql"select distinct
cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc;",and then found that the results of the sql is not as expected.
May be caused by precision(as float).
table'tbl_1_all_type2index_desc' showed in Private comment.
we can query the column 'date_col' that "select distinct date_col from tbl_1_all_type2index_desc;"
+------------+
| date_col |
+------------+
| 1998-09-02 |
| 2008-10-02 |
| 1997-09-02 |
| 1999-09-02 |
| 1995-09-02 |
| 1992-03-04 |
| 2020-01-01 |
| 9999-12-31 |
| 1998-01-03 |
| 1998-02-03 |
| 1998-03-03 |
| 1998-04-03 |
| 1998-05-03 |
| 1998-06-03 |
| 1998-07-03 |
| 1998-08-03 |
| 1998-09-03 |
| 1998-10-03 |
| 1998-11-03 |
| 1998-12-03 |
| 1000-01-01 |
+------------+
then using cast ... as ...,:
select distinct cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc;
| cast(cast(date_col as float) as signed int) |
+---------------------------------------------+
| 19980902 |
| 20081002 |
| 19970902 |
| 19990902 |
| 19950902 |
| 19920304 |
| 20200100 |
| 99991232 |
| 19980104 |
| 19980204 |
| 19980304 |
| 19980404 |
| 19980504 |
| 19980604 |
| 19980704 |
| 19980804 |
| 19980904 |
| 19981004 |
| 19981104 |
| 19981204 |
| 10000101 |
+---------------------------------------------+
I think the result is not accurate.And don't know whether is reasonable.
--------------------------------------------------------------------------------
The problems of the same nature as the previous one occured from the sql
"select cast(cast(173561798 as float) as signed int);"
+----------------------------------------------+
| cast(cast(173561798 as float) as signed int) |
+----------------------------------------------+
| 173561792 |
+----------------------------------------------+
but the result of open source 8.0.22 is appreciable.
(8.0.22)
+----------------------------------------------+
| cast(cast(173561798 as float) as signed int) |
+----------------------------------------------+
| 173561798 |
+----------------------------------------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
select distinct datetime_col from tbl_1_all_type2index_desc;
+----------------------------+
| datetime_col |
+----------------------------+
| 9999-12-31 23:59:59.000000 |
| 2022-12-09 00:00:00.000000 |
| 2020-01-01 00:00:01.000000 |
| 2018-12-31 00:00:00.000000 |
| 2018-10-09 00:00:00.000000 |
| 2015-05-09 00:00:00.000000 |
| 2014-11-09 00:00:00.000000 |
| 2012-11-09 00:00:00.000000 |
| 2011-11-09 00:00:00.000000 |
| 2008-10-02 13:20:00.000000 |
| 2001-05-09 00:00:00.000000 |
| 2001-04-06 00:00:00.000000 |
| 2001-03-02 00:00:00.000000 |
| 2001-02-01 00:00:00.000000 |
| 2001-01-01 00:00:00.000000 |
| 1999-09-02 13:20:00.000000 |
| 1998-09-02 13:20:00.000000 |
| 1997-09-02 13:20:00.000000 |
| 1995-09-02 13:20:00.000000 |
| 1992-03-04 12:20:00.000000 |
| 1000-01-01 00:00:00.000000 |
+----------------------------+
21 rows in set (0.00 sec)
select distinct cast(cast(datetime_col as float) as signed int) from tbl_1_all_type2index_desc;
+-------------------------------------------------+
| cast(cast(datetime_col as float) as signed int) |
+-------------------------------------------------+
| 19980902989824 |
| 20081002151936 |
| 19970901671936 |
| 19990902210560 |
| 19950901133312 |
| 19920303685632 |
| 20200101511168 |
| 99991234281472 |
| 20181231337472 |
| 20221209346048 |
| 20121108086784 |
| 20111108866048 |
| 20141108625408 |
| 20150508060672 |
| 20181009039360 |
| 20010508484608 |
| 20010405724160 |
| 20010302963712 |
| 20010200203264 |
| 20010101637120 |
| 10000100491264 |
+-------------------------------------------------+
21 rows in set (0.00 sec)
I have found that besides,there are still some sqls occured that the results are not as expected.
SELECT CAST(CAST(350766548351385.6 as float) as signed int);
select distinct cast(cast(HEX('qwer') as float) as signed int);
select distinct cast(cast(RADIANS(bigint_col) as float) as signed int) from tbl_1_all_type2index_desc;
select distinct cast(cast(ABS(int_col) as float) as signed int) from tbl_1_all_type2index_desc;
select distinct cast(cast(RADIANS(decimal_col) as float) as signed int) from tbl_1_all_typeindex_desc;
select distinct cast(cast(POW(2.5,40) as float) as signed int);
How to repeat:
1.execute the date_col :
select distinct date_col from tbl_1_all_type2index_desc;
2.excute the cast query:
select distinct
cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc;
--------------------------------------------------------------------------------
1.execute this sql:
select cast(cast(173561798 as float) as signed int);
--------------------------------------------------------------------------------
1.execute the date_col :
select distinct datetime_col from tbl_1_all_type2index_desc;
2.excute the cast query:
select distinct cast(cast(datetime_col as float) as signed int) from tbl_1_all_type2index_desc;
Suggested fix:
I hope that
select distinct cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc;
this sql 's result is the same as open source which version is 8.0.22
like this
+---------------------------------------------+
| cast(cast(date_col as float) as signed int) |
+---------------------------------------------+
| 19980902 |
| 20081002 |
| 19970902 |
| 19990902 |
| 19950902 |
| 19920304 |
| 20200101 |
| 99991231 |
| 19980103 |
| 19980203 |
| 19980303 |
| 19980403 |
| 19980503 |
| 19980603 |
| 19980703 |
| 19980803 |
| 19980903 |
| 19981003 |
| 19981103 |
| 19981203 |
| 10000101 |
+---------------------------------------------+
21 rows in set (0.00 sec)
--------------------------------------------------------------------------------
select cast(cast(173561798 as float) as signed int);
+----------------------------------------------+
| cast(cast(173561798 as float) as signed int) |
+----------------------------------------------+
| 173561798 |
+----------------------------------------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
select distinct cast(cast(datetime_col as float) as signed int) from tbl_1_all_type2index_desc;
+-------------------------------------------------+
| cast(cast(datetime_col as float) as signed int) |
+-------------------------------------------------+
| 19980902132000 |
| 20081002132000 |
| 19970902132000 |
| 19990902132000 |
| 19950902132000 |
| 19920304122000 |
| 20200101000001 |
| 99991231235959 |
| 20181231000000 |
| 20221209000000 |
| 20121109000000 |
| 20111109000000 |
| 20141109000000 |
| 20150509000000 |
| 20181009000000 |
| 20010509000000 |
| 20010406000000 |
| 20010302000000 |
| 20010201000000 |
| 20010101000000 |
| 10000101000000 |
+-------------------------------------------------+
21 rows in set (0.00 sec)
Description: execute sql"select distinct cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc;",and then found that the results of the sql is not as expected. May be caused by precision(as float). table'tbl_1_all_type2index_desc' showed in Private comment. we can query the column 'date_col' that "select distinct date_col from tbl_1_all_type2index_desc;" +------------+ | date_col | +------------+ | 1998-09-02 | | 2008-10-02 | | 1997-09-02 | | 1999-09-02 | | 1995-09-02 | | 1992-03-04 | | 2020-01-01 | | 9999-12-31 | | 1998-01-03 | | 1998-02-03 | | 1998-03-03 | | 1998-04-03 | | 1998-05-03 | | 1998-06-03 | | 1998-07-03 | | 1998-08-03 | | 1998-09-03 | | 1998-10-03 | | 1998-11-03 | | 1998-12-03 | | 1000-01-01 | +------------+ then using cast ... as ...,: select distinct cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc; | cast(cast(date_col as float) as signed int) | +---------------------------------------------+ | 19980902 | | 20081002 | | 19970902 | | 19990902 | | 19950902 | | 19920304 | | 20200100 | | 99991232 | | 19980104 | | 19980204 | | 19980304 | | 19980404 | | 19980504 | | 19980604 | | 19980704 | | 19980804 | | 19980904 | | 19981004 | | 19981104 | | 19981204 | | 10000101 | +---------------------------------------------+ I think the result is not accurate.And don't know whether is reasonable. -------------------------------------------------------------------------------- The problems of the same nature as the previous one occured from the sql "select cast(cast(173561798 as float) as signed int);" +----------------------------------------------+ | cast(cast(173561798 as float) as signed int) | +----------------------------------------------+ | 173561792 | +----------------------------------------------+ but the result of open source 8.0.22 is appreciable. (8.0.22) +----------------------------------------------+ | cast(cast(173561798 as float) as signed int) | +----------------------------------------------+ | 173561798 | +----------------------------------------------+ 1 row in set (0.00 sec) -------------------------------------------------------------------------------- select distinct datetime_col from tbl_1_all_type2index_desc; +----------------------------+ | datetime_col | +----------------------------+ | 9999-12-31 23:59:59.000000 | | 2022-12-09 00:00:00.000000 | | 2020-01-01 00:00:01.000000 | | 2018-12-31 00:00:00.000000 | | 2018-10-09 00:00:00.000000 | | 2015-05-09 00:00:00.000000 | | 2014-11-09 00:00:00.000000 | | 2012-11-09 00:00:00.000000 | | 2011-11-09 00:00:00.000000 | | 2008-10-02 13:20:00.000000 | | 2001-05-09 00:00:00.000000 | | 2001-04-06 00:00:00.000000 | | 2001-03-02 00:00:00.000000 | | 2001-02-01 00:00:00.000000 | | 2001-01-01 00:00:00.000000 | | 1999-09-02 13:20:00.000000 | | 1998-09-02 13:20:00.000000 | | 1997-09-02 13:20:00.000000 | | 1995-09-02 13:20:00.000000 | | 1992-03-04 12:20:00.000000 | | 1000-01-01 00:00:00.000000 | +----------------------------+ 21 rows in set (0.00 sec) select distinct cast(cast(datetime_col as float) as signed int) from tbl_1_all_type2index_desc; +-------------------------------------------------+ | cast(cast(datetime_col as float) as signed int) | +-------------------------------------------------+ | 19980902989824 | | 20081002151936 | | 19970901671936 | | 19990902210560 | | 19950901133312 | | 19920303685632 | | 20200101511168 | | 99991234281472 | | 20181231337472 | | 20221209346048 | | 20121108086784 | | 20111108866048 | | 20141108625408 | | 20150508060672 | | 20181009039360 | | 20010508484608 | | 20010405724160 | | 20010302963712 | | 20010200203264 | | 20010101637120 | | 10000100491264 | +-------------------------------------------------+ 21 rows in set (0.00 sec) I have found that besides,there are still some sqls occured that the results are not as expected. SELECT CAST(CAST(350766548351385.6 as float) as signed int); select distinct cast(cast(HEX('qwer') as float) as signed int); select distinct cast(cast(RADIANS(bigint_col) as float) as signed int) from tbl_1_all_type2index_desc; select distinct cast(cast(ABS(int_col) as float) as signed int) from tbl_1_all_type2index_desc; select distinct cast(cast(RADIANS(decimal_col) as float) as signed int) from tbl_1_all_typeindex_desc; select distinct cast(cast(POW(2.5,40) as float) as signed int); How to repeat: 1.execute the date_col : select distinct date_col from tbl_1_all_type2index_desc; 2.excute the cast query: select distinct cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc; -------------------------------------------------------------------------------- 1.execute this sql: select cast(cast(173561798 as float) as signed int); -------------------------------------------------------------------------------- 1.execute the date_col : select distinct datetime_col from tbl_1_all_type2index_desc; 2.excute the cast query: select distinct cast(cast(datetime_col as float) as signed int) from tbl_1_all_type2index_desc; Suggested fix: I hope that select distinct cast(cast(date_col as float) as signed int) from tbl_1_all_type2index_desc; this sql 's result is the same as open source which version is 8.0.22 like this +---------------------------------------------+ | cast(cast(date_col as float) as signed int) | +---------------------------------------------+ | 19980902 | | 20081002 | | 19970902 | | 19990902 | | 19950902 | | 19920304 | | 20200101 | | 99991231 | | 19980103 | | 19980203 | | 19980303 | | 19980403 | | 19980503 | | 19980603 | | 19980703 | | 19980803 | | 19980903 | | 19981003 | | 19981103 | | 19981203 | | 10000101 | +---------------------------------------------+ 21 rows in set (0.00 sec) -------------------------------------------------------------------------------- select cast(cast(173561798 as float) as signed int); +----------------------------------------------+ | cast(cast(173561798 as float) as signed int) | +----------------------------------------------+ | 173561798 | +----------------------------------------------+ 1 row in set (0.00 sec) -------------------------------------------------------------------------------- select distinct cast(cast(datetime_col as float) as signed int) from tbl_1_all_type2index_desc; +-------------------------------------------------+ | cast(cast(datetime_col as float) as signed int) | +-------------------------------------------------+ | 19980902132000 | | 20081002132000 | | 19970902132000 | | 19990902132000 | | 19950902132000 | | 19920304122000 | | 20200101000001 | | 99991231235959 | | 20181231000000 | | 20221209000000 | | 20121109000000 | | 20111109000000 | | 20141109000000 | | 20150509000000 | | 20181009000000 | | 20010509000000 | | 20010406000000 | | 20010302000000 | | 20010201000000 | | 20010101000000 | | 10000101000000 | +-------------------------------------------------+ 21 rows in set (0.00 sec)