1192
1192
SET TIME_ZONE='+02:00';
1193
1193
SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y'));
1194
1194
SET TIME_ZONE=DEFAULT;
1198
--echo # MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context
1200
CREATE TABLE t1 (a TIMESTAMP(3));
1201
INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999');
1202
SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1;
1204
CREATE TABLE t1 (a TIME(3));
1205
INSERT INTO t1 VALUES ('10:20:30.999');
1206
SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1;
1210
CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1211
CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1212
COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1213
CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1214
TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1215
DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1216
TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1219
CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
1220
CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
1221
COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3,
1222
CONCAT(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4,
1223
TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
1224
DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
1225
TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
1228
CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1229
CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1230
IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1231
CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1232
TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1233
DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1234
TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1237
CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
1238
CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
1239
IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3,
1240
CONCAT(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4,
1241
TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
1242
DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
1243
TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
1246
CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1247
CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1248
IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1249
CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1250
TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1251
DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1252
TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1255
CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
1256
CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
1257
IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3,
1258
CONCAT(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4,
1259
TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
1260
DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
1261
TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
1264
CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
1265
CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
1266
CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3,
1267
CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4,
1268
TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5,
1269
DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
1270
TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
1273
CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
1274
CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
1275
CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3,
1276
CONCAT(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4,
1277
TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5,
1278
DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
1279
TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
1281
CREATE TABLE t1 AS SELECT
1282
CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1,
1283
CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2,
1284
CONCAT(IFNULL(TIME(101010),TIME(101010))) AS c3,
1285
CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4;
1286
SHOW CREATE TABLE t1;
1291
--echo # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types
1293
CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
1294
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
1295
SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
1296
CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
1298
SHOW COLUMNS FROM t2;
1300
SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1;
1301
SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1;
1302
SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1;
1303
SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1;
1304
SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1;
1305
SELECT COALESCE(d, t3) FROM t1;
1306
SELECT CONCAT(COALESCE(d, t3)) FROM t1;
1307
SELECT COALESCE(dt2, t3) FROM t1;
1308
SELECT CONCAT(COALESCE(dt2, t3)) FROM t1;
1309
SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1;
1310
SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1;
1315
--echo # MDEV-4724 Some temporal functions do not preserve microseconds
1317
SELECT MAKETIME(10,10,10.231);
1318
SELECT MAKETIME(0, 0, 59.9);
1319
CREATE TABLE t1 AS SELECT
1321
MAKETIME(10,00,00.1),
1322
MAKETIME(10,00,00.12),
1323
MAKETIME(10,00,00.123),
1324
MAKETIME(10,00,00.1234),
1325
MAKETIME(10,00,00.12345),
1326
MAKETIME(10,00,00.123456);
1327
SHOW COLUMNS FROM t1;
1330
CREATE TABLE t1 AS SELECT
1333
TIME('10:00:00.12'),
1334
TIME('10:00:00.123'),
1335
TIME('10:00:00.1234'),
1336
TIME('10:00:00.12345'),
1337
TIME('10:00:00.12346');
1338
SHOW COLUMNS FROM t1;
1341
SET TIME_ZONE='+00:00';
1342
SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17');
1343
SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17');
1344
SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456');
1345
SELECT @@timestamp, FROM_UNIXTIME(@@timestamp);
1346
SET TIME_ZONE=DEFAULT;
1347
SET TIMESTAMP=DEFAULT;
1349
SELECT TIME('2012-10-16 15:54:16.12');
1350
SELECT TIMESTAMP('2012-10-16 15:54:16.12');
1351
SELECT TIMEDIFF('10:10:10.1','00:00:00');
1352
SELECT TIME_TO_SEC('10:10:10');
1353
SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12');
1354
SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12');
1355
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
1356
SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND);
1357
SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND);
1358
SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND);
1359
SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND);
1360
SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND);
1361
SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND);
1362
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
1363
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND);
1364
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND);
1365
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND);
1366
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND);
1367
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND);
1368
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND);
1369
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND);
1370
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND);
1371
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND);
1372
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND);
1373
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND);
1374
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND);
1375
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND);
1376
SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND);
1377
SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00');
1380
--echo # MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision
1382
CREATE TABLE t1 (t0 TIME);
1383
INSERT INTO t1 VALUES ('00:00:00');
1384
SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1385
CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1386
SHOW COLUMNS FROM t2;
1389
CREATE TABLE t1 (t0 DATETIME);
1390
INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
1391
SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1392
CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1;
1393
SHOW COLUMNS FROM t2;
1398
--echo # MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10')
1400
CREATE TABLE t1 AS SELECT
1401
TIMESTAMP('2001-01-01','10:10:10'),
1402
TIMESTAMP('2001-01-01','10:10:10.1'),
1403
TIMESTAMP('2001-01-01','10:10:10.12'),
1404
TIMESTAMP('2001-01-01','10:10:10.123'),
1405
TIMESTAMP('2001-01-01','10:10:10.1234'),
1406
TIMESTAMP('2001-01-01','10:10:10.12345'),
1407
TIMESTAMP('2001-01-01','10:10:10.123456'),
1408
TIMESTAMP('2001-01-01','10:10:10.1234567');
1409
SHOW COLUMNS FROM t1;
1413
CREATE TABLE t1 AS SELECT
1414
TIMESTAMP('2001-01-01 00:00:00','10:10:10'),
1415
TIMESTAMP('2001-01-01 00:00:00.1','10:10:10'),
1416
TIMESTAMP('2001-01-01 00:00:00.12','10:10:10'),
1417
TIMESTAMP('2001-01-01 00:00:00.123','10:10:10'),
1418
TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10'),
1419
TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10'),
1420
TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10'),
1421
TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10');
1422
SHOW COLUMNS FROM t1;
1426
CREATE TABLE t1 AS SELECT
1427
TIMESTAMP('00:00:00','10:10:10'),
1428
TIMESTAMP(TIME('00:00:00'),'10:10:10');
1429
SHOW COLUMNS FROM t1;
1434
--echo # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1)
1436
SELECT MAKETIME(0, 0, -0.1);
1439
--echo # MDEV-4857 Wrong result of HOUR('1 00:00:00')
1441
SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
1442
SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00');
1443
SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00'));
1444
SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00'));
1446
SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
1447
SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00');
1448
SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00'));
1449
SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
1452
--echo # MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.'
1454
SET TIMESTAMP=UNIX_TIMESTAMP('2014-01-22 18:19:20');
1455
CREATE TABLE t1 (t TIME);
1456
INSERT INTO t1 VALUES ('03:22:30'),('18:30:05');
1457
SELECT CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') FROM t1;
1458
SELECT GREATEST(t, CURRENT_DATE()) FROM t1;
1460
SET TIMESTAMP=DEFAULT;
1464
--echo # MDEV-5504 Server crashes in String::length on SELECT with MONTHNAME, GROUP BY, ROLLUP
1466
CREATE TABLE t1 (i INT);
1467
INSERT INTO t1 VALUES (1),(2);
1468
SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP;