1524
# Bug#43668: Wrong comparison and MIN/MAX for YEAR(2)
1526
create table t1 (f1 year(2), f2 year(4), f3 date, f4 datetime);
1527
insert into t1 values
1528
(98,1998,19980101,"1998-01-01 00:00:00"),
1529
(00,2000,20000101,"2000-01-01 00:00:01"),
1530
(02,2002,20020101,"2002-01-01 23:59:59"),
1531
(60,2060,20600101,"2060-01-01 11:11:11"),
1532
(70,1970,19700101,"1970-11-11 22:22:22"),
1533
(NULL,NULL,NULL,NULL);
1534
select min(f1),max(f1) from t1;
1537
select min(f2),max(f2) from t1;
1540
select min(f3),max(f3) from t1;
1542
1970-01-01 2060-01-01
1543
select min(f4),max(f4) from t1;
1545
1970-11-11 22:22:22 2060-01-01 11:11:11
1546
select a.f1 as a, b.f1 as b, a.f1 > b.f1 as gt,
1547
a.f1 < b.f1 as lt, a.f1<=>b.f1 as eq
1585
NULL NULL NULL NULL 1
1586
select a.f1 as a, b.f2 as b, a.f1 > b.f2 as gt,
1587
a.f1 < b.f2 as lt, a.f1<=>b.f2 as eq
1595
NULL 1998 NULL NULL 0
1601
NULL 2000 NULL NULL 0
1607
NULL 2002 NULL NULL 0
1613
NULL 2060 NULL NULL 0
1619
NULL 1970 NULL NULL 0
1625
NULL NULL NULL NULL 1
1626
select a.f1 as a, b.f3 as b, a.f1 > b.f3 as gt,
1627
a.f1 < b.f3 as lt, a.f1<=>b.f3 as eq
1635
NULL 1998-01-01 NULL NULL 0
1641
NULL 2000-01-01 NULL NULL 0
1647
NULL 2002-01-01 NULL NULL 0
1653
NULL 2060-01-01 NULL NULL 0
1659
NULL 1970-01-01 NULL NULL 0
1665
NULL NULL NULL NULL 1
1666
select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt,
1667
a.f1 < b.f4 as lt, a.f1<=>b.f4 as eq
1670
98 1998-01-01 00:00:00 0 1 0
1671
00 1998-01-01 00:00:00 1 0 0
1672
02 1998-01-01 00:00:00 1 0 0
1673
60 1998-01-01 00:00:00 1 0 0
1674
70 1998-01-01 00:00:00 0 1 0
1675
NULL 1998-01-01 00:00:00 NULL NULL 0
1676
98 2000-01-01 00:00:01 0 1 0
1677
00 2000-01-01 00:00:01 0 1 0
1678
02 2000-01-01 00:00:01 1 0 0
1679
60 2000-01-01 00:00:01 1 0 0
1680
70 2000-01-01 00:00:01 0 1 0
1681
NULL 2000-01-01 00:00:01 NULL NULL 0
1682
98 2002-01-01 23:59:59 0 1 0
1683
00 2002-01-01 23:59:59 0 1 0
1684
02 2002-01-01 23:59:59 0 1 0
1685
60 2002-01-01 23:59:59 1 0 0
1686
70 2002-01-01 23:59:59 0 1 0
1687
NULL 2002-01-01 23:59:59 NULL NULL 0
1688
98 2060-01-01 11:11:11 0 1 0
1689
00 2060-01-01 11:11:11 0 1 0
1690
02 2060-01-01 11:11:11 0 1 0
1691
60 2060-01-01 11:11:11 0 1 0
1692
70 2060-01-01 11:11:11 0 1 0
1693
NULL 2060-01-01 11:11:11 NULL NULL 0
1694
98 1970-11-11 22:22:22 1 0 0
1695
00 1970-11-11 22:22:22 1 0 0
1696
02 1970-11-11 22:22:22 1 0 0
1697
60 1970-11-11 22:22:22 1 0 0
1698
70 1970-11-11 22:22:22 0 1 0
1699
NULL 1970-11-11 22:22:22 NULL NULL 0
1705
NULL NULL NULL NULL 1
1706
select *, f1 = f2 from t1;
1708
98 1998 1998-01-01 1998-01-01 00:00:00 1
1709
00 2000 2000-01-01 2000-01-01 00:00:01 1
1710
02 2002 2002-01-01 2002-01-01 23:59:59 1
1711
60 2060 2060-01-01 2060-01-01 11:11:11 1
1712
70 1970 1970-01-01 1970-11-11 22:22:22 1
1713
NULL NULL NULL NULL NULL
1716
# Bug #54465: assert: field_types == 0 || field_types[field_pos] ==
1717
# MYSQL_TYPE_LONGLONG
1719
CREATE TABLE t1 (a INT);
1720
INSERT INTO t1 VALUES (1), (2);
1721
SELECT MAX((SELECT 1 FROM t1 ORDER BY @var LIMIT 1)) m FROM t1 t2, t1
1727
# Bug#58030 crash in Item_func_geometry_from_text::val_str
1729
SELECT MAX(TIMESTAMP(RAND(0)));
1730
SELECT MIN(TIMESTAMP(RAND(0)));
1732
# Bug#58177 crash and valgrind warnings in decimal and protocol sending functions...
1734
SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1735
SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1736
SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1737
SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
1738
SELECT RELEASE_LOCK('aaaaaaaaaaaaaaaaa');
1740
# Bug #11766094 - 59132: MIN() AND MAX() REMOVE UNSIGNEDNESS
1742
CREATE TABLE t1 (a BIGINT UNSIGNED);
1743
INSERT INTO t1 VALUES (18446668621106209655);
1744
SELECT MAX(LENGTH(a)), LENGTH(MAX(a)), MIN(a), MAX(a), CONCAT(MIN(a)), CONCAT(MAX(a)) FROM t1;
1745
MAX(LENGTH(a)) LENGTH(MAX(a)) MIN(a) MAX(a) CONCAT(MIN(a)) CONCAT(MAX(a))
1746
20 20 18446668621106209655 18446668621106209655 18446668621106209655 18446668621106209655
1749
# Bug #11766270 59343: YEAR(4): INCORRECT RESULT AND VALGRIND WARNINGS WITH MIN/MAX, UNION
1751
CREATE TABLE t1(f1 YEAR(4));
1752
INSERT INTO t1 VALUES (0000),(2001);
1753
(SELECT MAX(f1) FROM t1) UNION (SELECT MAX(f1) FROM t1);
1754
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
1755
def MAX(f1) MAX(f1) 13 4 4 Y 32864 0 63
1523
1760
End of 5.1 tests