Gagan Goel (Inactive) added a comment - 2020-06-27 00:17 MDEV-23032.patch Attached is a quick patch I wrote to get this case working.

Alexander Barkov added a comment - 2020-07-27 07:00 - edited Update: this problem was fixed by MDEV-23320 A related problem: CREATE OR REPLACE TABLE t1 AS SELECT floor(0xffffffffff); ERROR 1264 (22003): Out of range value for column 'floor(0xffffffffff)' at row 1

Alexander Barkov added a comment - 2020-07-27 07:09 - edited A related problem (now reported in MDEV-23323 ): CREATE OR REPLACE TABLE t1 (a BIT (64)); CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1; DESC t2; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | FLOOR(a) | decimal(64,0) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ Looks redundant. A DECIMAL(19,0) or even a BIGINT() column would be enough.

Alexander Barkov added a comment - 2020-07-27 07:18 - edited Update: This problem was fixed by MDEV-23323 A related problem: CREATE OR REPLACE TABLE t1 (a SET ( '1000000000000000000000000000000000000000000000000000000000000000000000000000000000000' )); INSERT INTO t1 VALUES (1); CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1; SELECT * FROM t2; DESC t2; +----------+ | FLOOR(a) | +----------+ | 1 | +----------+ +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | FLOOR(a) | decimal(65,0) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ Looks redundant. In numeric context SET can return a value beween 0 and 0xFF or between and 0xFFFF, depending on the amount of values in SET. INT should be enough (or even TINYINT/SMALLINT).

Alexander Barkov added a comment - 2020-07-27 07:25 - edited Update 2020-06-29: The problem described below was fixed under terms of MDEV-23311 . FLOOR(TIME) and CEILING(TIME) now return TIME rather than BIGINT. A related problem: CREATE OR REPLACE TABLE t1 (a TIME (6)); INSERT INTO t1 VALUES ( '10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1; SELECT * FROM t2; DESC t2; +----------+ | FLOOR(a) | +----------+ | 102030 | +----------+ +----------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------+------+-----+---------+-------+ | FLOOR(a) | bigint(12) | YES | | NULL | | +----------+------------+------+-----+---------+-------+ Looks redundant. FLOOR(time) should never return more than 9 digits (hhhmmss). So INT(9) should be enough. Or even better TIME(0), like in this script: CREATE OR REPLACE TABLE t1 (a TIME (6)); CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a) FROM t1; DESC t2; +----------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------+------+-----+---------+-------+ | ROUND(a) | time | YES | | NULL | | +----------+------+------+-----+---------+-------+