~drizzle-trunk/drizzle/development : revision 1

1

drop table if exists t1, t2;

2

select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296;

3

0 256 00000000000000065536 2147483647 -2147483648 2147483648 4294967296

4

0 256 65536 2147483647 -2147483648 2147483648 4294967296

5

select 9223372036854775807,-009223372036854775808;

6

9223372036854775807 -009223372036854775808

7

9223372036854775807 -9223372036854775808

8

select +9999999999999999999,-9999999999999999999;

9

9999999999999999999 -9999999999999999999

10

9999999999999999999 -9999999999999999999

11

select cast(9223372036854775808 as unsigned)+1;

12

cast(9223372036854775808 as unsigned)+1

13

9223372036854775809

14

select 9223372036854775808+1;

15

9223372036854775808+1

16

9223372036854775809

17

select -(0-3),round(-(0-3)), round(9999999999999999999);

18

-(0-3) round(-(0-3)) round(9999999999999999999)

19

3 3 9999999999999999999

20

select 1,11,101,1001,10001,100001,1000001,10000001,100000001,1000000001,10000000001,100000000001,1000000000001,10000000000001,100000000000001,1000000000000001,10000000000000001,100000000000000001,1000000000000000001,10000000000000000001;

21

1 11 101 1001 10001 100001 1000001 10000001 100000001 1000000001 10000000001 100000000001 1000000000001 10000000000001 100000000000001 1000000000000001 10000000000000001 100000000000000001 1000000000000000001 10000000000000000001

22

1 11 101 1001 10001 100001 1000001 10000001 100000001 1000000001 10000000001 100000000001 1000000000001 10000000000001 100000000000001 1000000000000001 10000000000000001 100000000000000001 1000000000000000001 10000000000000000001

23

select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001;

24

-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001

25

-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001

26

select conv(1,10,16),conv((1<<2)-1,10,16),conv((1<<10)-2,10,16),conv((1<<16)-3,10,16),conv((1<<25)-4,10,16),conv((1<<31)-5,10,16),conv((1<<36)-6,10,16),conv((1<<47)-7,10,16),conv((1<<48)-8,10,16),conv((1<<55)-9,10,16),conv((1<<56)-10,10,16),conv((1<<63)-11,10,16);

27

conv(1,10,16) conv((1<<2)-1,10,16) conv((1<<10)-2,10,16) conv((1<<16)-3,10,16) conv((1<<25)-4,10,16) conv((1<<31)-5,10,16) conv((1<<36)-6,10,16) conv((1<<47)-7,10,16) conv((1<<48)-8,10,16) conv((1<<55)-9,10,16) conv((1<<56)-10,10,16) conv((1<<63)-11,10,16)

28

1 3 3FE FFFD 1FFFFFC 7FFFFFFB FFFFFFFFA 7FFFFFFFFFF9 FFFFFFFFFFF8 7FFFFFFFFFFFF7 FFFFFFFFFFFFF6 7FFFFFFFFFFFFFF5

29

create table t1 (a bigint unsigned not null, primary key(a));

30

insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);

31

select * from t1;

32

a

33

18446744073709551612

34

18446744073709551613

35

18446744073709551614

36

18446744073709551615

37

select * from t1 where a=18446744073709551615;

38

a

39

18446744073709551615

40

delete from t1 where a=18446744073709551615;

41

select * from t1;

42

a

43

18446744073709551612

44

18446744073709551613

45

18446744073709551614

46

drop table t1;

47

create table t1 ( a int not null default 1, big bigint );

48

insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);

49

Warnings:

50

Warning 1264 Out of range value for column 'big' at row 4

51

select * from t1;

52

a big

53

1 -1

54

1 12345678901234567

55

1 9223372036854775807

56

1 9223372036854775807

57

select min(big),max(big),max(big)-1 from t1;

58

min(big) max(big) max(big)-1

59

-1 9223372036854775807 9223372036854775806

60

select min(big),max(big),max(big)-1 from t1 group by a;

61

min(big) max(big) max(big)-1

62

-1 9223372036854775807 9223372036854775806

63

alter table t1 modify big bigint unsigned not null;

64

Warnings:

65

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

66

select min(big),max(big),max(big)-1 from t1;

67

min(big) max(big) max(big)-1

68

0 9223372036854775807 9223372036854775806

69

select min(big),max(big),max(big)-1 from t1 group by a;

70

min(big) max(big) max(big)-1

71

0 9223372036854775807 9223372036854775806

72

insert into t1 (big) values (18446744073709551615);

73

select * from t1;

74

a big

75

1 0

76

1 12345678901234567

77

1 9223372036854775807

78

1 9223372036854775807

79

1 18446744073709551615

80

select min(big),max(big),max(big)-1 from t1;

81

min(big) max(big) max(big)-1

82

0 18446744073709551615 18446744073709551614

83

select min(big),max(big),max(big)-1 from t1 group by a;

84

min(big) max(big) max(big)-1

85

0 18446744073709551615 18446744073709551614

86

alter table t1 add key (big);

87

select min(big),max(big),max(big)-1 from t1;

88

min(big) max(big) max(big)-1

89

0 18446744073709551615 18446744073709551614

90

select min(big),max(big),max(big)-1 from t1 group by a;

91

min(big) max(big) max(big)-1

92

0 18446744073709551615 18446744073709551614

93

alter table t1 modify big bigint not null;

94

Warnings:

95

Warning 1264 Out of range value for column 'big' at row 5

96

select * from t1;

97

a big

98

1 0

99

1 12345678901234567

100

1 9223372036854775807

101

1 9223372036854775807

102

1 9223372036854775807

103

select min(big),max(big),max(big)-1 from t1;

104

min(big) max(big) max(big)-1

105

0 9223372036854775807 9223372036854775806

106

select min(big),max(big),max(big)-1 from t1 group by a;

107

min(big) max(big) max(big)-1

108

0 9223372036854775807 9223372036854775806

109

drop table t1;

110

create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;

111

insert into t1 values (null,1);

112

select * from t1;

113

id a

114

9999999999 1

115

select * from t1 limit 9999999999;

116

id a

117

9999999999 1

118

drop table t1;

119

CREATE TABLE t1 ( quantity decimal(60,0));

120

insert into t1 values (10000000000000000000);

121

insert into t1 values (10000000000000000000.0);

122

insert into t1 values ('10000000000000000000');

123

select * from t1;

124

quantity

125

10000000000000000000

126

10000000000000000000

127

10000000000000000000

128

drop table t1;

129

SELECT '0x8000000000000001'+0;

130

'0x8000000000000001'+0

131

0

132

Warnings:

133

Warning 1292 Truncated incorrect DOUBLE value: '0x8000000000000001'

134

create table t1 (

135

value64 bigint unsigned not null,

136

value32 integer not null,

137

primary key(value64, value32)

138

);

139

create table t2 (

140

value64 bigint unsigned not null,

141

value32 integer not null,

142

primary key(value64, value32)

143

);

144

insert into t1 values(17156792991891826145, 1);

145

insert into t1 values( 9223372036854775807, 2);

146

insert into t2 values(17156792991891826145, 3);

147

insert into t2 values( 9223372036854775807, 4);

148

select * from t1;

149

value64 value32

150

9223372036854775807 2

151

17156792991891826145 1

152

select * from t2;

153

value64 value32

154

9223372036854775807 4

155

17156792991891826145 3

156

select * from t1, t2 where t1.value64=17156792991891826145 and

157

t2.value64=17156792991891826145;

158

value64 value32 value64 value32

159

17156792991891826145 1 17156792991891826145 3

160

select * from t1, t2 where t1.value64=17156792991891826145 and

161

t2.value64=t1.value64;

162

value64 value32 value64 value32

163

17156792991891826145 1 17156792991891826145 3

164

select * from t1, t2 where t1.value64= 9223372036854775807 and

165

t2.value64=9223372036854775807;

166

value64 value32 value64 value32

167

9223372036854775807 2 9223372036854775807 4

168

select * from t1, t2 where t1.value64= 9223372036854775807 and

169

t2.value64=t1.value64;

170

value64 value32 value64 value32

171

9223372036854775807 2 9223372036854775807 4

172

drop table t1, t2;

173

create table t1 (sint64 bigint not null);

174

insert into t1 values (-9223372036854775808);

175

select * from t1;

176

sint64

177

-9223372036854775808

178

drop table t1;

179

create table t1 select 1 as 'a';

180

show create table t1;

181

Table Create Table

182

t1 CREATE TABLE `t1` (

183

`a` int(1) NOT NULL DEFAULT '0'

184

) ENGINE=MyISAM DEFAULT CHARSET=latin1

185

drop table t1;

186

create table t1 select 9223372036854775809 as 'a';

187

show create table t1;

188

Table Create Table

189

t1 CREATE TABLE `t1` (

190

`a` bigint(19) unsigned NOT NULL DEFAULT '0'

191

) ENGINE=MyISAM DEFAULT CHARSET=latin1

192

select * from t1;

193

a

194

9223372036854775809

195

drop table t1;

196

DROP DATABASE IF EXISTS `scott`;

197

Warnings:

198

Note 1008 Can't drop database 'scott'; database doesn't exist

199

create table t1 (a char(100), b varchar(100), c text, d blob);

200

insert into t1 values(

201

18446744073709551615,18446744073709551615,

202

18446744073709551615, 18446744073709551615

203

);

204

insert into t1 values (-1 | 0,-1 | 0,-1 | 0 ,-1 | 0);

205

select * from t1;

206

a b c d

207

18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615

208

18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615

209

drop table t1;

210

create table t1 ( quantity decimal(2) unsigned);

211

insert into t1 values (500), (-500), (~0), (-1);

212

Warnings:

213

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

214

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

215

Warning 1264 Out of range value for column 'quantity' at row 3

216

Warning 1264 Out of range value for column 'quantity' at row 4

217

select * from t1;

218

quantity

219

99

220

0

221

99

222

0

223

drop table t1;

224

CREATE TABLE t1 (

225

`col1` INT(1) NULL,

226

`col2` INT(2) NULL,

227

`col3` INT(3) NULL,

228

`col4` INT(4) NULL,

229

`col5` INT(5) NULL,

230

`col6` INT(6) NULL,

231

`col7` INT(7) NULL,

232

`col8` INT(8) NULL,

233

`col9` INT(9) NULL,

234

`col10` BIGINT(10) NULL,

235

`col11` BIGINT(11) NULL,

236

`col12` BIGINT(12) NULL,

237

`col13` BIGINT(13) NULL,

238

`col14` BIGINT(14) NULL,

239

`col15` BIGINT(15) NULL,

240

`col16` BIGINT(16) NULL,

241

`col17` BIGINT(17) NULL,

242

`col18` BIGINT(18) NULL,

243

`col19` DECIMAL(19, 0) NULL,

244

`col20` DECIMAL(20, 0) NULL,

245

`col21` DECIMAL(21, 0) NULL,

246

`col22` DECIMAL(22, 0) NULL,

247

`col23` DECIMAL(23, 0) NULL,

248

`col24` DECIMAL(24, 0) NULL,

249

`col25` DECIMAL(25, 0) NULL,

250

`col26` DECIMAL(26, 0) NULL,

251

`col27` DECIMAL(27, 0) NULL,

252

`col28` DECIMAL(28, 0) NULL,

253

`col29` DECIMAL(29, 0) NULL,

254

`col30` DECIMAL(30, 0) NULL,

255

`col31` DECIMAL(31, 0) NULL,

256

`col32` DECIMAL(32, 0) NULL,

257

`col33` DECIMAL(33, 0) NULL,

258

`col34` DECIMAL(34, 0) NULL,

259

`col35` DECIMAL(35, 0) NULL,

260

`col36` DECIMAL(36, 0) NULL,

261

`col37` DECIMAL(37, 0) NULL,

262

`col38` DECIMAL(38, 0) NULL,

263

`fix1` DECIMAL(38, 1) NULL,

264

`fix2` DECIMAL(38, 2) NULL,

265

`fix3` DECIMAL(38, 3) NULL,

266

`fix4` DECIMAL(38, 4) NULL,

267

`fix5` DECIMAL(38, 5) NULL,

268

`fix6` DECIMAL(38, 6) NULL,

269

`fix7` DECIMAL(38, 7) NULL,

270

`fix8` DECIMAL(38, 8) NULL,

271

`fix9` DECIMAL(38, 9) NULL,

272

`fix10` DECIMAL(38, 10) NULL,

273

`fix11` DECIMAL(38, 11) NULL,

274

`fix12` DECIMAL(38, 12) NULL,

275

`fix13` DECIMAL(38, 13) NULL,

276

`fix14` DECIMAL(38, 14) NULL,

277

`fix15` DECIMAL(38, 15) NULL,

278

`fix16` DECIMAL(38, 16) NULL,

279

`fix17` DECIMAL(38, 17) NULL,

280

`fix18` DECIMAL(38, 18) NULL,

281

`fix19` DECIMAL(38, 19) NULL,

282

`fix20` DECIMAL(38, 20) NULL,

283

`fix21` DECIMAL(38, 21) NULL,

284

`fix22` DECIMAL(38, 22) NULL,

285

`fix23` DECIMAL(38, 23) NULL,

286

`fix24` DECIMAL(38, 24) NULL,

287

`fix25` DECIMAL(38, 25) NULL,

288

`fix26` DECIMAL(38, 26) NULL,

289

`fix27` DECIMAL(38, 27) NULL,

290

`fix28` DECIMAL(38, 28) NULL,

291

`fix29` DECIMAL(38, 29) NULL,

292

`fix30` DECIMAL(38, 30) NULL

293

);

294

INSERT INTO t1(`col1`, `col2`, `col3`, `col4`, `col5`, `col6`, `col7`, `col8`, `col9`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`, `col31`, `col32`, `col33`, `col34`, `col35`, `col36`, `col37`, `col38`, `fix1`, `fix2`, `fix3`, `fix4`, `fix5`, `fix6`, `fix7`, `fix8`, `fix9`, `fix10`, `fix11`, `fix12`, `fix13`, `fix14`, `fix15`, `fix16`, `fix17`, `fix18`, `fix19`, `fix20`, `fix21`, `fix22`, `fix23`, `fix24`, `fix25`, `fix26`, `fix27`, `fix28`, `fix29`, `fix30`)

295

VALUES (9, 99, 999, 9999, 99999, 999999, 9999999, 99999999, 999999999,

296

9999999999, 99999999999, 999999999999, 9999999999999, 99999999999999,

297

999999999999999, 9999999999999999, 99999999999999999, 999999999999999999,

298

9999999999999999999, 99999999999999999999, 999999999999999999999,

299

9999999999999999999999, 99999999999999999999999, 999999999999999999999999,

300

9999999999999999999999999, 99999999999999999999999999,

301

999999999999999999999999999, 9999999999999999999999999999,

302

99999999999999999999999999999, 999999999999999999999999999999,

303

9999999999999999999999999999999, 99999999999999999999999999999999,

304

999999999999999999999999999999999, 9999999999999999999999999999999999,

305

99999999999999999999999999999999999, 999999999999999999999999999999999999,

306

9999999999999999999999999999999999999, 99999999999999999999999999999999999999,

307

9999999999999999999999999999999999999.9,

308

999999999999999999999999999999999999.99,

309

99999999999999999999999999999999999.999,

310

9999999999999999999999999999999999.9999,

311

999999999999999999999999999999999.99999,

312

99999999999999999999999999999999.999999,

313

9999999999999999999999999999999.9999999,

314

999999999999999999999999999999.99999999,

315

99999999999999999999999999999.999999999,

316

9999999999999999999999999999.9999999999,

317

999999999999999999999999999.99999999999,

318

99999999999999999999999999.999999999999,

319

9999999999999999999999999.9999999999999,

320

999999999999999999999999.99999999999999,

321

99999999999999999999999.999999999999999,

322

9999999999999999999999.9999999999999999,

323

999999999999999999999.99999999999999999,

324

99999999999999999999.999999999999999999,

325

9999999999999999999.9999999999999999999,

326

999999999999999999.99999999999999999999,

327

99999999999999999.999999999999999999999,

328

9999999999999999.9999999999999999999999,

329

999999999999999.99999999999999999999999,

330

99999999999999.999999999999999999999999,

331

9999999999999.9999999999999999999999999,

332

999999999999.99999999999999999999999999,

333

99999999999.999999999999999999999999999,

334

9999999999.9999999999999999999999999999,

335

999999999.99999999999999999999999999999,

336

99999999.999999999999999999999999999999);

337

SELECT * FROM t1;

338

col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23 col24 col25 col26 col27 col28 col29 col30 col31 col32 col33 col34 col35 col36 col37 col38 fix1 fix2 fix3 fix4 fix5 fix6 fix7 fix8 fix9 fix10 fix11 fix12 fix13 fix14 fix15 fix16 fix17 fix18 fix19 fix20 fix21 fix22 fix23 fix24 fix25 fix26 fix27 fix28 fix29 fix30

339

9 99 999 9999 99999 999999 9999999 99999999 999999999 9999999999 99999999999 999999999999 9999999999999 99999999999999 999999999999999 9999999999999999 99999999999999999 999999999999999999 9999999999999999999 99999999999999999999 999999999999999999999 9999999999999999999999 99999999999999999999999 999999999999999999999999 9999999999999999999999999 99999999999999999999999999 999999999999999999999999999 9999999999999999999999999999 99999999999999999999999999999 999999999999999999999999999999 9999999999999999999999999999999 99999999999999999999999999999999 999999999999999999999999999999999 9999999999999999999999999999999999 99999999999999999999999999999999999 999999999999999999999999999999999999 9999999999999999999999999999999999999 99999999999999999999999999999999999999 9999999999999999999999999999999999999.9 999999999999999999999999999999999999.99 99999999999999999999999999999999999.999 9999999999999999999999999999999999.9999 999999999999999999999999999999999.99999 99999999999999999999999999999999.999999 9999999999999999999999999999999.9999999 999999999999999999999999999999.99999999 99999999999999999999999999999.999999999 9999999999999999999999999999.9999999999 999999999999999999999999999.99999999999 99999999999999999999999999.999999999999 9999999999999999999999999.9999999999999 999999999999999999999999.99999999999999 99999999999999999999999.999999999999999 9999999999999999999999.9999999999999999 999999999999999999999.99999999999999999 99999999999999999999.999999999999999999 9999999999999999999.9999999999999999999 999999999999999999.99999999999999999999 99999999999999999.999999999999999999999 9999999999999999.9999999999999999999999 999999999999999.99999999999999999999999 99999999999999.999999999999999999999999 9999999999999.9999999999999999999999999 999999999999.99999999999999999999999999 99999999999.999999999999999999999999999 9999999999.9999999999999999999999999999 999999999.99999999999999999999999999999 99999999.999999999999999999999999999999

340

DROP TABLE t1;

341

create table t1 (bigint_col bigint unsigned);

342

insert into t1 values (17666000000000000000);

343

select * from t1 where bigint_col=17666000000000000000;

344

bigint_col

345

17666000000000000000

346

select * from t1 where bigint_col='17666000000000000000';

347

bigint_col

348

17666000000000000000

349

drop table t1;

350

351

bug 19955 -- mod is signed with bigint

352

select cast(10000002383263201056 as unsigned) mod 50 as result;

353

result

354

6

355

create table t1 (c1 bigint unsigned);

356

insert into t1 values (10000002383263201056);

357

select c1 mod 50 as result from t1;

358

result

359

6

360

drop table t1;

361

select cast(19999999999999999999 as signed);

362

cast(19999999999999999999 as signed)

363

9223372036854775807

364

Warnings:

365

Error 1292 Truncated incorrect DECIMAL value: ''

366

select cast(-19999999999999999999 as signed);

367

cast(-19999999999999999999 as signed)

368

-9223372036854775808

369

Warnings:

370

Error 1292 Truncated incorrect DECIMAL value: ''

371

select -9223372036854775808;

372

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

373

def -9223372036854775808 8 20 20 N 32897 0 63

374

-9223372036854775808

375

-9223372036854775808

376

select -(9223372036854775808);

377

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

378

def -(9223372036854775808) 8 20 20 N 32897 0 63

379

-(9223372036854775808)

380

-9223372036854775808

381

select -((9223372036854775808));

382

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

383

def -((9223372036854775808)) 8 20 20 N 32897 0 63

384

-((9223372036854775808))

385

-9223372036854775808

386

select -(-(9223372036854775808));

387

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

388

def -(-(9223372036854775808)) 246 21 19 N 129 0 63

389

-(-(9223372036854775808))

390

9223372036854775808

391

select --9223372036854775808, ---9223372036854775808, ----9223372036854775808;

392

--9223372036854775808 ---9223372036854775808 ----9223372036854775808

393

9223372036854775808 -9223372036854775808 9223372036854775808

394

select -(-9223372036854775808), -(-(-9223372036854775808));

395

-(-9223372036854775808) -(-(-9223372036854775808))

396

9223372036854775808 -9223372036854775808

397

create table t1 select -9223372036854775808 bi;

398

describe t1;

399

Field Type Null Key Default Extra

400

bi bigint(20) NO 0

401

drop table t1;

402

create table t1 select -9223372036854775809 bi;

403

describe t1;

404

Field Type Null Key Default Extra

405

bi decimal(19,0) NO 0

406

drop table t1;