← Back to branch summary

~drizzle-trunk/drizzle/development

1 by brian
clean slate 
1
 
set @org_mode=@@sql_mode;

2
 
set @@sql_mode='ansi,traditional';

3
 
select @@sql_mode;

4
 
@@sql_mode

5
 
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

6
 
DROP TABLE IF EXISTS t1, t2;

7
 
CREATE TABLE t1 (col1 date);

8
 
INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');

9
 
INSERT INTO t1 VALUES('0000-10-31');

10
 
INSERT INTO t1 VALUES('2004-0-31');

11
 
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 1

12
 
INSERT INTO t1 VALUES('2004-01-02'),('2004-0-31');

13
 
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2

14
 
INSERT INTO t1 VALUES('2004-10-0');

15
 
ERROR 22007: Incorrect date value: '2004-10-0' for column 'col1' at row 1

16
 
INSERT INTO t1 VALUES('2004-09-31');

17
 
ERROR 22007: Incorrect date value: '2004-09-31' for column 'col1' at row 1

18
 
INSERT INTO t1 VALUES('2004-10-32');

19
 
ERROR 22007: Incorrect date value: '2004-10-32' for column 'col1' at row 1

20
 
INSERT INTO t1 VALUES('2003-02-29');

21
 
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1

22
 
INSERT INTO t1 VALUES('2004-13-15');

23
 
ERROR 22007: Incorrect date value: '2004-13-15' for column 'col1' at row 1

24
 
INSERT INTO t1 VALUES('0000-00-00');

25
 
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1

26
 
INSERT INTO t1 VALUES ('59');

27
 
ERROR 22007: Incorrect date value: '59' for column 'col1' at row 1

28
 
set @@sql_mode='STRICT_ALL_TABLES';

29
 
INSERT INTO t1 VALUES('2004-01-03'),('2004-0-31');

30
 
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';

31
 
INSERT INTO t1 VALUES('2004-0-30');

32
 
ERROR 22007: Incorrect date value: '2004-0-30' for column 'col1' at row 1

33
 
INSERT INTO t1 VALUES('2004-01-04'),('2004-0-31'),('2004-01-05');

34
 
ERROR 22007: Incorrect date value: '2004-0-31' for column 'col1' at row 2

35
 
INSERT INTO t1 VALUES('0000-00-00');

36
 
INSERT IGNORE INTO t1 VALUES('2004-0-29');

37
 
Warnings:

38
 
Warning	1265	Data truncated for column 'col1' at row 1

39
 
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';

40
 
INSERT INTO t1 VALUES('0000-00-00');

41
 
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1

42
 
INSERT IGNORE INTO t1 VALUES('0000-00-00');

43
 
Warnings:

44
 
Warning	1265	Data truncated for column 'col1' at row 1

45
 
INSERT INTO t1 VALUES ('2004-0-30');

46
 
INSERT INTO t1 VALUES ('2004-2-30');

47
 
ERROR 22007: Incorrect date value: '2004-2-30' for column 'col1' at row 1

48
 
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';

49
 
INSERT INTO t1 VALUES ('2004-2-30');

50
 
set @@sql_mode='ansi,traditional';

51
 
INSERT IGNORE INTO t1 VALUES('2004-02-29'),('2004-13-15'),('0000-00-00');

52
 
Warnings:

53
 
Warning	1265	Data truncated for column 'col1' at row 2

54
 
Warning	1265	Data truncated for column 'col1' at row 3

55
 
select * from t1;

56
 
col1

57
 
2004-01-01

58
 
2004-02-29

59
 
0000-10-31

60
 
2004-01-02

61
 
2004-01-03

62
 
2004-00-31

63
 
2004-01-04

64
 
0000-00-00

65
 
0000-00-00

66
 
0000-00-00

67
 
2004-00-30

68
 
2004-02-30

69
 
2004-02-29

70
 
0000-00-00

71
 
0000-00-00

72
 
drop table t1;

73
 
set @@sql_mode='strict_trans_tables';

74
 
CREATE TABLE t1 (col1 date) engine=myisam;

75
 
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');

76
 
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1

77
 
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');

78
 
Warnings:

79
 
Warning	1265	Data truncated for column 'col1' at row 2

80
 
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');

81
 
Warnings:

82
 
Warning	1265	Data truncated for column 'col1' at row 1

83
 
INSERT INTO t1 VALUES ('2003-02-29');

84
 
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1

85
 
INSERT ignore INTO t1 VALUES('2003-02-30');

86
 
Warnings:

87
 
Warning	1265	Data truncated for column 'col1' at row 1

88
 
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';

89
 
INSERT ignore INTO t1 VALUES('2003-02-31');

90
 
select * from t1;

91
 
col1

92
 
2004-01-02

93
 
0000-00-00

94
 
2004-01-03

95
 
0000-00-00

96
 
2004-01-04

97
 
0000-00-00

98
 
2003-02-31

99
 
drop table t1;

100
 
set @@sql_mode='strict_trans_tables';

101
 
CREATE TABLE t1 (col1 date) engine=innodb;

102
 
INSERT INTO t1 VALUES('2004-13-31'),('2004-1-1');

103
 
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 1

104
 
INSERT INTO t1 VALUES ('2004-1-2'), ('2004-13-31'),('2004-1-3');

105
 
ERROR 22007: Incorrect date value: '2004-13-31' for column 'col1' at row 2

106
 
INSERT IGNORE INTO t1 VALUES('2004-13-31'),('2004-1-4');

107
 
Warnings:

108
 
Warning	1265	Data truncated for column 'col1' at row 1

109
 
INSERT INTO t1 VALUES ('2003-02-29');

110
 
ERROR 22007: Incorrect date value: '2003-02-29' for column 'col1' at row 1

111
 
INSERT ignore INTO t1 VALUES('2003-02-30');

112
 
Warnings:

113
 
Warning	1265	Data truncated for column 'col1' at row 1

114
 
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';

115
 
INSERT ignore INTO t1 VALUES('2003-02-31');

116
 
select * from t1;

117
 
col1

118
 
0000-00-00

119
 
2004-01-04

120
 
0000-00-00

121
 
2003-02-31

122
 
drop table t1;

123
 
set @@sql_mode='ansi,traditional';

124
 
CREATE TABLE t1 (col1 datetime);

125
 
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');

126
 
INSERT INTO t1 VALUES('0000-10-31 15:30:00');

127
 
INSERT INTO t1 VALUES('2004-0-31 15:30:00');

128
 
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1

129
 
INSERT INTO t1 VALUES('2004-10-0 15:30:00');

130
 
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1

131
 
INSERT INTO t1 VALUES('2004-09-31 15:30:00');

132
 
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1

133
 
INSERT INTO t1 VALUES('2004-10-32 15:30:00');

134
 
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1

135
 
INSERT INTO t1 VALUES('2003-02-29 15:30:00');

136
 
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1

137
 
INSERT INTO t1 VALUES('2004-13-15 15:30:00');

138
 
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1

139
 
INSERT INTO t1 VALUES('0000-00-00 15:30:00');

140
 
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1

141
 
INSERT INTO t1 VALUES ('59');

142
 
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1

143
 
select * from t1;

144
 
col1

145
 
2004-10-31 15:30:00

146
 
2004-02-29 15:30:00

147
 
0000-10-31 15:30:00

148
 
drop table t1;

149
 
CREATE TABLE t1 (col1 timestamp);

150
 
INSERT INTO t1 VALUES('2004-10-31 15:30:00'),('2004-02-29 15:30:00');

151
 
INSERT INTO t1 VALUES('0000-10-31 15:30:00');

152
 
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col1' at row 1

153
 
INSERT INTO t1 VALUES('2004-0-31 15:30:00');

154
 
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1

155
 
INSERT INTO t1 VALUES('2004-10-0 15:30:00');

156
 
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1

157
 
INSERT INTO t1 VALUES('2004-09-31 15:30:00');

158
 
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col1' at row 1

159
 
INSERT INTO t1 VALUES('2004-10-32 15:30:00');

160
 
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1

161
 
INSERT INTO t1 VALUES('2003-02-29 15:30:00');

162
 
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col1' at row 1

163
 
INSERT INTO t1 VALUES('2004-13-15 15:30:00');

164
 
ERROR 22007: Incorrect datetime value: '2004-13-15 15:30:00' for column 'col1' at row 1

165
 
INSERT INTO t1 VALUES('2004-02-29 25:30:00');

166
 
ERROR 22007: Incorrect datetime value: '2004-02-29 25:30:00' for column 'col1' at row 1

167
 
INSERT INTO t1 VALUES('2004-02-29 15:65:00');

168
 
ERROR 22007: Incorrect datetime value: '2004-02-29 15:65:00' for column 'col1' at row 1

169
 
INSERT INTO t1 VALUES('2004-02-29 15:31:61');

170
 
ERROR 22007: Incorrect datetime value: '2004-02-29 15:31:61' for column 'col1' at row 1

171
 
INSERT INTO t1 VALUES('0000-00-00 15:30:00');

172
 
ERROR 22007: Incorrect datetime value: '0000-00-00 15:30:00' for column 'col1' at row 1

173
 
INSERT INTO t1 VALUES('0000-00-00 00:00:00');

174
 
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1

175
 
INSERT IGNORE INTO t1 VALUES('0000-00-00 00:00:00');

176
 
Warnings:

177
 
Warning	1265	Data truncated for column 'col1' at row 1

178
 
INSERT INTO t1 VALUES ('59');

179
 
ERROR 22007: Incorrect datetime value: '59' for column 'col1' at row 1

180
 
set @@sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';

181
 
INSERT INTO t1 VALUES('2004-0-31 15:30:00');

182
 
ERROR 22007: Incorrect datetime value: '2004-0-31 15:30:00' for column 'col1' at row 1

183
 
INSERT INTO t1 VALUES('2004-10-0 15:30:00');

184
 
ERROR 22007: Incorrect datetime value: '2004-10-0 15:30:00' for column 'col1' at row 1

185
 
INSERT INTO t1 VALUES('2004-10-32 15:30:00');

186
 
ERROR 22007: Incorrect datetime value: '2004-10-32 15:30:00' for column 'col1' at row 1

187
 
INSERT INTO t1 VALUES('2004-02-30 15:30:04');

188
 
ERROR 22007: Incorrect datetime value: '2004-02-30 15:30:04' for column 'col1' at row 1

189
 
INSERT INTO t1 VALUES('0000-00-00 00:00:00');

190
 
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';

191
 
INSERT INTO t1 VALUES('0000-00-00 00:00:00');

192
 
set @@sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE';

193
 
INSERT INTO t1 VALUES('0000-00-00 00:00:00');

194
 
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1

195
 
set @@sql_mode='ansi,traditional';

196
 
SELECT * FROM t1;

197
 
col1

198
 
2004-10-31 15:30:00

199
 
2004-02-29 15:30:00

200
 
0000-00-00 00:00:00

201
 
0000-00-00 00:00:00

202
 
0000-00-00 00:00:00

203
 
DROP TABLE t1;

204
 
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);

205
 
INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y'));

206
 
INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));

207
 
INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));

208
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));

209
 
Warnings:

210
 
Note	1265	Data truncated for column 'col1' at row 1

211
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));

212
 
ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at row 1

213
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));

214
 
ERROR 22007: Incorrect date value: '2004-10-00 15:30:00' for column 'col1' at row 1

215
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));

216
 
ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at row 1

217
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));

218
 
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date

219
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));

220
 
ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column 'col1' at row 1

221
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));

222
 
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date

223
 
INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));

224
 
ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1

225
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));

226
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));

227
 
ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col2' at row 1

228
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));

229
 
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1

230
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));

231
 
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2' at row 1

232
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));

233
 
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date

234
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));

235
 
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col2' at row 1

236
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));

237
 
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date

238
 
INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));

239
 
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col2' at row 1

240
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));

241
 
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1

242
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));

243
 
ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column 'col3' at row 1

244
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));

245
 
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1

246
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));

247
 
ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3' at row 1

248
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));

249
 
ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date

250
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));

251
 
ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col3' at row 1

252
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));

253
 
ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date

254
 
INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));

255
 
ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col3' at row 1

256
 
drop table t1;

257
 
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);

258
 
INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));

259
 
INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));

260
 
INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));

261
 
INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));

262
 
INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));

263
 
ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1

264
 
INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));

265
 
ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1

266
 
INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));

267
 
ERROR 22007: Incorrect datetime value: '0000-00-00'

268
 
INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));

269
 
INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));

270
 
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1

271
 
INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME));

272
 
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1

273
 
INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME));

274
 
ERROR 22007: Incorrect datetime value: '0000-00-00'

275
 
INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME));

276
 
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1

277
 
INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME));

278
 
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1

279
 
INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME));

280
 
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1

281
 
INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME));

282
 
ERROR 22007: Incorrect datetime value: '0000-00-00'

283
 
drop table t1;

284
 
CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);

285
 
INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE));

286
 
INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));

287
 
INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));

288
 
INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));

289
 
INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));

290
 
ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1

291
 
INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));

292
 
ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1

293
 
INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));

294
 
ERROR 22007: Incorrect datetime value: '0000-00-00'

295
 
INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));

296
 
INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));

297
 
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1

298
 
INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME));

299
 
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1

300
 
INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME));

301
 
ERROR 22007: Incorrect datetime value: '0000-00-00'

302
 
INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME));

303
 
ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1

304
 
INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME));

305
 
ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1

306
 
INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME));

307
 
ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1

308
 
INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME));

309
 
ERROR 22007: Incorrect datetime value: '0000-00-00'

310
 
drop table t1;

311
 
CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED);

312
 
INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0);

313
 
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;

314
 
MOD(col1,0)

315
 
NULL

316
 
NULL

317
 
Warnings:

318
 
Error	1365	Division by 0

319
 
Error	1365	Division by 0

320
 
INSERT INTO t1 (col1) VALUES(-129);

321
 
ERROR 22003: Out of range value for column 'col1' at row 1

322
 
INSERT INTO t1 (col1) VALUES(128);

323
 
ERROR 22003: Out of range value for column 'col1' at row 1

324
 
INSERT INTO t1 (col2) VALUES(-1);

325
 
ERROR 22003: Out of range value for column 'col2' at row 1

326
 
INSERT INTO t1 (col2) VALUES(256);

327
 
ERROR 22003: Out of range value for column 'col2' at row 1

328
 
INSERT INTO t1 (col1) VALUES('-129');

329
 
ERROR 22003: Out of range value for column 'col1' at row 1

330
 
INSERT INTO t1 (col1) VALUES('128');

331
 
ERROR 22003: Out of range value for column 'col1' at row 1

332
 
INSERT INTO t1 (col2) VALUES('-1');

333
 
ERROR 22003: Out of range value for column 'col2' at row 1

334
 
INSERT INTO t1 (col2) VALUES('256');

335
 
ERROR 22003: Out of range value for column 'col2' at row 1

336
 
INSERT INTO t1 (col1) VALUES(128.0);

337
 
ERROR 22003: Out of range value for column 'col1' at row 1

338
 
INSERT INTO t1 (col2) VALUES(-1.0);

339
 
ERROR 22003: Out of range value for column 'col2' at row 1

340
 
INSERT INTO t1 (col2) VALUES(256.0);

341
 
ERROR 22003: Out of range value for column 'col2' at row 1

342
 
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 1;

343
 
MOD(col1,0)

344
 
NULL

345
 
Warnings:

346
 
Error	1365	Division by 0

347
 
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;

348
 
ERROR 22003: Out of range value for column 'col1' at row 1

349
 
UPDATE t1 SET col2=col2 + 50 WHERE col2 > 0;

350
 
ERROR 22003: Out of range value for column 'col2' at row 3

351
 
UPDATE t1 SET col1=col1 / 0 WHERE col1 > 0;

352
 
ERROR 22012: Division by 0

353
 
set @@sql_mode='ERROR_FOR_DIVISION_BY_ZERO';

354
 
INSERT INTO t1 values (1/0,1/0);

355
 
Warnings:

356
 
Error	1365	Division by 0

357
 
Error	1365	Division by 0

358
 
set @@sql_mode='ansi,traditional';

359
 
SELECT MOD(col1,0) FROM t1 WHERE col1 > 0 LIMIT 2;

360
 
MOD(col1,0)

361
 
NULL

362
 
NULL

363
 
Warnings:

364
 
Error	1365	Division by 0

365
 
Error	1365	Division by 0

366
 
INSERT INTO t1 (col1) VALUES ('');

367
 
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1

368
 
INSERT INTO t1 (col1) VALUES ('a59b');

369
 
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1

370
 
INSERT INTO t1 (col1) VALUES ('1a');

371
 
ERROR 01000: Data truncated for column 'col1' at row 1

372
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

373
 
Warnings:

374
 
Warning	1265	Data truncated for column 'col1' at row 1

375
 
INSERT IGNORE INTO t1 values (1/0,1/0);

376
 
Warnings:

377
 
Error	1365	Division by 0

378
 
Error	1365	Division by 0

379
 
set @@sql_mode='ansi';

380
 
INSERT INTO t1 values (1/0,1/0);

381
 
set @@sql_mode='ansi,traditional';

382
 
INSERT IGNORE INTO t1 VALUES('-129','-1'),('128','256');

383
 
Warnings:

384
 
Warning	1264	Out of range value for column 'col1' at row 1

385
 
Warning	1264	Out of range value for column 'col2' at row 1

386
 
Warning	1264	Out of range value for column 'col1' at row 2

387
 
Warning	1264	Out of range value for column 'col2' at row 2

388
 
INSERT IGNORE INTO t1 VALUES(-129.0,-1.0),(128.0,256.0);

389
 
Warnings:

390
 
Warning	1264	Out of range value for column 'col1' at row 1

391
 
Warning	1264	Out of range value for column 'col2' at row 1

392
 
Warning	1264	Out of range value for column 'col1' at row 2

393
 
Warning	1264	Out of range value for column 'col2' at row 2

394
 
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;

395
 
SELECT * FROM t1;

396
 
col1	col2

397
 
-128	0

398
 
0	NULL

399
 
127	255

400
 
-128	0

401
 
0	NULL

402
 
127	255

403
 
-128	0

404
 
0	NULL

405
 
127	255

406
 
NULL	NULL

407
 
2	NULL

408
 
NULL	NULL

409
 
NULL	NULL

410
 
-128	0

411
 
127	255

412
 
-128	0

413
 
127	255

414
 
DROP TABLE t1;

415
 
CREATE TABLE t1(col1 SMALLINT, col2 SMALLINT UNSIGNED);

416
 
INSERT INTO t1 VALUES(-32768,0),(0,0),(32767,65535),('-32768','0'),('32767','65535'),(-32768.0,0.0),(32767.0,65535.0);

417
 
INSERT INTO t1 (col1) VALUES(-32769);

418
 
ERROR 22003: Out of range value for column 'col1' at row 1

419
 
INSERT INTO t1 (col1) VALUES(32768);

420
 
ERROR 22003: Out of range value for column 'col1' at row 1

421
 
INSERT INTO t1 (col2) VALUES(-1);

422
 
ERROR 22003: Out of range value for column 'col2' at row 1

423
 
INSERT INTO t1 (col2) VALUES(65536);

424
 
ERROR 22003: Out of range value for column 'col2' at row 1

425
 
INSERT INTO t1 (col1) VALUES('-32769');

426
 
ERROR 22003: Out of range value for column 'col1' at row 1

427
 
INSERT INTO t1 (col1) VALUES('32768');

428
 
ERROR 22003: Out of range value for column 'col1' at row 1

429
 
INSERT INTO t1 (col2) VALUES('-1');

430
 
ERROR 22003: Out of range value for column 'col2' at row 1

431
 
INSERT INTO t1 (col2) VALUES('65536');

432
 
ERROR 22003: Out of range value for column 'col2' at row 1

433
 
INSERT INTO t1 (col1) VALUES(-32769.0);

434
 
ERROR 22003: Out of range value for column 'col1' at row 1

435
 
INSERT INTO t1 (col1) VALUES(32768.0);

436
 
ERROR 22003: Out of range value for column 'col1' at row 1

437
 
INSERT INTO t1 (col2) VALUES(-1.0);

438
 
ERROR 22003: Out of range value for column 'col2' at row 1

439
 
INSERT INTO t1 (col2) VALUES(65536.0);

440
 
ERROR 22003: Out of range value for column 'col2' at row 1

441
 
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;

442
 
ERROR 22003: Out of range value for column 'col1' at row 1

443
 
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;

444
 
ERROR 22003: Out of range value for column 'col2' at row 3

445
 
UPDATE t1 SET col1 = col1 / 0 WHERE col1 > 0;

446
 
ERROR 22012: Division by 0

447
 
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

448
 
ERROR 22012: Division by 0

449
 
INSERT INTO t1 (col1) VALUES ('');

450
 
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1

451
 
INSERT INTO t1 (col1) VALUES ('a59b');

452
 
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1

453
 
INSERT INTO t1 (col1) VALUES ('1a');

454
 
ERROR 01000: Data truncated for column 'col1' at row 1

455
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

456
 
Warnings:

457
 
Warning	1265	Data truncated for column 'col1' at row 1

458
 
INSERT IGNORE INTO t1 values (1/0,1/0);

459
 
Warnings:

460
 
Error	1365	Division by 0

461
 
Error	1365	Division by 0

462
 
INSERT IGNORE INTO t1 VALUES(-32769,-1),(32768,65536);

463
 
Warnings:

464
 
Warning	1264	Out of range value for column 'col1' at row 1

465
 
Warning	1264	Out of range value for column 'col2' at row 1

466
 
Warning	1264	Out of range value for column 'col1' at row 2

467
 
Warning	1264	Out of range value for column 'col2' at row 2

468
 
INSERT IGNORE INTO t1 VALUES('-32769','-1'),('32768','65536');

469
 
Warnings:

470
 
Warning	1264	Out of range value for column 'col1' at row 1

471
 
Warning	1264	Out of range value for column 'col2' at row 1

472
 
Warning	1264	Out of range value for column 'col1' at row 2

473
 
Warning	1264	Out of range value for column 'col2' at row 2

474
 
INSERT IGNORE INTO t1 VALUES(-32769,-1.0),(32768.0,65536.0);

475
 
Warnings:

476
 
Warning	1264	Out of range value for column 'col1' at row 1

477
 
Warning	1264	Out of range value for column 'col2' at row 1

478
 
Warning	1264	Out of range value for column 'col1' at row 2

479
 
Warning	1264	Out of range value for column 'col2' at row 2

480
 
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;

481
 
SELECT * FROM t1;

482
 
col1	col2

483
 
-32768	0

484
 
0	NULL

485
 
32767	65535

486
 
-32768	0

487
 
32767	65535

488
 
-32768	0

489
 
32767	65535

490
 
2	NULL

491
 
NULL	NULL

492
 
-32768	0

493
 
32767	65535

494
 
-32768	0

495
 
32767	65535

496
 
-32768	0

497
 
32767	65535

498
 
DROP TABLE t1;

499
 
CREATE TABLE t1 (col1 MEDIUMINT, col2 MEDIUMINT UNSIGNED);

500
 
INSERT INTO t1 VALUES(-8388608,0),(0,0),(8388607,16777215),('-8388608','0'),('8388607','16777215'),(-8388608.0,0.0),(8388607.0,16777215.0);

501
 
INSERT INTO t1 (col1) VALUES(-8388609);

502
 
ERROR 22003: Out of range value for column 'col1' at row 1

503
 
INSERT INTO t1 (col1) VALUES(8388608);

504
 
ERROR 22003: Out of range value for column 'col1' at row 1

505
 
INSERT INTO t1 (col2) VALUES(-1);

506
 
ERROR 22003: Out of range value for column 'col2' at row 1

507
 
INSERT INTO t1 (col2) VALUES(16777216);

508
 
ERROR 22003: Out of range value for column 'col2' at row 1

509
 
INSERT INTO t1 (col1) VALUES('-8388609');

510
 
ERROR 22003: Out of range value for column 'col1' at row 1

511
 
INSERT INTO t1 (col1) VALUES('8388608');

512
 
ERROR 22003: Out of range value for column 'col1' at row 1

513
 
INSERT INTO t1 (col2) VALUES('-1');

514
 
ERROR 22003: Out of range value for column 'col2' at row 1

515
 
INSERT INTO t1 (col2) VALUES('16777216');

516
 
ERROR 22003: Out of range value for column 'col2' at row 1

517
 
INSERT INTO t1 (col1) VALUES(-8388609.0);

518
 
ERROR 22003: Out of range value for column 'col1' at row 1

519
 
INSERT INTO t1 (col1) VALUES(8388608.0);

520
 
ERROR 22003: Out of range value for column 'col1' at row 1

521
 
INSERT INTO t1 (col2) VALUES(-1.0);

522
 
ERROR 22003: Out of range value for column 'col2' at row 1

523
 
INSERT INTO t1 (col2) VALUES(16777216.0);

524
 
ERROR 22003: Out of range value for column 'col2' at row 1

525
 
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;

526
 
ERROR 22003: Out of range value for column 'col1' at row 1

527
 
UPDATE t1 SET col2 = col2 + 50 WHERE col2 > 0;

528
 
ERROR 22003: Out of range value for column 'col2' at row 3

529
 
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;

530
 
ERROR 22012: Division by 0

531
 
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

532
 
ERROR 22012: Division by 0

533
 
INSERT INTO t1 (col1) VALUES ('');

534
 
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1

535
 
INSERT INTO t1 (col1) VALUES ('a59b');

536
 
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1

537
 
INSERT INTO t1 (col1) VALUES ('1a');

538
 
ERROR 01000: Data truncated for column 'col1' at row 1

539
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

540
 
Warnings:

541
 
Warning	1265	Data truncated for column 'col1' at row 1

542
 
INSERT IGNORE INTO t1 values (1/0,1/0);

543
 
Warnings:

544
 
Error	1365	Division by 0

545
 
Error	1365	Division by 0

546
 
INSERT IGNORE INTO t1 VALUES(-8388609,-1),(8388608,16777216);

547
 
Warnings:

548
 
Warning	1264	Out of range value for column 'col1' at row 1

549
 
Warning	1264	Out of range value for column 'col2' at row 1

550
 
Warning	1264	Out of range value for column 'col1' at row 2

551
 
Warning	1264	Out of range value for column 'col2' at row 2

552
 
INSERT IGNORE INTO t1 VALUES('-8388609','-1'),('8388608','16777216');

553
 
Warnings:

554
 
Warning	1264	Out of range value for column 'col1' at row 1

555
 
Warning	1264	Out of range value for column 'col2' at row 1

556
 
Warning	1264	Out of range value for column 'col1' at row 2

557
 
Warning	1264	Out of range value for column 'col2' at row 2

558
 
INSERT IGNORE INTO t1 VALUES(-8388609.0,-1.0),(8388608.0,16777216.0);

559
 
Warnings:

560
 
Warning	1264	Out of range value for column 'col1' at row 1

561
 
Warning	1264	Out of range value for column 'col2' at row 1

562
 
Warning	1264	Out of range value for column 'col1' at row 2

563
 
Warning	1264	Out of range value for column 'col2' at row 2

564
 
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;

565
 
SELECT * FROM t1;

566
 
col1	col2

567
 
-8388608	0

568
 
0	NULL

569
 
8388607	16777215

570
 
-8388608	0

571
 
8388607	16777215

572
 
-8388608	0

573
 
8388607	16777215

574
 
2	NULL

575
 
NULL	NULL

576
 
-8388608	0

577
 
8388607	16777215

578
 
-8388608	0

579
 
8388607	16777215

580
 
-8388608	0

581
 
8388607	16777215

582
 
DROP TABLE t1;

583
 
CREATE TABLE t1 (col1 INT, col2 INT UNSIGNED);

584
 
INSERT INTO t1 VALUES(-2147483648,0),(0,0),(2147483647,4294967295),('-2147483648','0'),('2147483647','4294967295'),(-2147483648.0,0.0),(2147483647.0,4294967295.0);

585
 
INSERT INTO t1 (col1) VALUES(-2147483649);

586
 
ERROR 22003: Out of range value for column 'col1' at row 1

587
 
INSERT INTO t1 (col1) VALUES(2147643648);

588
 
ERROR 22003: Out of range value for column 'col1' at row 1

589
 
INSERT INTO t1 (col2) VALUES(-1);

590
 
ERROR 22003: Out of range value for column 'col2' at row 1

591
 
INSERT INTO t1 (col2) VALUES(4294967296);

592
 
ERROR 22003: Out of range value for column 'col2' at row 1

593
 
INSERT INTO t1 (col1) VALUES('-2147483649');

594
 
ERROR 22003: Out of range value for column 'col1' at row 1

595
 
INSERT INTO t1 (col1) VALUES('2147643648');

596
 
ERROR 22003: Out of range value for column 'col1' at row 1

597
 
INSERT INTO t1 (col2) VALUES('-1');

598
 
ERROR 22003: Out of range value for column 'col2' at row 1

599
 
INSERT INTO t1 (col2) VALUES('4294967296');

600
 
ERROR 22003: Out of range value for column 'col2' at row 1

601
 
INSERT INTO t1 (col1) VALUES(-2147483649.0);

602
 
ERROR 22003: Out of range value for column 'col1' at row 1

603
 
INSERT INTO t1 (col1) VALUES(2147643648.0);

604
 
ERROR 22003: Out of range value for column 'col1' at row 1

605
 
INSERT INTO t1 (col2) VALUES(-1.0);

606
 
ERROR 22003: Out of range value for column 'col2' at row 1

607
 
INSERT INTO t1 (col2) VALUES(4294967296.0);

608
 
ERROR 22003: Out of range value for column 'col2' at row 1

609
 
UPDATE t1 SET col1 = col1 - 50 WHERE col1 < 0;

610
 
ERROR 22003: Out of range value for column 'col1' at row 1

611
 
UPDATE t1 SET col2 =col2 + 50 WHERE col2 > 0;

612
 
ERROR 22003: Out of range value for column 'col2' at row 3

613
 
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;

614
 
ERROR 22012: Division by 0

615
 
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

616
 
ERROR 22012: Division by 0

617
 
INSERT INTO t1 (col1) VALUES ('');

618
 
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1

619
 
INSERT INTO t1 (col1) VALUES ('a59b');

620
 
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1

621
 
INSERT INTO t1 (col1) VALUES ('1a');

622
 
ERROR 01000: Data truncated for column 'col1' at row 1

623
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

624
 
Warnings:

625
 
Warning	1265	Data truncated for column 'col1' at row 1

626
 
INSERT IGNORE INTO t1 values (1/0,1/0);

627
 
Warnings:

628
 
Error	1365	Division by 0

629
 
Error	1365	Division by 0

630
 
INSERT IGNORE INTO t1 values (-2147483649, -1),(2147643648,4294967296);

631
 
Warnings:

632
 
Warning	1264	Out of range value for column 'col1' at row 1

633
 
Warning	1264	Out of range value for column 'col2' at row 1

634
 
Warning	1264	Out of range value for column 'col1' at row 2

635
 
Warning	1264	Out of range value for column 'col2' at row 2

636
 
INSERT IGNORE INTO t1 values ('-2147483649', '-1'),('2147643648','4294967296');

637
 
Warnings:

638
 
Warning	1264	Out of range value for column 'col1' at row 1

639
 
Warning	1264	Out of range value for column 'col2' at row 1

640
 
Warning	1264	Out of range value for column 'col1' at row 2

641
 
Warning	1264	Out of range value for column 'col2' at row 2

642
 
INSERT IGNORE INTO t1 values (-2147483649.0, -1.0),(2147643648.0,4294967296.0);

643
 
Warnings:

644
 
Warning	1264	Out of range value for column 'col1' at row 1

645
 
Warning	1264	Out of range value for column 'col2' at row 1

646
 
Warning	1264	Out of range value for column 'col1' at row 2

647
 
Warning	1264	Out of range value for column 'col2' at row 2

648
 
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;

649
 
SELECT * FROM t1;

650
 
col1	col2

651
 
-2147483648	0

652
 
0	NULL

653
 
2147483647	4294967295

654
 
-2147483648	0

655
 
2147483647	4294967295

656
 
-2147483648	0

657
 
2147483647	4294967295

658
 
2	NULL

659
 
NULL	NULL

660
 
-2147483648	0

661
 
2147483647	4294967295

662
 
-2147483648	0

663
 
2147483647	4294967295

664
 
-2147483648	0

665
 
2147483647	4294967295

666
 
DROP TABLE t1;

667
 
CREATE TABLE t1 (col1 BIGINT, col2 BIGINT UNSIGNED);

668
 
INSERT INTO t1 VALUES(-9223372036854775808,0),(0,0),(9223372036854775807,18446744073709551615);

669
 
INSERT INTO t1 VALUES('-9223372036854775808','0'),('9223372036854775807','18446744073709551615');

670
 
INSERT INTO t1 VALUES(-9223372036854774000.0,0.0),(9223372036854775700.0,1844674407370954000.0);

671
 
INSERT INTO t1 (col1) VALUES(-9223372036854775809);

672
 
ERROR 22003: Out of range value for column 'col1' at row 1

673
 
INSERT INTO t1 (col1) VALUES(9223372036854775808);

674
 
ERROR 22003: Out of range value for column 'col1' at row 1

675
 
INSERT INTO t1 (col2) VALUES(-1);

676
 
ERROR 22003: Out of range value for column 'col2' at row 1

677
 
INSERT INTO t1 (col2) VALUES(18446744073709551616);

678
 
ERROR 22003: Out of range value for column 'col2' at row 1

679
 
INSERT INTO t1 (col1) VALUES('-9223372036854775809');

680
 
ERROR 22003: Out of range value for column 'col1' at row 1

681
 
INSERT INTO t1 (col1) VALUES('9223372036854775808');

682
 
ERROR 22003: Out of range value for column 'col1' at row 1

683
 
INSERT INTO t1 (col2) VALUES('-1');

684
 
ERROR 22003: Out of range value for column 'col2' at row 1

685
 
INSERT INTO t1 (col2) VALUES('18446744073709551616');

686
 
ERROR 22003: Out of range value for column 'col2' at row 1

687
 
INSERT INTO t1 (col1) VALUES(-9223372036854785809.0);

688
 
ERROR 22003: Out of range value for column 'col1' at row 1

689
 
INSERT INTO t1 (col1) VALUES(9223372036854785808.0);

690
 
ERROR 22003: Out of range value for column 'col1' at row 1

691
 
INSERT INTO t1 (col2) VALUES(-1.0);

692
 
ERROR 22003: Out of range value for column 'col2' at row 1

693
 
INSERT INTO t1 (col2) VALUES(18446744073709551616.0);

694
 
ERROR 22003: Out of range value for column 'col2' at row 1

695
 
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;

696
 
ERROR 22012: Division by 0

697
 
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

698
 
ERROR 22012: Division by 0

699
 
INSERT INTO t1 (col1) VALUES ('');

700
 
ERROR HY000: Incorrect integer value: '' for column 'col1' at row 1

701
 
INSERT INTO t1 (col1) VALUES ('a59b');

702
 
ERROR HY000: Incorrect integer value: 'a59b' for column 'col1' at row 1

703
 
INSERT INTO t1 (col1) VALUES ('1a');

704
 
ERROR 01000: Data truncated for column 'col1' at row 1

705
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

706
 
Warnings:

707
 
Warning	1265	Data truncated for column 'col1' at row 1

708
 
INSERT IGNORE INTO t1 values (1/0,1/0);

709
 
Warnings:

710
 
Error	1365	Division by 0

711
 
Error	1365	Division by 0

712
 
INSERT IGNORE INTO t1 VALUES(-9223372036854775809,-1),(9223372036854775808,18446744073709551616);

713
 
Warnings:

714
 
Warning	1264	Out of range value for column 'col1' at row 1

715
 
Warning	1264	Out of range value for column 'col2' at row 1

716
 
Warning	1264	Out of range value for column 'col1' at row 2

717
 
Warning	1264	Out of range value for column 'col2' at row 2

718
 
INSERT IGNORE INTO t1 VALUES('-9223372036854775809','-1'),('9223372036854775808','18446744073709551616');

719
 
Warnings:

720
 
Warning	1264	Out of range value for column 'col1' at row 1

721
 
Warning	1264	Out of range value for column 'col2' at row 1

722
 
Warning	1264	Out of range value for column 'col1' at row 2

723
 
Warning	1264	Out of range value for column 'col2' at row 2

724
 
INSERT IGNORE INTO t1 VALUES(-9223372036854785809.0,-1.0),(9223372036854785808.0,18446744073709551616.0);

725
 
Warnings:

726
 
Warning	1264	Out of range value for column 'col1' at row 1

727
 
Warning	1264	Out of range value for column 'col2' at row 1

728
 
Warning	1264	Out of range value for column 'col1' at row 2

729
 
Warning	1264	Out of range value for column 'col2' at row 2

730
 
UPDATE IGNORE t1 SET col2=1/NULL where col1=0;

731
 
SELECT * FROM t1;

732
 
col1	col2

733
 
-9223372036854775808	0

734
 
0	NULL

735
 
9223372036854775807	18446744073709551615

736
 
-9223372036854775808	0

737
 
9223372036854775807	18446744073709551615

738
 
-9223372036854774000	0

739
 
9223372036854775700	1844674407370954000

740
 
2	NULL

741
 
NULL	NULL

742
 
-9223372036854775808	0

743
 
9223372036854775807	18446744073709551615

744
 
-9223372036854775808	0

745
 
9223372036854775807	18446744073709551615

746
 
-9223372036854775808	0

747
 
9223372036854775807	18446744073709551615

748
 
DROP TABLE t1;

749
 
CREATE TABLE t1 (col1 NUMERIC(4,2));

750
 
INSERT INTO t1 VALUES (10.55),(10.5555),(0),(-10.55),(-10.5555),(11),(1e+01);

751
 
Warnings:

752
 
Note	1265	Data truncated for column 'col1' at row 2

753
 
Note	1265	Data truncated for column 'col1' at row 5

754
 
INSERT INTO t1 VALUES ('10.55'),('10.5555'),('-10.55'),('-10.5555'),('11'),('1e+01');

755
 
Warnings:

756
 
Note	1265	Data truncated for column 'col1' at row 2

757
 
Note	1265	Data truncated for column 'col1' at row 4

758
 
INSERT INTO t1 VALUES (101.55);

759
 
ERROR 22003: Out of range value for column 'col1' at row 1

760
 
INSERT INTO t1 VALUES (101);

761
 
ERROR 22003: Out of range value for column 'col1' at row 1

762
 
INSERT INTO t1 VALUES (-101.55);

763
 
ERROR 22003: Out of range value for column 'col1' at row 1

764
 
INSERT INTO t1 VALUES (1010.55);

765
 
ERROR 22003: Out of range value for column 'col1' at row 1

766
 
INSERT INTO t1 VALUES (1010);

767
 
ERROR 22003: Out of range value for column 'col1' at row 1

768
 
INSERT INTO t1 VALUES ('101.55');

769
 
ERROR 22003: Out of range value for column 'col1' at row 1

770
 
INSERT INTO t1 VALUES ('101');

771
 
ERROR 22003: Out of range value for column 'col1' at row 1

772
 
INSERT INTO t1 VALUES ('-101.55');

773
 
ERROR 22003: Out of range value for column 'col1' at row 1

774
 
INSERT INTO t1 VALUES ('-1010.55');

775
 
ERROR 22003: Out of range value for column 'col1' at row 1

776
 
INSERT INTO t1 VALUES ('-100E+1');

777
 
ERROR 22003: Out of range value for column 'col1' at row 1

778
 
INSERT INTO t1 VALUES ('-100E');

779
 
ERROR HY000: Incorrect decimal value: '-100E' for column 'col1' at row 1

780
 
UPDATE t1 SET col1 =col1 * 50000 WHERE col1 =11;

781
 
ERROR 22003: Out of range value for column 'col1' at row 6

782
 
UPDATE t1 SET col1 =col1 / 0 WHERE col1 > 0;

783
 
ERROR 22012: Division by 0

784
 
UPDATE t1 SET col1= MOD(col1,0) WHERE col1 > 0;

785
 
ERROR 22012: Division by 0

786
 
INSERT INTO t1 (col1) VALUES ('');

787
 
ERROR HY000: Incorrect decimal value: '' for column 'col1' at row 1

788
 
INSERT INTO t1 (col1) VALUES ('a59b');

789
 
ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1

790
 
INSERT INTO t1 (col1) VALUES ('1a');

791
 
ERROR HY000: Incorrect decimal value: '1a' for column 'col1' at row 1

792
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

793
 
Warnings:

794
 
Note	1265	Data truncated for column 'col1' at row 1

795
 
INSERT IGNORE INTO t1 values (1/0);

796
 
Warnings:

797
 
Error	1365	Division by 0

798
 
INSERT IGNORE INTO t1 VALUES(1000),(-1000);

799
 
Warnings:

800
 
Warning	1264	Out of range value for column 'col1' at row 1

801
 
Warning	1264	Out of range value for column 'col1' at row 2

802
 
INSERT IGNORE INTO t1 VALUES('1000'),('-1000');

803
 
Warnings:

804
 
Warning	1264	Out of range value for column 'col1' at row 1

805
 
Warning	1264	Out of range value for column 'col1' at row 2

806
 
INSERT IGNORE INTO t1 VALUES(1000.0),(-1000.0);

807
 
Warnings:

808
 
Warning	1264	Out of range value for column 'col1' at row 1

809
 
Warning	1264	Out of range value for column 'col1' at row 2

810
 
UPDATE IGNORE t1 SET col1=1/NULL where col1=0;

811
 
SELECT * FROM t1;

812
 
col1

813
 
10.55

814
 
10.56

815
 
NULL

816
 
-10.55

817
 
-10.56

818
 
11.00

819
 
10.00

820
 
10.55

821
 
10.56

822
 
-10.55

823
 
-10.56

824
 
11.00

825
 
10.00

826
 
2.00

827
 
NULL

828
 
99.99

829
 
-99.99

830
 
99.99

831
 
-99.99

832
 
99.99

833
 
-99.99

834
 
DROP TABLE t1;

835
 
CREATE TABLE t1 (col1 FLOAT, col2 FLOAT UNSIGNED);

836
 
INSERT INTO t1 VALUES (-1.1E-37,0),(+3.4E+38,+3.4E+38);

837
 
INSERT INTO t1 VALUES ('-1.1E-37',0),('+3.4E+38','+3.4E+38');

838
 
INSERT INTO t1 (col1) VALUES (3E-46);

839
 
INSERT INTO t1 (col1) VALUES (+3.4E+39);

840
 
ERROR 22003: Out of range value for column 'col1' at row 1

841
 
INSERT INTO t1 (col2) VALUES (-1.1E-3);

842
 
ERROR 22003: Out of range value for column 'col2' at row 1

843
 
INSERT INTO t1 (col1) VALUES ('+3.4E+39');

844
 
ERROR 22003: Out of range value for column 'col1' at row 1

845
 
INSERT INTO t1 (col2) VALUES ('-1.1E-3');

846
 
ERROR 22003: Out of range value for column 'col2' at row 1

847
 
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;

848
 
ERROR 22003: Out of range value for column 'col1' at row 2

849
 
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;

850
 
ERROR 22012: Division by 0

851
 
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;

852
 
ERROR 22012: Division by 0

853
 
INSERT INTO t1 (col1) VALUES ('');

854
 
ERROR 01000: Data truncated for column 'col1' at row 1

855
 
INSERT INTO t1 (col1) VALUES ('a59b');

856
 
ERROR 01000: Data truncated for column 'col1' at row 1

857
 
INSERT INTO t1 (col1) VALUES ('1a');

858
 
ERROR 01000: Data truncated for column 'col1' at row 1

859
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

860
 
Warnings:

861
 
Warning	1265	Data truncated for column 'col1' at row 1

862
 
INSERT IGNORE INTO t1 (col1) VALUES (1/0);

863
 
Warnings:

864
 
Error	1365	Division by 0

865
 
INSERT IGNORE INTO t1 VALUES (+3.4E+39,-3.4E+39);

866
 
Warnings:

867
 
Warning	1264	Out of range value for column 'col1' at row 1

868
 
Warning	1264	Out of range value for column 'col2' at row 1

869
 
INSERT IGNORE INTO t1 VALUES ('+3.4E+39','-3.4E+39');

870
 
Warnings:

871
 
Warning	1264	Out of range value for column 'col1' at row 1

872
 
Warning	1264	Out of range value for column 'col2' at row 1

873
 
SELECT * FROM t1;

874
 
col1	col2

875
 
-1.1e-37	0

876
 
3.4e38	3.4e38

877
 
-1.1e-37	0

878
 
3.4e38	3.4e38

879
 
0	NULL

880
 
2	NULL

881
 
NULL	NULL

882
 
3.40282e38	0

883
 
3.40282e38	0

884
 
DROP TABLE t1;

885
 
CREATE TABLE t1 (col1 DOUBLE PRECISION, col2 DOUBLE PRECISION UNSIGNED);

886
 
INSERT INTO t1 VALUES (-2.2E-307,0),(2E-307,0),(+1.7E+308,+1.7E+308);

887
 
INSERT INTO t1 VALUES ('-2.2E-307',0),('-2E-307',0),('+1.7E+308','+1.7E+308');

888
 
INSERT INTO t1 (col1) VALUES (-2.2E-330);

889
 
INSERT INTO t1 (col1) VALUES (+1.7E+309);

890
 
Got one of the listed errors

891
 
INSERT INTO t1 (col2) VALUES (-1.1E-3);

892
 
ERROR 22003: Out of range value for column 'col2' at row 1

893
 
INSERT INTO t1 (col1) VALUES ('+1.8E+309');

894
 
ERROR 22003: Out of range value for column 'col1' at row 1

895
 
INSERT INTO t1 (col2) VALUES ('-1.2E-3');

896
 
ERROR 22003: Out of range value for column 'col2' at row 1

897
 
UPDATE t1 SET col1 =col1 * 5000 WHERE col1 > 0;

898
 
UPDATE t1 SET col2 =col2 / 0 WHERE col2 > 0;

899
 
ERROR 22012: Division by 0

900
 
UPDATE t1 SET col2= MOD(col2,0) WHERE col2 > 0;

901
 
ERROR 22012: Division by 0

902
 
INSERT INTO t1 (col1) VALUES ('');

903
 
ERROR 01000: Data truncated for column 'col1' at row 1

904
 
INSERT INTO t1 (col1) VALUES ('a59b');

905
 
ERROR 01000: Data truncated for column 'col1' at row 1

906
 
INSERT INTO t1 (col1) VALUES ('1a');

907
 
ERROR 01000: Data truncated for column 'col1' at row 1

908
 
INSERT IGNORE INTO t1 (col1) VALUES ('2a');

909
 
Warnings:

910
 
Warning	1265	Data truncated for column 'col1' at row 1

911
 
INSERT IGNORE INTO t1 (col1) values (1/0);

912
 
Warnings:

913
 
Error	1365	Division by 0

914
 
INSERT IGNORE INTO t1 VALUES (+1.9E+309,-1.9E+309);

915
 
ERROR 22007: Illegal double '1.9E+309' value found during parsing

916
 
INSERT IGNORE INTO t1 VALUES ('+2.0E+309','-2.0E+309');

917
 
Warnings:

918
 
Warning	1264	Out of range value for column 'col1' at row 1

919
 
Warning	1264	Out of range value for column 'col2' at row 1

920
 
Warning	1264	Out of range value for column 'col2' at row 1

921
 
SELECT * FROM t1;

922
 
col1	col2

923
 
-2.2e-307	0

924
 
1e-303	0

925
 
NULL	1.7e308

926
 
-2.2e-307	0

927
 
-2e-307	0

928
 
NULL	1.7e308

929
 
0	NULL

930
 
2	NULL

931
 
NULL	NULL

932
 
1.7976931348623157e308	0

933
 
DROP TABLE t1;

934
 
CREATE TABLE t1 (col1 CHAR(5), col2 VARCHAR(6));

935
 
INSERT INTO t1 VALUES ('hello', 'hello'),('he', 'he'),('hello   ', 'hello ');

936
 
Warnings:

937
 
Note	1265	Data truncated for column 'col1' at row 3

938
 
INSERT INTO t1 (col1) VALUES ('hellobob');

939
 
ERROR 22001: Data too long for column 'col1' at row 1

940
 
INSERT INTO t1 (col2) VALUES ('hellobob');

941
 
ERROR 22001: Data too long for column 'col2' at row 1

942
 
INSERT INTO t1 (col2) VALUES ('hello  ');

943
 
Warnings:

944
 
Note	1265	Data truncated for column 'col2' at row 1

945
 
UPDATE t1 SET col1 ='hellobob' WHERE col1 ='he';

946
 
ERROR 22001: Data too long for column 'col1' at row 2

947
 
UPDATE t1 SET col2 ='hellobob' WHERE col2 ='he';

948
 
ERROR 22001: Data too long for column 'col2' at row 2

949
 
INSERT IGNORE INTO t1 VALUES ('hellobob', 'hellobob');

950
 
Warnings:

951
 
Warning	1265	Data truncated for column 'col1' at row 1

952
 
Warning	1265	Data truncated for column 'col2' at row 1

953
 
UPDATE IGNORE t1 SET col2 ='hellotrudy' WHERE col2 ='he';

954
 
Warnings:

955
 
Warning	1265	Data truncated for column 'col2' at row 2

956
 
SELECT * FROM t1;

957
 
col1	col2

958
 
hello	hello

959
 
he	hellot

960
 
hello	hello 

961
 
NULL	hello 

962
 
hello	hellob

963
 
DROP TABLE t1;

964
 
CREATE TABLE t1 (col1 enum('red','blue','green'));

965
 
INSERT INTO t1 VALUES ('red'),('blue'),('green');

966
 
INSERT INTO t1 (col1) VALUES ('yellow');

967
 
ERROR 01000: Data truncated for column 'col1' at row 1

968
 
INSERT INTO t1 (col1) VALUES ('redd');

969
 
ERROR 01000: Data truncated for column 'col1' at row 1

970
 
INSERT INTO t1 VALUES ('');

971
 
ERROR 01000: Data truncated for column 'col1' at row 1

972
 
UPDATE t1 SET col1 ='yellow' WHERE col1 ='green';

973
 
ERROR 01000: Data truncated for column 'col1' at row 3

974
 
INSERT IGNORE INTO t1 VALUES ('yellow');

975
 
Warnings:

976
 
Warning	1265	Data truncated for column 'col1' at row 1

977
 
UPDATE IGNORE t1 SET col1 ='yellow' WHERE col1 ='blue';

978
 
Warnings:

979
 
Warning	1265	Data truncated for column 'col1' at row 2

980
 
SELECT * FROM t1;

981
 
col1

982
 
red

983
 









984



green










985












986



DROP TABLE t1;










987



CREATE TABLE t1 (col1 INT NOT NULL, col2 CHAR(5) NOT NULL, col3 DATE NOT NULL);










988



INSERT INTO t1 VALUES (100, 'hello', '2004-08-20');










989



INSERT INTO t1 (col1,col2,col3) VALUES (101, 'hell2', '2004-08-21');










990



INSERT INTO t1 (col1,col2,col3) VALUES (NULL, '', '2004-01-01');










991



ERROR 23000: Column 'col1' cannot be null










992



INSERT INTO t1 (col1,col2,col3) VALUES (102, NULL, '2004-01-01');










993



ERROR 23000: Column 'col2' cannot be null










994



INSERT INTO t1 VALUES (103,'',NULL);










995



ERROR 23000: Column 'col3' cannot be null










996



UPDATE t1 SET col1=NULL WHERE col1 =100;










997



ERROR 23000: Column 'col1' cannot be null










998



UPDATE t1 SET col2 =NULL WHERE col2 ='hello';










999



ERROR 23000: Column 'col2' cannot be null










1000



UPDATE t1 SET col2 =NULL where col3 IS NOT NULL;










1001



ERROR 23000: Column 'col2' cannot be null










1002



INSERT IGNORE INTO t1 values (NULL,NULL,NULL);










1003



Warnings:










1004



Warning	1048	Column 'col1' cannot be null










1005



Warning	1048	Column 'col2' cannot be null










1006



Warning	1048	Column 'col3' cannot be null










1007



SELECT * FROM t1;










1008



col1	col2	col3










1009



100	hello	2004-08-20










1010



101	hell2	2004-08-21










1011



0		0000-00-00










1012



DROP TABLE t1;










1013



CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);










1014



SHOW CREATE TABLE t1;










1015



Table	Create Table










1016



t1	CREATE TABLE "t1" (










1017



  "col1" int(11) NOT NULL DEFAULT '99',










1018



  "col2" char(6) NOT NULL










1019



)










1020



INSERT INTO t1 VALUES (1, 'hello');










1021



INSERT INTO t1 (col2) VALUES ('hello2');










1022



INSERT INTO t1 (col2) VALUES (NULL);










1023



ERROR 23000: Column 'col2' cannot be null










1024



INSERT INTO t1 (col1) VALUES (2);










1025



ERROR HY000: Field 'col2' doesn't have a default value










1026



INSERT INTO t1 VALUES(default(col1),default(col2));










1027



ERROR HY000: Field 'col2' doesn't have a default value










1028



INSERT INTO t1 (col1) SELECT 1;










1029



ERROR HY000: Field 'col2' doesn't have a default value










1030



INSERT INTO t1 SELECT 1,NULL;










1031



ERROR 23000: Column 'col2' cannot be null










1032



INSERT IGNORE INTO t1 values (NULL,NULL);










1033



Warnings:










1034



Warning	1048	Column 'col1' cannot be null










1035



Warning	1048	Column 'col2' cannot be null










1036



INSERT IGNORE INTO t1 (col1) values (3);










1037



Warnings:










1038



Warning	1364	Field 'col2' doesn't have a default value










1039



INSERT IGNORE INTO t1 () values ();










1040



Warnings:










1041



Warning	1364	Field 'col2' doesn't have a default value










1042



SELECT * FROM t1;










1043



col1	col2










1044



1	hello










1045



99	hello2










1046



0	










1047



3	










1048



99	










1049



DROP TABLE t1;










1050



set sql_mode='traditional';










1051



create table t1 (charcol char(255), varcharcol varchar(255),










1052



binarycol binary(255), varbinarycol varbinary(255), tinytextcol tinytext,










1053



tinyblobcol tinyblob);










1054



insert into t1 (charcol) values (repeat('x',256));










1055



ERROR 22001: Data too long for column 'charcol' at row 1










1056



insert into t1 (varcharcol) values (repeat('x',256));










1057



ERROR 22001: Data too long for column 'varcharcol' at row 1










1058



insert into t1 (binarycol) values (repeat('x',256));










1059



ERROR 22001: Data too long for column 'binarycol' at row 1










1060



insert into t1 (varbinarycol) values (repeat('x',256));










1061



ERROR 22001: Data too long for column 'varbinarycol' at row 1










1062



insert into t1 (tinytextcol) values (repeat('x',256));










1063



ERROR 22001: Data too long for column 'tinytextcol' at row 1










1064



insert into t1 (tinyblobcol) values (repeat('x',256));










1065



ERROR 22001: Data too long for column 'tinyblobcol' at row 1










1066



select * from t1;










1067



charcol	varcharcol	binarycol	varbinarycol	tinytextcol	tinyblobcol










1068



drop table t1;










1069



set sql_mode='traditional';










1070



create table t1 (col1 datetime);










1071



insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));










1072



ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc'










1073



insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));










1074



ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_date










1075



insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));










1076



ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_date










1077



insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));










1078



ERROR HY000: Incorrect time value: 'abc' for function str_to_date










1079



set sql_mode='';










1080



insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i'));










1081



Warnings:










1082



Warning	1292	Truncated incorrect datetime value: '31.10.2004 15.30 abc'










1083



insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));










1084



Warnings:










1085



Error	1411	Incorrect datetime value: '32.10.2004 15.30' for function str_to_date










1086



insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r'));










1087



Warnings:










1088



Error	1411	Incorrect time value: '22:22:33 AM' for function str_to_date










1089



insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T'));










1090



Warnings:










1091



Error	1411	Incorrect time value: 'abc' for function str_to_date










1092



insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i'));










1093



insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r'));










1094



insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T'));










1095



select * from t1;










1096



col1










1097



2004-10-31 15:30:00










1098



NULL










1099



NULL










1100



NULL










1101



2004-10-31 15:30:00










1102



2004-12-12 11:22:33










1103



2004-12-12 10:22:59










1104



set sql_mode='traditional';










1105



select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL;










1106



count(*)










1107



7










1108



Warnings:










1109



Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date










1110



Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date










1111



Error	1411	Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_date










1112



drop table t1;










1113



create table t1 (col1 char(3), col2 integer);










1114



insert into t1 (col1) values (cast(1000 as char(3)));










1115



ERROR 22007: Truncated incorrect CHAR(3) value: '1000'










1116



insert into t1 (col1) values (cast(1000E+0 as char(3)));










1117



ERROR 22007: Truncated incorrect CHAR(3) value: '1000'










1118



insert into t1 (col1) values (cast(1000.0 as char(3)));










1119



ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0'










1120



insert into t1 (col2) values (cast('abc' as signed integer));










1121



ERROR 22007: Truncated incorrect INTEGER value: 'abc'










1122



insert into t1 (col2) values (10E+0 + 'a');










1123



ERROR 22007: Truncated incorrect DOUBLE value: 'a'










1124



insert into t1 (col2) values (cast('10a' as unsigned integer));










1125



ERROR 22007: Truncated incorrect INTEGER value: '10a'










1126



insert into t1 (col2) values (cast('10' as unsigned integer));










1127



insert into t1 (col2) values (cast('10' as signed integer));










1128



insert into t1 (col2) values (10E+0 + '0 ');










1129



select * from t1;










1130



col1	col2










1131



NULL	10










1132



NULL	10










1133



NULL	10










1134



drop table t1;










1135



create table t1 (col1 date, col2 datetime, col3 timestamp);










1136



insert into t1 values (0,0,0);










1137



ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1










1138



insert into t1 values (0.0,0.0,0.0);










1139



ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1










1140



insert into t1 (col1) values (convert('0000-00-00',date));










1141



ERROR 22007: Incorrect datetime value: '0000-00-00'










1142



insert into t1 (col1) values (cast('0000-00-00' as date));










1143



ERROR 22007: Incorrect datetime value: '0000-00-00'










1144



set sql_mode='no_zero_date';










1145



insert into t1 values (0,0,0);










1146



Warnings:










1147



Warning	1264	Out of range value for column 'col1' at row 1










1148



Warning	1264	Out of range value for column 'col2' at row 1










1149



Warning	1265	Data truncated for column 'col3' at row 1










1150



insert into t1 values (0.0,0.0,0.0);










1151



Warnings:










1152



Warning	1264	Out of range value for column 'col1' at row 1










1153



Warning	1264	Out of range value for column 'col2' at row 1










1154



Warning	1265	Data truncated for column 'col3' at row 1










1155



drop table t1;










1156



set sql_mode='traditional';










1157



create table t1 (col1 date);










1158



insert ignore into t1 values ('0000-00-00');










1159



Warnings:










1160



Warning	1265	Data truncated for column 'col1' at row 1










1161



insert into t1 select * from t1;










1162



ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1










1163



insert ignore into t1 values ('0000-00-00');










1164



Warnings:










1165



Warning	1265	Data truncated for column 'col1' at row 1










1166



insert ignore into t1 (col1) values (cast('0000-00-00' as date));










1167



Warnings:










1168



Warning	1292	Incorrect datetime value: '0000-00-00'










1169



insert into t1 select * from t1;










1170



ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1










1171



alter table t1 modify col1 datetime;










1172



ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1










1173



alter ignore table t1 modify col1 datetime;










1174



Warnings:










1175



Warning	1264	Out of range value for column 'col1' at row 1










1176



Warning	1264	Out of range value for column 'col1' at row 2










1177



insert into t1 select * from t1;










1178



ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1










1179



select * from t1;










1180



col1










1181



0000-00-00 00:00:00










1182



0000-00-00 00:00:00










1183



NULL










1184



drop table t1;










1185



create table t1 (col1 tinyint);










1186



drop procedure if exists t1;










1187



Warnings:










1188



Note	1305	PROCEDURE t1 does not exist










1189



create procedure t1 () begin declare exit handler for sqlexception










1190



select'a'; insert into t1 values (200); end;|










1191



call t1();










1192



a










1193



a










1194



select * from t1;










1195



col1










1196



drop procedure t1;










1197



drop table t1;










1198



set sql_mode=@org_mode;










1199



SET @@sql_mode = 'traditional';










1200



CREATE TABLE t1 (i int not null);










1201



INSERT INTO t1 VALUES ();










1202



ERROR HY000: Field 'i' doesn't have a default value










1203



INSERT INTO t1 VALUES (DEFAULT);










1204



ERROR HY000: Field 'i' doesn't have a default value










1205



INSERT INTO t1 VALUES (DEFAULT(i));










1206



ERROR HY000: Field 'i' doesn't have a default value










1207



ALTER TABLE t1 ADD j int;










1208



INSERT INTO t1 SET j = 1;










1209



ERROR HY000: Field 'i' doesn't have a default value










1210



INSERT INTO t1 SET j = 1, i = DEFAULT;










1211



ERROR HY000: Field 'i' doesn't have a default value










1212



INSERT INTO t1 SET j = 1, i = DEFAULT(i);










1213



ERROR HY000: Field 'i' doesn't have a default value










1214



INSERT INTO t1 VALUES (DEFAULT,1);










1215



ERROR HY000: Field 'i' doesn't have a default value










1216



DROP TABLE t1;










1217



SET @@sql_mode = '';










1218



CREATE TABLE t1 (i int not null);










1219



INSERT INTO t1 VALUES ();










1220



Warnings:










1221



Warning	1364	Field 'i' doesn't have a default value










1222



INSERT INTO t1 VALUES (DEFAULT);










1223



Warnings:










1224



Warning	1364	Field 'i' doesn't have a default value










1225



INSERT INTO t1 VALUES (DEFAULT(i));










1226



ERROR HY000: Field 'i' doesn't have a default value










1227



ALTER TABLE t1 ADD j int;










1228



INSERT INTO t1 SET j = 1;










1229



Warnings:










1230



Warning	1364	Field 'i' doesn't have a default value










1231



INSERT INTO t1 SET j = 1, i = DEFAULT;










1232



Warnings:










1233



Warning	1364	Field 'i' doesn't have a default value










1234



INSERT INTO t1 SET j = 1, i = DEFAULT(i);










1235



ERROR HY000: Field 'i' doesn't have a default value










1236



INSERT INTO t1 VALUES (DEFAULT,1);










1237



Warnings:










1238



Warning	1364	Field 'i' doesn't have a default value










1239



DROP TABLE t1;










1240



set @@sql_mode='traditional';










1241



create table t1(a varchar(65537));










1242



ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead










1243



create table t1(a varbinary(65537));










1244



ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead










1245



set @@sql_mode='traditional';










1246



create table t1(a int, b date not null);










1247



alter table t1 modify a bigint unsigned not null;










1248



show create table t1;










1249



Table	Create Table










1250



t1	CREATE TABLE `t1` (










1251



  `a` bigint(20) unsigned NOT NULL,










1252



  `b` date NOT NULL










1253



) ENGINE=MyISAM DEFAULT CHARSET=latin1










1254



drop table t1;










1255



set @@sql_mode='traditional';










1256



create table t1 (d date);










1257



insert into t1 values ('2000-10-00');










1258



ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1










1259



insert into t1 values (1000);










1260



ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1










1261



insert into t1 values ('2000-10-01');










1262



update t1 set d = 1100;










1263



ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1










1264



select * from t1;










1265



d










1266



2000-10-01










1267



drop table t1;










1268



set @@sql_mode='traditional';










1269



create table t1(a int, b timestamp);










1270



alter table t1 add primary key(a);










1271



show create table t1;










1272



Table	Create Table










1273



t1	CREATE TABLE `t1` (










1274



  `a` int(11) NOT NULL DEFAULT '0',










1275



  `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,










1276



  PRIMARY KEY (`a`)










1277



) ENGINE=MyISAM DEFAULT CHARSET=latin1










1278



drop table t1;










1279



create table t1(a int, b timestamp default 20050102030405);










1280



alter table t1 add primary key(a);










1281



show create table t1;










1282



Table	Create Table










1283



t1	CREATE TABLE `t1` (










1284



  `a` int(11) NOT NULL DEFAULT '0',










1285



  `b` timestamp NOT NULL DEFAULT '2005-01-02 03:04:05',










1286



  PRIMARY KEY (`a`)










1287



) ENGINE=MyISAM DEFAULT CHARSET=latin1










1288



drop table t1;










1289



set @@sql_mode='traditional';










1290



create table t1(a bit(2));










1291



insert into t1 values(b'101');










1292



ERROR 22001: Data too long for column 'a' at row 1










1293



select * from t1;










1294



a










1295



drop table t1;










1296



set sql_mode='traditional';










1297



create table t1 (date date not null);










1298



create table t2 select date from t1;










1299



show create table t2;










1300



Table	Create Table










1301



t2	CREATE TABLE `t2` (










1302



  `date` date NOT NULL










1303



) ENGINE=MyISAM DEFAULT CHARSET=latin1










1304



drop table t2,t1;










1305



set @@sql_mode= @org_mode;










1306



set names utf8;










1307



create table t1 (i int)










1308



comment '123456789*123456789*123456789*123456789*123456789*123456789*';










1309



show create table t1;










1310



Table	Create Table










1311



t1	CREATE TABLE `t1` (










1312



  `i` int(11) DEFAULT NULL










1313



) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'










1314



drop table t1;










1315



set sql_mode= 'traditional';










1316



create table t1(col1 tinyint, col2 tinyint unsigned, 










1317



col3 smallint, col4 smallint unsigned,










1318



col5 mediumint, col6 mediumint unsigned,










1319



col7 int, col8 int unsigned,










1320



col9 bigint, col10 bigint unsigned);










1321



insert into t1(col1) values('-');










1322



ERROR HY000: Incorrect integer value: '-' for column 'col1' at row 1










1323



insert into t1(col2) values('+');










1324



ERROR HY000: Incorrect integer value: '+' for column 'col2' at row 1










1325



insert into t1(col3) values('-');










1326



ERROR HY000: Incorrect integer value: '-' for column 'col3' at row 1










1327



insert into t1(col4) values('+');










1328



ERROR HY000: Incorrect integer value: '+' for column 'col4' at row 1










1329



insert into t1(col5) values('-');










1330



ERROR HY000: Incorrect integer value: '-' for column 'col5' at row 1










1331



insert into t1(col6) values('+');










1332



ERROR HY000: Incorrect integer value: '+' for column 'col6' at row 1










1333



insert into t1(col7) values('-');










1334



ERROR HY000: Incorrect integer value: '-' for column 'col7' at row 1










1335



insert into t1(col8) values('+');










1336



ERROR HY000: Incorrect integer value: '+' for column 'col8' at row 1










1337



insert into t1(col9) values('-');










1338



ERROR HY000: Incorrect integer value: '-' for column 'col9' at row 1










1339



insert into t1(col10) values('+');










1340



ERROR HY000: Incorrect integer value: '+' for column 'col10' at row 1










1341



drop table t1;










1342



set sql_mode='traditional';










1343



create table t1(a year);










1344



insert into t1 values ('-');










1345



ERROR HY000: Incorrect integer value: '-' for column 'a' at row 1










1346



insert into t1 values ('+');










1347



ERROR HY000: Incorrect integer value: '+' for column 'a' at row 1










1348



insert into t1 values ('');










1349



ERROR HY000: Incorrect integer value: '' for column 'a' at row 1










1350



insert into t1 values ('2000a');










1351



ERROR 01000: Data truncated for column 'a' at row 1










1352



insert into t1 values ('2E3x');










1353



ERROR 01000: Data truncated for column 'a' at row 1










1354



drop table t1;










1355



set sql_mode='traditional';










1356



create table t1 (f1 set('a','a'));










1357



ERROR HY000: Column 'f1' has duplicated value 'a' in SET










1358



create table t1 (f1 enum('a','a'));










1359



ERROR HY000: Column 'f1' has duplicated value 'a' in ENUM










1360



set @@sql_mode='NO_ZERO_DATE';










1361



create table t1(a datetime not null);










1362



select count(*) from t1 where a is null;










1363



count(*)










1364



0










1365



drop table t1;










1366



End of 5.0 tests