~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

create table t1 (f float, f2 float(24), f3 float(6,2), d double, d2 float(53), d3 double(10,3), de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(7,6));

# We mask out Privileges column because it differs for embedded server

--replace_column 8 #

show full columns from t1;

drop table t1;

create table t1 (a decimal(7,3) not null, key (a));

insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");

select a from t1 order by a;

select min(a) from t1;

drop table t1;

--error 1425

create table t1 (a float(200,100), b double(200,100));

# float in a char(1) field

create table t1 (c20 char);

insert into t1 values (5000.0);

insert into t1 values (0.5e4);

drop table t1;

# Errors

--error 1063

create table t1 (f float(54)); # Should give an error

--disable_warnings

drop table if exists t1;

--enable_warnings

# Don't allow 'double unsigned' to be set to a negative value (Bug #7700)

create table t1 (d1 double, d2 double unsigned);

100

insert into t1 set d1 = -1.0;

101

update t1 set d2 = d1;

102

select * from t1;

103

drop table t1;

104

105

# Ensure that maximum values as the result of number of decimals

106

# being specified in table schema are enforced (Bug #7361)

107

create table t1 (f float(4,3));

108

insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");

109

select * from t1;

110

drop table if exists t1;

111

create table t1 (f double(4,3));

112

insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");

113

select * from t1;

114

drop table if exists t1;

115

116

# Check conversion of floats to character field (Bug #7774)

117

create table t1 (c char(20));

118

insert into t1 values (5e-28);

119

select * from t1;

120

drop table t1;

121

create table t1 (c char(6));

122

insert into t1 values (2e5),(2e6),(2e-4),(2e-5);

123

select * from t1;

124

drop table t1;

125

126

127

# Test of comparison of integer with float-in-range (Bug #7840)

128

# This is needed because some ODBC applications (like Foxpro) uses

129

# floats for everything.

130

131

132

CREATE TABLE t1 (

133

reckey int unsigned NOT NULL,

134

recdesc varchar(50) NOT NULL,

135

PRIMARY KEY (reckey)

136

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

137

138

INSERT INTO t1 VALUES (108, 'Has 108 as key');

139

INSERT INTO t1 VALUES (109, 'Has 109 as key');

140

select * from t1 where reckey=108;

141

select * from t1 where reckey=1.08E2;

142

select * from t1 where reckey=109;

143

select * from t1 where reckey=1.09E2;

144

drop table t1;

145

146

147

# Bug #13372 (decimal union)

148

149

create table t1 (d double(10,1));

150

create table t2 (d double(10,9));

151

insert into t1 values ("100000000.0");

152

insert into t2 values ("1.23456780");

153

create table t3 select * from t2 union select * from t1;

154

select * from t3;

155

show create table t3;

156

drop table t1, t2, t3;

157

158

159

160

# Bug #9855 (inconsistent column type for create select

161

162

create table t1 select 105213674794682365.00 + 0.0 x;

163

show warnings;

164

desc t1;

165

drop table t1;

166

167

create table t1 select 0.0 x;

168

desc t1;

169

create table t2 select 105213674794682365.00 y;

170

desc t2;

171

create table t3 select x+y a from t1,t2;

172

show warnings;

173

desc t3;

174

drop table t1,t2,t3;

175

176

177

# Bug #22129: A small double precision number becomes zero

178

179

# check if underflows are detected correctly

180

select 1e-308, 1.00000001e-300, 100000000e-300;

181

182

# check if overflows are detected correctly

183

select 10e307;

184

185

186

# Bug #19690: ORDER BY eliminates rows from the result

187

188

create table t1(a int, b double(8, 2));

189

insert into t1 values

190

(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75),

191

(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00),

192

(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93),

193

(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3, 0.16),

194

(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82);

195

explain select sum(b) s from t1 group by a;

196

select sum(b) s from t1 group by a;

197

select sum(b) s from t1 group by a having s <> 0;

198

select sum(b) s from t1 group by a having s <> 0 order by s;

199

select sum(b) s from t1 group by a having s <=> 0;

200

select sum(b) s from t1 group by a having s <=> 0 order by s;

201

alter table t1 add key (a, b);

202

explain select sum(b) s from t1 group by a;

203

select sum(b) s from t1 group by a;

204

select sum(b) s from t1 group by a having s <> 0;

205

select sum(b) s from t1 group by a having s <> 0 order by s;

206

select sum(b) s from t1 group by a having s <=> 0;

207

select sum(b) s from t1 group by a having s <=> 0 order by s;

208

drop table t1;

209

210

--echo End of 4.1 tests

211

212

213

# bug #12694 (float(m,d) specifications)

214

215

216

--error 1427

217

create table t1 (s1 float(0,2));

218

--error 1427

219

create table t1 (s1 float(1,2));

220

221

222

# MySQL Bugs: #11589: mysqltest --ps-protocol, strange output, float/double/real with zerofill

223

224

225

CREATE TABLE t1 (

226

f1 real zerofill,

227

f2 double zerofill,

228

f3 float zerofill);

229

INSERT INTO t1 VALUES ( 0.314152e+1, 0.314152e+1, 0.314152e+1);

230

231

let $my_stmt= select f1, f2, f3 FROM t1;

232

eval PREPARE stmt1 FROM '$my_stmt';

233

select f1, f2, f3 FROM t1;

234

eval $my_stmt;

235

EXECUTE stmt1;

236

237

DROP TABLE t1;

238

# Bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits"

239

240

241

create table t1 (f1 double(200, 0));

242

insert into t1 values (1e199), (-1e199);

243

insert into t1 values (1e200), (-1e200);

244

insert into t1 values (2e200), (-2e200);

245

select f1 + 0e0 from t1;

246

drop table t1;

247

248

create table t1 (f1 float(30, 0));

249

insert into t1 values (1e29), (-1e29);

250

insert into t1 values (1e30), (-1e30);

251

insert into t1 values (2e30), (-2e30);

252

select f1 + 0e0 from t1;

253

drop table t1;

254

255

256

# Bug #27483: Casting 'scientific notation type' to 'unsigned bigint' fails on

257

# windows.

258

259

260

create table t1(d double, u bigint unsigned);

261

262

insert into t1(d) values (9.22337203685479e18),

263

(1.84e19);

264

265

update t1 set u = d;

266

select u from t1;

267

268

drop table t1;

269

270

271

# Bug #21205: Different number of digits for float/doble/real in --ps-protocol

272

273

274

CREATE TABLE t1 (f1 DOUBLE);

275

INSERT INTO t1 VALUES(-1.79769313486231e+308);

276

SELECT f1 FROM t1;

277

DROP TABLE t1;

278

279

--echo End of 5.0 tests

Older »