~drizzle-trunk/drizzle/development

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

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

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

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

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

insert into t1 values (9223372036854775808);

insert into t1 values (0x7000000000000000);

select * from t1;

-9223372036854775808

8070450532247928832

select * from t1 where a=18446744073709551615;

delete from t1 where a=18446744073709551615;

select * from t1;

-9223372036854775808

8070450532247928832

drop table t1;

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

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

select * from t1;

a big

1 -1

1 -9223372036854775808

1 9223372036854775807

1 -9223372036854775808

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

alter table t1 modify big bigint not null;

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

insert into t1 (big) values (9223372036854775808);

select * from t1;

a big

1 -1

1 -9223372036854775808

1 9223372036854775807

1 -9223372036854775808

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

alter table t1 add key (big);

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

alter table t1 modify big bigint not null;

select * from t1;

a big

1 -1

1 -9223372036854775808

1 9223372036854775807

1 -9223372036854775808

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

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

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

-9223372036854775808 9223372036854775807 9223372036854775806

drop table t1;

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

insert into t1 values (null,1);

100

select * from t1;

101

id a

102

9999999999 1

103

select * from t1 limit 9999999999;

104

id a

105

9999999999 1

106

drop table t1;

107

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

108

insert into t1 values (10000000000000000000);

109

insert into t1 values (10000000000000000000.0);

110

insert into t1 values ('10000000000000000000');

111

select * from t1;

112

quantity

113

10000000000000000000

114

10000000000000000000

115

10000000000000000000

116

drop table t1;

117

SELECT '0x8000000000000001'+0;

118

'0x8000000000000001'+0

119

120

Warnings:

121

Warning 1292 Truncated incorrect DOUBLE value: '0x8000000000000001'

122

create table t1 (

123

value64 bigint not null,

124

value32 integer not null,

125

primary key(value64, value32)

126

);

127

create table t2 (

128

value64 bigint not null,

129

value32 integer not null,

130

primary key(value64, value32)

131

);

132

insert into t1 values(17156792991891826145, 1);

133

insert into t1 values( 9223372036854775807, 2);

134

insert into t2 values(17156792991891826145, 3);

135

insert into t2 values( 9223372036854775807, 4);

136

select * from t1;

137

value64 value32

138

-1289951081817725471 1

139

9223372036854775807 2

140

select * from t2;

141

value64 value32

142

-1289951081817725471 3

143

9223372036854775807 4

144

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

145

t2.value64=17156792991891826145;

146

value64 value32 value64 value32

147

-1289951081817725471 1 -1289951081817725471 3

148

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

149

t2.value64=t1.value64;

150

value64 value32 value64 value32

151

-1289951081817725471 1 -1289951081817725471 3

152

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

153

t2.value64=9223372036854775807;

154

value64 value32 value64 value32

155

9223372036854775807 2 9223372036854775807 4

156

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

157

t2.value64=t1.value64;

158

value64 value32 value64 value32

159

9223372036854775807 2 9223372036854775807 4

160

drop table t1, t2;

161

create table t1 (sint64 bigint not null);

162

insert into t1 values (-9223372036854775808);

163

select * from t1;

164

sint64

165

-9223372036854775808

166

drop table t1;

167

create table t1 select 1 as 'a';

168

show create table t1;

169

Table Create Table

170

t1 CREATE TABLE `t1` (

171

`a` int NOT NULL

172

) ENGINE=InnoDB

173

drop table t1;

174

create table t1 select 9223372036854775809 as 'a';

175

show create table t1;

176

Table Create Table

177

t1 CREATE TABLE `t1` (

178

`a` bigint NOT NULL

179

) ENGINE=InnoDB

180

select * from t1;

181

182

-9223372036854775807

183

drop table t1;

184

DROP DATABASE IF EXISTS `scott`;

185

Warnings:

186

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

187

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

188

insert into t1 values(

189

18446744073709551615,18446744073709551615,

190

18446744073709551615, 18446744073709551615

191

);

192

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

193

select * from t1;

194

a b c d

195

18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615

196

-1 -1 -1 -1

197

drop table t1;

198

create table t1 ( quantity decimal(2));

199

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

200

select * from t1;

201

quantity

202

203

-50

204

205

-1

206

drop table t1;

207

CREATE TABLE t1 (

208

`col1` INT NULL,

209

`col2` INT NULL,

210

`col3` INT NULL,

211

`col4` INT NULL,

212

`col5` INT NULL,

213

`col6` INT NULL,

214

`col7` INT NULL,

215

`col8` INT NULL,

216

`col9` INT NULL,

217

`col10` BIGINT NULL,

218

`col11` BIGINT NULL,

219

`col12` BIGINT NULL,

220

`col13` BIGINT NULL,

221

`col14` BIGINT NULL,

222

`col15` BIGINT NULL,

223

`col16` BIGINT NULL,

224

`col17` BIGINT NULL,

225

`col18` BIGINT NULL,

226

`col19` DECIMAL(19, 0) NULL,

227

`col20` DECIMAL(20, 0) NULL,

228

`col21` DECIMAL(21, 0) NULL,

229

`col22` DECIMAL(22, 0) NULL,

230

`col23` DECIMAL(23, 0) NULL,

231

`col24` DECIMAL(24, 0) NULL,

232

`col25` DECIMAL(25, 0) NULL,

233

`col26` DECIMAL(26, 0) NULL,

234

`col27` DECIMAL(27, 0) NULL,

235

`col28` DECIMAL(28, 0) NULL,

236

`col29` DECIMAL(29, 0) NULL,

237

`col30` DECIMAL(30, 0) NULL,

238

`col31` DECIMAL(31, 0) NULL,

239

`col32` DECIMAL(32, 0) NULL,

240

`col33` DECIMAL(33, 0) NULL,

241

`col34` DECIMAL(34, 0) NULL,

242

`col35` DECIMAL(35, 0) NULL,

243

`col36` DECIMAL(36, 0) NULL,

244

`col37` DECIMAL(37, 0) NULL,

245

`col38` DECIMAL(38, 0) NULL,

246

`fix1` DECIMAL(38, 1) NULL,

247

`fix2` DECIMAL(38, 2) NULL,

248

`fix3` DECIMAL(38, 3) NULL,

249

`fix4` DECIMAL(38, 4) NULL,

250

`fix5` DECIMAL(38, 5) NULL,

251

`fix6` DECIMAL(38, 6) NULL,

252

`fix7` DECIMAL(38, 7) NULL,

253

`fix8` DECIMAL(38, 8) NULL,

254

`fix9` DECIMAL(38, 9) NULL,

255

`fix10` DECIMAL(38, 10) NULL,

256

`fix11` DECIMAL(38, 11) NULL,

257

`fix12` DECIMAL(38, 12) NULL,

258

`fix13` DECIMAL(38, 13) NULL,

259

`fix14` DECIMAL(38, 14) NULL,

260

`fix15` DECIMAL(38, 15) NULL,

261

`fix16` DECIMAL(38, 16) NULL,

262

`fix17` DECIMAL(38, 17) NULL,

263

`fix18` DECIMAL(38, 18) NULL,

264

`fix19` DECIMAL(38, 19) NULL,

265

`fix20` DECIMAL(38, 20) NULL,

266

`fix21` DECIMAL(38, 21) NULL,

267

`fix22` DECIMAL(38, 22) NULL,

268

`fix23` DECIMAL(38, 23) NULL,

269

`fix24` DECIMAL(38, 24) NULL,

270

`fix25` DECIMAL(38, 25) NULL,

271

`fix26` DECIMAL(38, 26) NULL,

272

`fix27` DECIMAL(38, 27) NULL,

273

`fix28` DECIMAL(38, 28) NULL,

274

`fix29` DECIMAL(38, 29) NULL,

275

`fix30` DECIMAL(38, 30) NULL

276

);

277

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`)

278

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

279

9999999999, 99999999999, 999999999999, 9999999999999, 99999999999999,

280

999999999999999, 9999999999999999, 99999999999999999, 999999999999999999,

281

9999999999999999999, 99999999999999999999, 999999999999999999999,

282

9999999999999999999999, 99999999999999999999999, 999999999999999999999999,

283

9999999999999999999999999, 99999999999999999999999999,

284

999999999999999999999999999, 9999999999999999999999999999,

285

99999999999999999999999999999, 999999999999999999999999999999,

286

9999999999999999999999999999999, 99999999999999999999999999999999,

287

999999999999999999999999999999999, 9999999999999999999999999999999999,

288

99999999999999999999999999999999999, 999999999999999999999999999999999999,

289

9999999999999999999999999999999999999, 99999999999999999999999999999999999999,

290

9999999999999999999999999999999999999.9,

291

999999999999999999999999999999999999.99,

292

99999999999999999999999999999999999.999,

293

9999999999999999999999999999999999.9999,

294

999999999999999999999999999999999.99999,

295

99999999999999999999999999999999.999999,

296

9999999999999999999999999999999.9999999,

297

999999999999999999999999999999.99999999,

298

99999999999999999999999999999.999999999,

299

9999999999999999999999999999.9999999999,

300

999999999999999999999999999.99999999999,

301

99999999999999999999999999.999999999999,

302

9999999999999999999999999.9999999999999,

303

999999999999999999999999.99999999999999,

304

99999999999999999999999.999999999999999,

305

9999999999999999999999.9999999999999999,

306

999999999999999999999.99999999999999999,

307

99999999999999999999.999999999999999999,

308

9999999999999999999.9999999999999999999,

309

999999999999999999.99999999999999999999,

310

99999999999999999.999999999999999999999,

311

9999999999999999.9999999999999999999999,

312

999999999999999.99999999999999999999999,

313

99999999999999.999999999999999999999999,

314

9999999999999.9999999999999999999999999,

315

999999999999.99999999999999999999999999,

316

99999999999.999999999999999999999999999,

317

9999999999.9999999999999999999999999999,

318

999999999.99999999999999999999999999999,

319

99999999.999999999999999999999999999999);

320

SELECT * FROM t1;

321

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

322

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

323

DROP TABLE t1;

324

create table t1 (bigint_col bigint);

325

insert into t1 values (17666000000000000000);

326

select * from t1 where bigint_col=17666000000000000000;

327

bigint_col

328

-780744073709551616

329

select * from t1 where bigint_col='17666000000000000000';

330

bigint_col

331

drop table t1;

332

333

bug 19955 -- mod is signed with bigint

334

select 10000002383263201056 mod 50 as result;

335

result

336

337

create table t1 (c1 bigint);

338

insert into t1 values (10000002383263201056);

339

select c1 mod 50 as result from t1;

340

result

341

-10

342

drop table t1;

343

select -9223372036854775808;

344

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

345

def -9223372036854775808 5 20 20 N 32897 0 63

346

-9223372036854775808

347

-9223372036854775808

348

select -(9223372036854775808);

349

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

350

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

351

-(9223372036854775808)

352

-9223372036854775808

353

select -((9223372036854775808));

354

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

355

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

356

-((9223372036854775808))

357

-9223372036854775808

358

select -(-(9223372036854775808));

359

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

360

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

361

-(-(9223372036854775808))

362

9223372036854775808

363

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

364

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

365

9223372036854775808 -9223372036854775808 9223372036854775808

366

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

367

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

368

9223372036854775808 -9223372036854775808

369

create table t1 select -9223372036854775808 bi;

370

describe t1;

371

Field Type Null Key Default Extra

372

bi bigint NO NULL

373

drop table t1;

374

create table t1 select -9223372036854775809 bi;

375

describe t1;

376

Field Type Null Key Default Extra

377

bi decimal(19,0) NO NULL

378

drop table t1;

Older »