813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
1 |
#
|
2 |
# Tests for the MONTH() function |
|
3 |
#
|
|
4 |
# The Drizzle MONTH() function differs from the MySQL MONTH() |
|
5 |
# function in these ways: |
|
6 |
#
|
|
7 |
# * Does not accept invalid parameters. This results in an error |
|
8 |
# in Drizzle. |
|
9 |
#
|
|
10 |
||
11 |
# MONTH() on a NULL should produce |
|
12 |
# a NULL. |
|
13 |
SELECT MONTH(NULL); |
|
14 |
||
15 |
#
|
|
16 |
# Test improper argument list |
|
17 |
#
|
|
18 |
# 1 arg is required. |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
19 |
--error ER_PARSE_ERROR
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
20 |
# Wrong parameter count...but unfortunately produces 1064 Syntax Error due to limitations of |
21 |
# the SQL parser, which considers MONTH a keyword before being a function symbol |
|
22 |
SELECT MONTH(); |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
23 |
--error ER_PARSE_ERROR
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
24 |
# Wrong parameter count...but unfortunately produces 1064 Syntax Error due to limitations of |
25 |
# the SQL parser, which considers MONTH a keyword before being a function symbol |
|
26 |
SELECT MONTH(1, 0); |
|
27 |
||
28 |
#
|
|
29 |
# Test invalid dates passed to MONTH |
|
30 |
# produce an error, not a NULL or anything |
|
31 |
# else... |
|
32 |
#
|
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
33 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
34 |
SELECT MONTH("xxx"); |
35 |
||
36 |
# Indy, bad dates! |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
37 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
38 |
SELECT MONTH("0000-00-00"); # No 0000-00-00 dates!... |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
39 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
40 |
SELECT MONTH("0000-01-01"); # No zero year parts |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
41 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
42 |
SELECT MONTH("0001-00-01"); # No zero month parts |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
43 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
44 |
SELECT MONTH("0001-01-00"); # No zero day parts |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
45 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
46 |
SELECT MONTH("2000-02-30"); # No Feb 30th! |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
47 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
48 |
SELECT MONTH("1900-02-29"); # Not a leap MONTH since not divisible evenly by 400... |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
49 |
--error ER_INVALID_DATETIME_VALUE
|
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
50 |
SELECT MONTH('1976-15-15'); # No 15th month! |
51 |
||
52 |
# A good date, which should output 1 |
|
53 |
SELECT MONTH("2009-01-12"); |
|
54 |
||
55 |
# A good date, which should output 1999 - Test of 1 digit MONTH conversion... |
|
56 |
SELECT MONTH("99-1-31"); |
|
57 |
||
58 |
# A good date, which should output 9 |
|
59 |
SELECT MONTH("0001-09-30"); |
|
60 |
||
61 |
# A good date, which should output 12 |
|
62 |
SELECT MONTH("9999-12-31"); |
|
63 |
||
64 |
# A good date in the common USA format, should output 7 |
|
65 |
SELECT MONTH('07/31/2009'); |
|
66 |
||
67 |
#
|
|
68 |
# Tests for alternate DATETIME syntaxes |
|
69 |
# in string and numeric formats. All should |
|
70 |
# return 9. |
|
71 |
#
|
|
72 |
SELECT MONTH('20090916'); |
|
73 |
SELECT MONTH('20090916091528'); |
|
74 |
SELECT MONTH("20090916091528.000000"); |
|
75 |
SELECT MONTH('09-09-16'); |
|
76 |
SELECT MONTH('09.09.16'); |
|
77 |
SELECT MONTH('09/09/16'); |
|
78 |
SELECT MONTH('09-09-16'); |
|
79 |
SELECT MONTH('09-9-2'); |
|
80 |
SELECT MONTH(20090916); |
|
81 |
SELECT MONTH(20090916091528); |
|
813.1.19
by Jay Pipes
To remain in compatibility with MySQL, added ability to interpret |
82 |
SELECT MONTH(20090916091528.000001); |
813.1.8
by Jay Pipes
Fixes MONTH() function to use new Temporal system. Errors |
83 |
|
84 |
# Now test field values of different types |
|
85 |
||
86 |
USE test; |
|
87 |
--disable_warnings
|
|
88 |
DROP TABLE IF EXISTS t1; |
|
89 |
--enable_warnings
|
|
90 |
CREATE TABLE t1 ( |
|
91 |
int_fld INT NOT NULL |
|
92 |
, date_fld DATE NOT NULL |
|
93 |
, datetime_fld DATETIME NOT NULL |
|
94 |
, timestamp_fld TIMESTAMP NOT NULL |
|
95 |
, char_fld CHAR(22) NOT NULL |
|
96 |
, varchar_fld VARCHAR(22) NOT NULL |
|
97 |
, text_fld TEXT NOT NULL |
|
98 |
, blob_fld BLOB NOT NULL |
|
99 |
);
|
|
100 |
||
101 |
INSERT INTO t1 VALUES ( |
|
102 |
20071130
|
|
103 |
, "2007-11-30" |
|
104 |
, "2007-11-30 16:30:19" |
|
105 |
, "2007-11-30T16:30:19" |
|
106 |
, "2007-11-30 16:30:19" |
|
107 |
, "2007-11-30 16:30:19" |
|
108 |
, "2007-11-30T16:30:19" |
|
109 |
, "2007-11-30T16:30:19" |
|
110 |
);
|
|
111 |
||
112 |
# Should all output 11 |
|
113 |
SELECT MONTH(int_fld) FROM t1; |
|
114 |
SELECT MONTH(date_fld) FROM t1; |
|
115 |
SELECT MONTH(datetime_fld) FROM t1; |
|
116 |
SELECT MONTH(timestamp_fld) FROM t1; |
|
117 |
SELECT MONTH(char_fld) FROM t1; |
|
118 |
SELECT MONTH(varchar_fld) FROM t1; |
|
119 |
SELECT MONTH(text_fld) FROM t1; |
|
120 |
SELECT MONTH(blob_fld) FROM t1; |
|
121 |
||
122 |
DROP TABLE t1; |