WebSVN – wasCSharpSQLite – Blame – Rev 1

1

office

1

# 2010 September 25

2

#

3

# The author disclaims copyright to this source code. In place of

4

# a legal notice, here is a blessing:

5

#

6

# May you do good and not evil.

7

# May you find forgiveness for yourself and forgive others.

8

# May you share freely, never taking more than you give.

9

#

10

#***********************************************************************

11

#

12

# This file implements tests to verify that the "testable statements" in

13

# the lang_createtable.html document are correct.

14

#

15

16

set testdir [file dirname $argv0]

17

source $testdir/tester.tcl

18

19

set ::testprefix e_createtable

20

21

# Test organization:

22

#

23

# e_createtable-0.*: Test that the syntax diagrams are correct.

24

#

25

# e_createtable-1.*: Test statements related to table and database names,

26

# the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.

27

#

28

# e_createtable-2.*: Test "CREATE TABLE AS" statements.

29

#

30

31

proc do_createtable_tests {nm args} {

32

uplevel do_select_tests [list e_createtable-$nm] $args

}

#-------------------------------------------------------------------------

37

# This command returns a serialized tcl array mapping from the name of

38

# each attached database to a list of tables in that database. For example,

39

# if the database schema is created with:

40

#

41

# CREATE TABLE t1(x);

42

# CREATE TEMP TABLE t2(x);

43

# CREATE TEMP TABLE t3(x);

44

#

45

# Then this command returns "main t1 temp {t2 t3}".

46

#

47

proc table_list {} {

48

set res [list]

49

db eval { pragma database_list } a {

50

set dbname $a(name)

51

set master $a(name).sqlite_master

52

if {$dbname == "temp"} { set master sqlite_temp_master }

53

lappend res $dbname [

54

db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"

]

}

set res

}

# EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name

62

#

63

do_createtable_tests 0.1.1 -repair {

64

drop_all_tables

65

} {

66

1 "CREATE TABLE t1(c1 one)" {}

67

2 "CREATE TABLE t1(c1 one two)" {}

68

3 "CREATE TABLE t1(c1 one two three)" {}

69

4 "CREATE TABLE t1(c1 one two three four)" {}

70

5 "CREATE TABLE t1(c1 one two three four(14))" {}

71

6 "CREATE TABLE t1(c1 one two three four(14, 22))" {}

72

7 "CREATE TABLE t1(c1 var(+14, -22.3))" {}

73

8 "CREATE TABLE t1(c1 var(1.0e10))" {}

74

}

75

do_createtable_tests 0.1.2 -error {

76

near "%s": syntax error

77

} {

78

1 "CREATE TABLE t1(c1 one(number))" {number}

}

# EVIDENCE-OF: R-18762-12428 -- syntax diagram column-constraint

83

#

84

# Note: Not shown in the syntax diagram is the "NULL" constraint. This

85

# is the opposite of "NOT NULL" - it implies that the column may

86

# take a NULL value. This is the default anyway, so this type of

87

# constraint is rarely used.

88

#

89

do_createtable_tests 0.2.1 -repair {

90

drop_all_tables

91

execsql { CREATE TABLE t2(x PRIMARY KEY) }

92

} {

93

1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {}

94

1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {}

95

1.3 "CREATE TABLE t1(c1 text PRIMARY KEY DESC)" {}

96

1.4 "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)" {}

97

98

2.1 "CREATE TABLE t1(c1 text NOT NULL)" {}

99

2.2 "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)" {}

100

2.3 "CREATE TABLE t1(c1 text NULL)" {}

101

2.4 "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)" {}

102

103

3.1 "CREATE TABLE t1(c1 text UNIQUE)" {}

104

3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {}

105

106

4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {}

107

4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {}

108

109

5.1 "CREATE TABLE t1(c1 text DEFAULT 1)" {}

110

5.2 "CREATE TABLE t1(c1 text DEFAULT -1)" {}

111

5.3 "CREATE TABLE t1(c1 text DEFAULT +1)" {}

112

5.4 "CREATE TABLE t1(c1 text DEFAULT -45.8e22)" {}

113

5.5 "CREATE TABLE t1(c1 text DEFAULT (1+1))" {}

114

5.6 "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))" {}

115

116

6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {}

117

6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {}

118

119

7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {}

120

7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {}

8.1 {

CREATE TABLE t1(c1

PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1

);

} {}

8.2 {

CREATE TABLE t1(c1

REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY

);

} {}

}

# EVIDENCE-OF: R-17905-31923 -- syntax diagram table-constraint

135

#

136

do_createtable_tests 0.3.1 -repair {

137

drop_all_tables

138

execsql { CREATE TABLE t2(x PRIMARY KEY) }

139

} {

140

1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {}

141

1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {}

142

1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {}

143

144

2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {}

145

2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {}

146

2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {}

147

148

3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {}

149

150

4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {}

151

}

152

153

# EVIDENCE-OF: R-18765-31171 -- syntax diagram column-def

154

#

155

do_createtable_tests 0.4.1 -repair {

156

drop_all_tables

157

} {

158

1 {CREATE TABLE t1(

159

col1,

160

col2 TEXT,

161

col3 INTEGER UNIQUE,

162

col4 VARCHAR(10, 10) PRIMARY KEY,

163

"name with spaces" REFERENCES t1

);

} {}

}

# EVIDENCE-OF: R-59573-11075 -- syntax diagram create-table-stmt

169

#

170

do_createtable_tests 0.5.1 -repair {

171

drop_all_tables

172

execsql { CREATE TABLE t2(a, b, c) }

173

} {

174

1 "CREATE TABLE t1(a, b, c)" {}

175

2 "CREATE TEMP TABLE t1(a, b, c)" {}

176

3 "CREATE TEMPORARY TABLE t1(a, b, c)" {}

177

4 "CREATE TABLE IF NOT EXISTS t1(a, b, c)" {}

178

5 "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)" {}

179

6 "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)" {}

180

181

7 "CREATE TABLE main.t1(a, b, c)" {}

182

8 "CREATE TEMP TABLE temp.t1(a, b, c)" {}

183

9 "CREATE TEMPORARY TABLE temp.t1(a, b, c)" {}

184

10 "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)" {}

185

11 "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)" {}

186

12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {}

187

188

13 "CREATE TABLE t1 AS SELECT * FROM t2" {}

189

14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {}

190

15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {}

191

}

192

193

# EVIDENCE-OF: R-32138-02228 -- syntax diagram foreign-key-clause

194

#

195

# 1: Explicit parent-key columns.

196

# 2: Implicit child-key columns.

#

# 1: MATCH FULL

# 2: MATCH PARTIAL

# 3: MATCH SIMPLE

# 4: MATCH STICK

# 5:

#

# 1: ON DELETE SET NULL

205

# 2: ON DELETE SET DEFAULT

206

# 3: ON DELETE CASCADE

207

# 4: ON DELETE RESTRICT

208

# 5: ON DELETE NO ACTION

209

# 6:

210

#

211

# 1: ON UPDATE SET NULL

212

# 2: ON UPDATE SET DEFAULT

213

# 3: ON UPDATE CASCADE

214

# 4: ON UPDATE RESTRICT

215

# 5: ON UPDATE NO ACTION

216

# 6:

217

#

218

# 1: NOT DEFERRABLE INITIALLY DEFERRED

219

# 2: NOT DEFERRABLE INITIALLY IMMEDIATE

220

# 3: NOT DEFERRABLE

221

# 4: DEFERRABLE INITIALLY DEFERRED

222

# 5: DEFERRABLE INITIALLY IMMEDIATE

# 6: DEFERRABLE

# 7:

#

do_createtable_tests 0.6.1 -repair {

227

drop_all_tables

228

execsql { CREATE TABLE t2(x PRIMARY KEY, y) }

229

execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }

230

} {

231

11146 { CREATE TABLE t1(a

232

REFERENCES t2(x) MATCH FULL

233

ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE

234

)} {}

235

11412 { CREATE TABLE t1(a

236

REFERENCES t2(x)

237

ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL

238

NOT DEFERRABLE INITIALLY IMMEDIATE

239

)} {}

240

12135 { CREATE TABLE t1(a

241

REFERENCES t2(x) MATCH PARTIAL

242

ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE

243

)} {}

244

12427 { CREATE TABLE t1(a

245

REFERENCES t2(x) MATCH PARTIAL

246

ON DELETE RESTRICT ON UPDATE SET DEFAULT

247

)} {}

248

12446 { CREATE TABLE t1(a

249

REFERENCES t2(x) MATCH PARTIAL

250

ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE

251

)} {}

252

12522 { CREATE TABLE t1(a

253

REFERENCES t2(x) MATCH PARTIAL

254

ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE

255

)} {}

256

13133 { CREATE TABLE t1(a

257

REFERENCES t2(x) MATCH SIMPLE

258

ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE

259

)} {}

260

13216 { CREATE TABLE t1(a

261

REFERENCES t2(x) MATCH SIMPLE

262

ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE

263

)} {}

264

13263 { CREATE TABLE t1(a

265

REFERENCES t2(x) MATCH SIMPLE

266

ON DELETE SET DEFAULT NOT DEFERRABLE

267

)} {}

268

13421 { CREATE TABLE t1(a

269

REFERENCES t2(x) MATCH SIMPLE

270

ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED

271

)} {}

272

13432 { CREATE TABLE t1(a

273

REFERENCES t2(x) MATCH SIMPLE

274

ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE

275

)} {}

276

13523 { CREATE TABLE t1(a

277

REFERENCES t2(x) MATCH SIMPLE

278

ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE

279

)} {}

280

14336 { CREATE TABLE t1(a

281

REFERENCES t2(x) MATCH STICK

282

ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE

283

)} {}

284

14611 { CREATE TABLE t1(a

285

REFERENCES t2(x) MATCH STICK

286

ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED

287

)} {}

288

15155 { CREATE TABLE t1(a

289

REFERENCES t2(x)

290

ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE

291

)} {}

292

15453 { CREATE TABLE t1(a

293

REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE

294

)} {}

295

15661 { CREATE TABLE t1(a

296

REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED

297

)} {}

298

21115 { CREATE TABLE t1(a

299

REFERENCES t2 MATCH FULL

300

ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE

301

)} {}

302

21123 { CREATE TABLE t1(a

303

REFERENCES t2 MATCH FULL

304

ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE

305

)} {}

306

21217 { CREATE TABLE t1(a

307

REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL

308

)} {}

309

21362 { CREATE TABLE t1(a

310

REFERENCES t2 MATCH FULL

311

ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE

312

)} {}

313

22143 { CREATE TABLE t1(a

314

REFERENCES t2 MATCH PARTIAL

315

ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE

316

)} {}

317

22156 { CREATE TABLE t1(a

318

REFERENCES t2 MATCH PARTIAL

319

ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE

320

)} {}

321

22327 { CREATE TABLE t1(a

322

REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT

323

)} {}

324

22663 { CREATE TABLE t1(a

325

REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE

326

)} {}

327

23236 { CREATE TABLE t1(a

328

REFERENCES t2 MATCH SIMPLE

329

ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE

330

)} {}

331

24155 { CREATE TABLE t1(a

332

REFERENCES t2 MATCH STICK

333

ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE

334

)} {}

335

24522 { CREATE TABLE t1(a

336

REFERENCES t2 MATCH STICK

337

ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE

338

)} {}

339

24625 { CREATE TABLE t1(a

340

REFERENCES t2 MATCH STICK

341

ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE

342

)} {}

343

25454 { CREATE TABLE t1(a

344

REFERENCES t2

345

ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED

)} {}

}

#-------------------------------------------------------------------------

350

# Test cases e_createtable-1.* - test statements related to table and

351

# database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS

# clause.

#

drop_all_tables

forcedelete test.db2 test.db3

356

357

do_execsql_test e_createtable-1.0 {

358

ATTACH 'test.db2' AS auxa;

359

ATTACH 'test.db3' AS auxb;

360

} {}

361

362

# EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are

363

# reserved for internal use. It is an error to attempt to create a table

364

# with a name that starts with "sqlite_".

365

#

366

do_createtable_tests 1.1.1 -error {

367

object name reserved for internal use: %s

368

} {

369

1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc

370

2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld

371

3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__

372

4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_

373

5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL

374

}

375

do_createtable_tests 1.1.2 {

376

1 "CREATE TABLE sqlit_abc(a, b, c)" {}

377

2 "CREATE TABLE temp.sqlitehelloworld(x)" {}

378

3 {CREATE TABLE auxa."sqlite"(x, y)} {}

379

4 {CREATE TABLE auxb."sqlite-"(z)} {}

380

5 {CREATE TABLE "SQLITE-TBL"(z)} {}

}

# EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it

385

# must be either "main", "temp", or the name of an attached database.

386

#

387

# EVIDENCE-OF: R-39822-07822 In this case the new table is created in

388

# the named database.

389

#

390

# Test cases 1.2.* test the first of the two requirements above. The

391

# second is verified by cases 1.3.*.

392

#

393

do_createtable_tests 1.2.1 -error {

394

unknown database %s

395

} {

396

1 "CREATE TABLE george.t1(a, b)" george

397

2 "CREATE TABLE _.t1(a, b)" _

398

}

399

do_createtable_tests 1.2.2 {

400

1 "CREATE TABLE main.abc(a, b, c)" {}

401

2 "CREATE TABLE temp.helloworld(x)" {}

402

3 {CREATE TABLE auxa."t 1"(x, y)} {}

403

4 {CREATE TABLE auxb.xyz(z)} {}

404

}

405

drop_all_tables

406

do_createtable_tests 1.3 -tclquery {

407

unset -nocomplain X

408

array set X [table_list]

409

list $X(main) $X(temp) $X(auxa) $X(auxb)

410

} {

411

1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}}

412

2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}}

413

3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}}

414

4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl}

415

5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}}

416

6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}}

417

}

418

419

# EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs

420

# between the "CREATE" and "TABLE" then the new table is created in the

# temp database.

#

drop_all_tables

do_createtable_tests 1.4 -tclquery {

425

unset -nocomplain X

426

array set X [table_list]

427

list $X(main) $X(temp) $X(auxa) $X(auxb)

428

} {

429

1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}}

430

2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}

431

}

432

433

# EVIDENCE-OF: R-49439-47561 It is an error to specify both a

434

# <database-name> and the TEMP or TEMPORARY keyword, unless the

435

# <database-name> is "temp".

436

#

437

drop_all_tables

438

do_createtable_tests 1.5.1 -error {

439

temporary table name must be unqualified

440

} {

441

1 "CREATE TEMP TABLE main.t1(a, b)" {}

442

2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {}

443

3 "CREATE TEMP TABLE auxb.t3(a, b)" {}

444

4 "CREATE TEMPORARY TABLE main.xxx(x)" {}

445

}

446

drop_all_tables

447

do_createtable_tests 1.5.2 -tclquery {

448

unset -nocomplain X

449

array set X [table_list]

450

list $X(main) $X(temp) $X(auxa) $X(auxb)

451

} {

452

1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}}

453

2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}}

454

3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}}

455

4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}}

456

}

457

458

# EVIDENCE-OF: R-00917-09393 If no database name is specified and the

459

# TEMP keyword is not present then the table is created in the main

# database.

#

drop_all_tables

do_createtable_tests 1.6 -tclquery {

464

unset -nocomplain X

465

array set X [table_list]

466

list $X(main) $X(temp) $X(auxa) $X(auxb)

467

} {

468

1 "CREATE TABLE t1(a, b)" {t1 {} {} {}}

469

2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}}

470

3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}}

471

4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}}

}

drop_all_tables

do_execsql_test e_createtable-1.7.0 {

476

CREATE TABLE t1(x, y);

477

CREATE INDEX i1 ON t1(x);

478

CREATE VIEW v1 AS SELECT * FROM t1;

479

480

CREATE TABLE auxa.tbl1(x, y);

481

CREATE INDEX auxa.idx1 ON tbl1(x);

482

CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;

483

} {}

484

485

# EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create

486

# a new table in a database that already contains a table, index or view

487

# of the same name.

488

#

489

# Test cases 1.7.1.* verify that creating a table in a database with a

490

# table/index/view of the same name does fail. 1.7.2.* tests that creating

491

# a table with the same name as a table/index/view in a different database

492

# is Ok.

493

#

494

do_createtable_tests 1.7.1 -error { %s } {

495

1 "CREATE TABLE t1(a, b)" {{table t1 already exists}}

496

2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}}

497

3 "CREATE TABLE v1(a, b)" {{table v1 already exists}}

498

4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}}

499

5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}}

500

6 "CREATE TABLE auxa.view1(a, b)" {{table view1 already exists}}

501

}

502

do_createtable_tests 1.7.2 {

503

1 "CREATE TABLE auxa.t1(a, b)" {}

504

2 "CREATE TABLE auxa.i1(a, b)" {}

505

3 "CREATE TABLE auxa.v1(a, b)" {}

506

4 "CREATE TABLE tbl1(a, b)" {}

507

5 "CREATE TABLE idx1(a, b)" {}

508

6 "CREATE TABLE view1(a, b)" {}

509

}

510

511

# EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is

512

# specified as part of the CREATE TABLE statement and a table or view of

513

# the same name already exists, the CREATE TABLE command simply has no

514

# effect (and no error message is returned).

515

#

516

drop_all_tables

517

do_execsql_test e_createtable-1.8.0 {

518

CREATE TABLE t1(x, y);

519

CREATE INDEX i1 ON t1(x);

520

CREATE VIEW v1 AS SELECT * FROM t1;

521

CREATE TABLE auxa.tbl1(x, y);

522

CREATE INDEX auxa.idx1 ON tbl1(x);

523

CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;

524

} {}

525

do_createtable_tests 1.8 {

526

1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {}

527

2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {}

528

3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {}

529

4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {}

530

}

531

532

# EVIDENCE-OF: R-16465-40078 An error is still returned if the table

533

# cannot be created because of an existing index, even if the "IF NOT

534

# EXISTS" clause is specified.

535

#

536

do_createtable_tests 1.9 -error { %s } {

537

1 "CREATE TABLE IF NOT EXISTS i1(a, b)"

538

{{there is already an index named i1}}

539

2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)"

540

{{there is already an index named idx1}}

541

}

542

543

# EVIDENCE-OF: R-05513-33819 It is not an error to create a table that

544

# has the same name as an existing trigger.

545

#

546

drop_all_tables

547

do_execsql_test e_createtable-1.10.0 {

548

CREATE TABLE t1(x, y);

549

CREATE TABLE auxb.t2(x, y);

550

551

CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN

552

SELECT 1;

553

END;

554

CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN

SELECT 1;

END;

} {}

do_createtable_tests 1.10 {

559

1 "CREATE TABLE tr1(a, b)" {}

560

2 "CREATE TABLE tr2(a, b)" {}

561

3 "CREATE TABLE auxb.tr1(a, b)" {}

562

4 "CREATE TABLE auxb.tr2(a, b)" {}

563

}

564

565

# EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE

# statement.

#

drop_all_tables

do_execsql_test e_createtable-1.11.0 {

570

CREATE TABLE t1(a, b);

571

CREATE TABLE t2(a, b);

572

CREATE TABLE auxa.t3(a, b);

573

CREATE TABLE auxa.t4(a, b);

574

} {}

575

576

do_execsql_test e_createtable-1.11.1.1 {

SELECT * FROM t1;

SELECT * FROM t2;

SELECT * FROM t3;

SELECT * FROM t4;

} {}

do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {}

583

do_catchsql_test e_createtable-1.11.1.3 {

584

SELECT * FROM t1

585

} {1 {no such table: t1}}

586

do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {}

587

do_catchsql_test e_createtable-1.11.1.5 {

588

SELECT * FROM t3

589

} {1 {no such table: t3}}

590

591

do_execsql_test e_createtable-1.11.2.1 {

592

SELECT name FROM sqlite_master;

593

SELECT name FROM auxa.sqlite_master;

594

} {t2 t4}

595

do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {}

596

do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {}

597

do_execsql_test e_createtable-1.11.2.4 {

598

SELECT name FROM sqlite_master;

599

SELECT name FROM auxa.sqlite_master;

600

} {}

601

602

#-------------------------------------------------------------------------

603

# Test cases e_createtable-2.* - test statements related to the CREATE

604

# TABLE AS ... SELECT statement.

605

#

606

607

# Three Tcl commands:

608

#

609

# select_column_names SQL

610

# The argument must be a SELECT statement. Return a list of the names

611

# of the columns of the result-set that would be returned by executing

612

# the SELECT.

613

#

614

# table_column_names TBL

615

# The argument must be a table name. Return a list of column names, from

616

# left to right, for the table.

617

#

618

# table_column_decltypes TBL

619

# The argument must be a table name. Return a list of column declared

620

# types, from left to right, for the table.

621

#

622

proc sci {select cmd} {

623

set res [list]

624

set STMT [sqlite3_prepare_v2 db $select -1 dummy]

625

for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {

626

lappend res [$cmd $STMT $i]

627

}

628

sqlite3_finalize $STMT

629

set res

630

}

631

proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }

632

proc select_column_names {sql} { sci $sql sqlite3_column_name }

633

proc table_column_names {tbl} { tci $tbl sqlite3_column_name }

634

proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }

635

636

# Create a database schema. This schema is used by tests 2.1.* through 2.3.*.

637

#

638

drop_all_tables

639

do_execsql_test e_createtable-2.0 {

640

CREATE TABLE t1(a, b, c);

641

CREATE TABLE t2(d, e, f);

642

CREATE TABLE t3(g BIGINT, h VARCHAR(10));

643

CREATE TABLE t4(i BLOB, j ANYOLDATA);

644

CREATE TABLE t5(k FLOAT, l INTEGER);

645

CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));

646

CREATE TABLE t7(x INTEGER PRIMARY KEY);

647

CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');

648

CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);

649

} {}

650

651

# EVIDENCE-OF: R-64828-59568 The table has the same number of columns as

652

# the rows returned by the SELECT statement. The name of each column is

653

# the same as the name of the corresponding column in the result set of

654

# the SELECT statement.

655

#

656

do_createtable_tests 2.1 -tclquery {

657

table_column_names x1

658

} -repair {

659

catchsql { DROP TABLE x1 }

660

} {

661

1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c}

662

2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a}

663

3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f}

664

4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)}

665

5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}

666

}

667

668

# EVIDENCE-OF: R-37111-22855 The declared type of each column is

669

# determined by the expression affinity of the corresponding expression

670

# in the result set of the SELECT statement, as follows: Expression

671

# Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"

672

# REAL "REAL" NONE "" (empty string)

673

#

674

do_createtable_tests 2.2 -tclquery {

675

table_column_decltypes x1

676

} -repair {

677

catchsql { DROP TABLE x1 }

678

} {

679

1 "CREATE TABLE x1 AS SELECT a FROM t1" {""}

680

2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT}

681

3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM}

682

4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT}

683

}

684

685

# EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has

686

# no PRIMARY KEY and no constraints of any kind. The default value of

687

# each column is NULL. The default collation sequence for each column of

688

# the new table is BINARY.

689

#

690

# The following tests create tables based on SELECT statements that read

691

# from tables that have primary keys, constraints and explicit default

692

# collation sequences. None of this is transfered to the definition of

693

# the new table as stored in the sqlite_master table.

694

#

695

# Tests 2.3.2.* show that the default value of each column is NULL.

696

#

697

do_createtable_tests 2.3.1 -query {

698

SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1

699

} {

700

1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}

701

2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}

702

3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}

703

4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}

704

}

705

do_execsql_test e_createtable-2.3.2.1 {

706

INSERT INTO x1 DEFAULT VALUES;

707

INSERT INTO x2 DEFAULT VALUES;

708

INSERT INTO x3 DEFAULT VALUES;

709

INSERT INTO x4 DEFAULT VALUES;

710

} {}

711

db nullvalue null

712

do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}

713

do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}

714

do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}

715

do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}

db nullvalue {}

drop_all_tables

do_execsql_test e_createtable-2.4.0 {

720

CREATE TABLE t1(x, y);

721

INSERT INTO t1 VALUES('i', 'one');

722

INSERT INTO t1 VALUES('ii', 'two');

723

INSERT INTO t1 VALUES('iii', 'three');

724

} {}

725

726

# EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are

727

# initially populated with the rows of data returned by the SELECT

728

# statement.

729

#

730

# EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending

731

# rowid values, starting with 1, in the order that they are returned by

732

# the SELECT statement.

733

#

734

# Each test case below is specified as the name of a table to create

735

# using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in

736

# creating it. The table is created.

737

#

738

# Test cases 2.4.*.1 check that after it has been created, the data in the

739

# table is the same as the data returned by the SELECT statement executed as

740

# a standalone command, verifying the first testable statement above.

741

#

742

# Test cases 2.4.*.2 check that the rowids were allocated contiguously

743

# as required by the second testable statement above. That the rowids

744

# from the contiguous block were allocated to rows in the order rows are

745

# returned by the SELECT statement is verified by 2.4.*.1.

746

#

747

# EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement

748

# creates and populates a database table based on the results of a

749

# SELECT statement.

750

#

751

# The above is also considered to be tested by the following. It is

752

# clear that tables are being created and populated by the command in

753

# question.

754

#

755

foreach {tn tbl select} {

756

1 x1 "SELECT * FROM t1"

757

2 x2 "SELECT * FROM t1 ORDER BY x DESC"

758

3 x3 "SELECT * FROM t1 ORDER BY x ASC"

759

} {

760

# Create the table using a "CREATE TABLE ... AS SELECT ..." command.

761

execsql [subst {CREATE TABLE $tbl AS $select}]

762

763

# Check that the rows inserted into the table, sorted in ascending rowid

764

# order, match those returned by executing the SELECT statement as a

765

# standalone command.

766

do_execsql_test e_createtable-2.4.$tn.1 [subst {

767

SELECT * FROM $tbl ORDER BY rowid;

768

}] [execsql $select]

769

770

# Check that the rowids in the new table are a contiguous block starting

771

# with rowid 1. Note that this will fail if SELECT statement $select

772

# returns 0 rows (as max(rowid) will be NULL).

773

do_execsql_test e_createtable-2.4.$tn.2 [subst {

774

SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl

}] {1 1}

}

#--------------------------------------------------------------------------

779

# Test cases for column defintions in CREATE TABLE statements that do not

780

# use a SELECT statement. Not including data constraints. In other words,

781

# tests for the specification of:

782

#

783

# * declared types,

784

# * default values, and

785

# * default collation sequences.

786

#

787

788

# EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not

789

# restrict the type of data that may be inserted into a column based on

790

# the columns declared type.

791

#

792

# Test this by creating a few tables with varied declared types, then

793

# inserting various different types of values into them.

794

#

795

drop_all_tables

796

do_execsql_test e_createtable-3.1.0 {

797

CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);

798

CREATE TABLE t2(a DATETIME, b STRING, c REAL);

799

CREATE TABLE t3(o, t);

800

} {}

801

802

# value type -> declared column type

803

# ----------------------------------

804

# integer -> VARCHAR(10)

805

# string -> INTEGER

806

# blob -> DOUBLE

807

#

808

do_execsql_test e_createtable-3.1.1 {

809

INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');

810

SELECT * FROM t1;

811

} {14 {quite a lengthy string} UVU}

812

813

# string -> DATETIME

814

# integer -> STRING

815

# time -> REAL

816

#

817

do_execsql_test e_createtable-3.1.2 {

818

INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');

819

SELECT * FROM t2;

820

} {{not a datetime} 13 12:41:59}

821

822

# EVIDENCE-OF: R-10565-09557 The declared type of a column is used to

823

# determine the affinity of the column only.

824

#

825

# Affinities are tested in more detail elsewhere (see document

826

# datatype3.html). Here, just test that affinity transformations

827

# consistent with the expected affinity of each column (based on

828

# the declared type) appear to take place.

829

#

830

# Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL

831

# Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL

832

# Affinities of t3 (test cases 3.2.3.*): NONE, NONE

833

#

834

do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}

835

836

do_createtable_tests 3.2.1 -query {

837

SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;

838

} {

839

1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0}

840

2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0}

841

}

842

do_createtable_tests 3.2.2 -query {

843

SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;

844

} {

845

1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0}

846

2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0}

847

}

848

do_createtable_tests 3.2.3 -query {

849

SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;

850

} {

851

1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'}

852

2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0}

853

}

854

855

# EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause

856

# attached to a column definition, then the default value of the column

857

# is NULL.

858

#

859

# None of the columns in table t1 have an explicit DEFAULT clause.

860

# So testing that the default value of all columns in table t1 is

861

# NULL serves to verify the above.

862

#

863

do_createtable_tests 3.2.3 -query {

864

SELECT quote(x), quote(y), quote(z) FROM t1

865

} -repair {

866

execsql { DELETE FROM t1 }

867

} {

868

1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL}

869

2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'}

870

3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL}

871

}

872

873

# EVIDENCE-OF: R-62940-43005 An explicit DEFAULT clause may specify that

874

# the default value is NULL, a string constant, a blob constant, a

875

# signed-number, or any constant expression enclosed in parentheses. An

876

# explicit default value may also be one of the special case-independent

877

# keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.

878

#

879

do_execsql_test e_createtable-3.3.1 {

880

CREATE TABLE t4(

881

a DEFAULT NULL,

882

b DEFAULT 'string constant',

883

c DEFAULT X'424C4F42',

d DEFAULT 1,

e DEFAULT -1,

f DEFAULT 3.14,

g DEFAULT -3.14,

h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),

889

i DEFAULT CURRENT_TIME,

890

j DEFAULT CURRENT_DATE,

891

k DEFAULT CURRENT_TIMESTAMP

);

} {}

# EVIDENCE-OF: R-10288-43169 For the purposes of the DEFAULT clause, an

896

# expression is considered constant provided that it does not contain

897

# any sub-queries or string constants enclosed in double quotes.

898

#

899

do_createtable_tests 3.4.1 -error {

900

default value of column [x] is not constant

901

} {

902

1 {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))} {}

903

2 {CREATE TABLE t5(x DEFAULT ( "abc" ))} {}

904

3 {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))} {}

905

4 {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))} {}

906

}

907

do_createtable_tests 3.4.2 -repair {

908

catchsql { DROP TABLE t5 }

909

} {

910

1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {}

911

2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {}

912

}

913

914

# EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table

915

# by an INSERT statement that does not provide explicit values for all

916

# table columns the values stored in the new row are determined by their

917

# default values

918

#

919

# Verify this with some assert statements for which all, some and no

920

# columns lack explicit values.

921

#

922

set sqlite_current_time 1000000000

923

do_createtable_tests 3.5 -query {

924

SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f),

925

quote(g), quote(h), quote(i), quote(j), quote(k)

926

FROM t4 ORDER BY rowid DESC LIMIT 1;

927

} {

928

1 "INSERT INTO t4 DEFAULT VALUES" {

929

NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14

930

'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}

931

}

932

933

2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {

934

1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}

935

}

936

937

3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {

938

NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1

939

}

940

941

4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" {

942

1 2 3 4 5 6 7 8 9 10 11

}

}

# EVIDENCE-OF: R-12572-62501 If the default value of the column is a

947

# constant NULL, text, blob or signed-number value, then that value is

948

# used directly in the new row.

949

#

950

do_execsql_test e_createtable-3.6.1 {

951

CREATE TABLE t5(

952

a DEFAULT NULL,

953

b DEFAULT 'text value',

954

c DEFAULT X'424C4F42',

955

d DEFAULT -45678.6,

e DEFAULT 394507

);

} {}

do_execsql_test e_createtable-3.6.2 {

960

INSERT INTO t5 DEFAULT VALUES;

961

SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;

962

} {NULL {'text value'} X'424C4F42' -45678.6 394507}

963

964

# EVIDENCE-OF: R-60616-50251 If the default value of a column is an

965

# expression in parentheses, then the expression is evaluated once for

966

# each row inserted and the results used in the new row.

967

#

968

# Test case 3.6.4 demonstrates that the expression is evaluated

969

# separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."

# command.

#

set ::nextint 0

proc nextint {} { incr ::nextint }

974

db func nextint nextint

975

976

do_execsql_test e_createtable-3.7.1 {

977

CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));

978

} {}

979

do_execsql_test e_createtable-3.7.2 {

980

INSERT INTO t6 DEFAULT VALUES;

981

SELECT quote(a), quote(b) FROM t6;

982

} {1 2}

983

do_execsql_test e_createtable-3.7.3 {

984

INSERT INTO t6(a) VALUES('X');

985

SELECT quote(a), quote(b) FROM t6;

986

} {1 2 'X' 3}

987

do_execsql_test e_createtable-3.7.4 {

988

INSERT INTO t6(a) SELECT a FROM t6;

989

SELECT quote(a), quote(b) FROM t6;

990

} {1 2 'X' 3 1 4 'X' 5}

991

992

# EVIDENCE-OF: R-15363-55230 If the default value of a column is

993

# CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used

994

# in the new row is a text representation of the current UTC date and/or

995

# time.

996

#

997

# This is difficult to test literally without knowing what time the

998

# user will run the tests. Instead, we test that the three cases

999

# above set the value to the current date and/or time according to

1000

# the xCurrentTime() method of the VFS. Which is usually the same

1001

# as UTC. In this case, however, we instrument it to always return

1002

# a time equivalent to "2001-09-09 01:46:40 UTC".

1003

#

1004

set sqlite_current_time 1000000000

1005

do_execsql_test e_createtable-3.8.1 {

1006

CREATE TABLE t7(

1007

a DEFAULT CURRENT_TIME,

1008

b DEFAULT CURRENT_DATE,

1009

c DEFAULT CURRENT_TIMESTAMP

1010

);

1011

} {}

1012

do_execsql_test e_createtable-3.8.2 {

1013

INSERT INTO t7 DEFAULT VALUES;

1014

SELECT quote(a), quote(b), quote(c) FROM t7;

1015

} {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}

1016

1017

1018

# EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value

1019

# is "HH:MM:SS".

1020

#

1021

# EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".

1022

#

1023

# EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is

1024

# "YYYY-MM-DD HH:MM:SS".

1025

#

1026

# The three above are demonstrated by tests 1, 2 and 3 below.

1027

# Respectively.

1028

#

1029

do_createtable_tests 3.8.3 -query {

1030

SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;

1031

} {

1032

1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}

1033

2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}

1034

3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}

1035

}

1036

1037

# EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a

1038

# collating sequence to use as the default collation sequence for the

1039

# column.

1040

#

1041

# EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the

1042

# default collation sequence is BINARY.

1043

#

1044

do_execsql_test e_createtable-3-9.1 {

1045

CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);

1046

INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc');

1047

INSERT INTO t8 VALUES('abc ', 'abc ', 'abc ', 'abc ');

1048

INSERT INTO t8 VALUES('ABC ', 'ABC ', 'ABC ', 'ABC ');

1049

INSERT INTO t8 VALUES('ABC', 'ABC', 'ABC', 'ABC');

1050

} {}

1051

do_createtable_tests 3.9 {

1052

2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }}

1053

3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }}

1054

4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }}

1055

5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }}

1056

}

1057

1058

# EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited

1059

# by the SQLITE_MAX_COLUMN compile-time parameter.

#

proc columns {n} {

set res [list]

for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }

1064

join $res ", "

1065

}

1066

do_execsql_test e_createtable-3.10.1 [subst {

1067

CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);

1068

}] {}

1069

do_catchsql_test e_createtable-3.10.2 [subst {

1070

CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);

1071

}] {1 {too many columns on t10}}

1072

1073

# EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at

1074

# runtime using the sqlite3_limit() C/C++ interface.

1075

#

1076

# A 30,000 byte blob consumes 30,003 bytes of record space. A record

1077

# that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests

1078

# 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered

1079

# at runtime, are based on this calculation.

1080

#

1081

sqlite3_limit db SQLITE_LIMIT_COLUMN 500

1082

do_execsql_test e_createtable-3.11.1 [subst {

1083

CREATE TABLE t10([columns 500]);

1084

}] {}

1085

do_catchsql_test e_createtable-3.11.2 [subst {

1086

CREATE TABLE t11([columns 501]);

1087

}] {1 {too many columns on t11}}

1088

1089

# Check that it is not possible to raise the column limit above its

1090

# default compile time value.

1091

#

1092

sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]

1093

do_catchsql_test e_createtable-3.11.3 [subst {

1094

CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);

1095

}] {1 {too many columns on t11}}

1096

1097

sqlite3_limit db SQLITE_LIMIT_LENGTH 90010

1098

do_execsql_test e_createtable-3.11.4 {

1099

CREATE TABLE t12(a, b, c);

1100

INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));

1101

} {}

1102

do_catchsql_test e_createtable-3.11.5 {

1103

INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));

1104

} {1 {string or blob too big}}

1105

1106

#-------------------------------------------------------------------------

1107

# Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT

1108

# NULL and CHECK constraints).

1109

#

1110

1111

# EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one

1112

# PRIMARY KEY.

1113

#

1114

# EVIDENCE-OF: R-18080-47271 If there is more than one PRIMARY KEY

1115

# clause in a single CREATE TABLE statement, it is an error.

1116

#

1117

# To test the two above, show that zero primary keys is Ok, one primary

1118

# key is Ok, and two or more primary keys is an error.

1119

#

1120

drop_all_tables

1121

do_createtable_tests 4.1.1 {

1122

1 "CREATE TABLE t1(a, b, c)" {}

1123

2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {}

1124

3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {}

1125

4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {}

1126

}

1127

do_createtable_tests 4.1.2 -error {

1128

table "t5" has more than one primary key

1129

} {

1130

1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {}

1131

2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {}

1132

3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {}

1133

4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}

1134

5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {}

1135

6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {}

1136

}

1137

1138

proc table_pk {tbl} {

1139

set pk [list]

1140

db eval "pragma table_info($tbl)" a {

1141

if {$a(pk)} { lappend pk $a(name) }

}

set pk

}

# EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a

1147

# column definition, then the primary key for the table consists of that

1148

# single column.

1149

#

1150

# The above is tested by 4.2.1.*

1151

#

1152

# EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as

1153

# a table-constraint, then the primary key of the table consists of the

1154

# list of columns specified as part of the PRIMARY KEY clause.

1155

#

1156

# The above is tested by 4.2.2.*

1157

#

1158

do_createtable_tests 4.2 -repair {

1159

catchsql { DROP TABLE t5 }

} -tclquery {

table_pk t5

} {

1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}

1164

1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a}

1165

1166

2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a}

1167

2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c}

1168

2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}

1169

}

1170

1171

# EVIDENCE-OF: R-33986-09410 Each row in a table with a primary key must

1172

# feature a unique combination of values in its primary key columns.

1173

#

1174

# EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts

1175

# to modify the table content so that two or more rows feature identical

1176

# primary key values, it is a constraint violation.

1177

#

1178

drop_all_tables

1179

do_execsql_test 4.3.0 {

1180

CREATE TABLE t1(x PRIMARY KEY, y);

1181

INSERT INTO t1 VALUES(0, 'zero');

1182

INSERT INTO t1 VALUES(45.5, 'one');

1183

INSERT INTO t1 VALUES('brambles', 'two');

1184

INSERT INTO t1 VALUES(X'ABCDEF', 'three');

1185

1186

CREATE TABLE t2(x, y, PRIMARY KEY(x, y));

1187

INSERT INTO t2 VALUES(0, 'zero');

1188

INSERT INTO t2 VALUES(45.5, 'one');

1189

INSERT INTO t2 VALUES('brambles', 'two');

1190

INSERT INTO t2 VALUES(X'ABCDEF', 'three');

1191

} {}

1192

1193

do_createtable_tests 4.3.1 -error { %s not unique } {

1194

1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"}

1195

2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"}

1196

3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"}

1197

4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"}

1198

5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"}

1199

1200

6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"}

1201

7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"}

1202

8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"}

1203

9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"}

1204

10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"}

1205

}

1206

do_createtable_tests 4.3.2 {

1207

1 "INSERT INTO t1 VALUES(-1, 0)" {}

1208

2 "INSERT INTO t1 VALUES(45.2, 'abc')" {}

1209

3 "INSERT INTO t1 VALUES(0.01, 'abc')" {}

1210

4 "INSERT INTO t1 VALUES('bramble', 'abc')" {}

1211

5 "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')" {}

1212

1213

6 "INSERT INTO t2 VALUES(0, 0)" {}

1214

7 "INSERT INTO t2 VALUES(45.5, 'abc')" {}

1215

8 "INSERT INTO t2 VALUES(0.0, 'abc')" {}

1216

9 "INSERT INTO t2 VALUES('brambles', 'abc')" {}

1217

10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {}

1218

}

1219

do_createtable_tests 4.3.3 -error { %s not unique } {

1220

1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"}

1221

2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"}

1222

3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"}

1223

4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"}

1224

5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"}

1225

1226

6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"}

1227

7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"

1228

{"columns x, y are"}

1229

8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}

1230

9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'"

1231

{"columns x, y are"}

1232

10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'"

1233

{"columns x, y are"}

}

# EVIDENCE-OF: R-52572-02078 For the purposes of determining the

1238

# uniqueness of primary key values, NULL values are considered distinct

1239

# from all other values, including other NULLs.

1240

#

1241

do_createtable_tests 4.4 {

1242

1 "INSERT INTO t1 VALUES(NULL, 0)" {}

1243

2 "INSERT INTO t1 VALUES(NULL, 0)" {}

1244

3 "INSERT INTO t1 VALUES(NULL, 0)" {}

1245

1246

4 "INSERT INTO t2 VALUES(NULL, 'zero')" {}

1247

5 "INSERT INTO t2 VALUES(NULL, 'one')" {}

1248

6 "INSERT INTO t2 VALUES(NULL, 'two')" {}

1249

7 "INSERT INTO t2 VALUES(NULL, 'three')" {}

1250

1251

8 "INSERT INTO t2 VALUES(0, NULL)" {}

1252

9 "INSERT INTO t2 VALUES(45.5, NULL)" {}

1253

10 "INSERT INTO t2 VALUES(0.0, NULL)" {}

1254

11 "INSERT INTO t2 VALUES('brambles', NULL)" {}

1255

12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {}

1256

1257

13 "INSERT INTO t2 VALUES(NULL, NULL)" {}

1258

14 "INSERT INTO t2 VALUES(NULL, NULL)" {}

1259

}

1260

1261

# EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY

1262

# SQLite allows NULL values in a PRIMARY KEY column.

1263

#

1264

# If the column is an integer primary key, attempting to insert a NULL

1265

# into the column triggers the auto-increment behaviour. Attempting

1266

# to use UPDATE to set an ipk column to a NULL value is an error.

1267

#

1268

do_createtable_tests 4.5.1 {

1269

1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3

1270

2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6

1271

3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7

1272

4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2

1273

}

1274

do_execsql_test 4.5.2 {

1275

CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);

1276

INSERT INTO t3 VALUES(1, NULL, 2);

1277

INSERT INTO t3 VALUES('x', NULL, 'y');

1278

SELECT u FROM t3;

1279

} {1 2}

1280

do_catchsql_test 4.5.3 {

1281

INSERT INTO t3 VALUES(2, 5, 3);

1282

UPDATE t3 SET u = NULL WHERE s = 2;

1283

} {1 {datatype mismatch}}

1284

1285

# EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY

1286

# KEY constraint, except that a single table may have any number of

1287

# UNIQUE constraints.

1288

#

1289

drop_all_tables

1290

do_createtable_tests 4.6 {

1291

1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {}

1292

2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {}

1293

3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}

1294

4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {}

1295

}

1296

1297

# EVIDENCE-OF: R-55240-58877 For each UNIQUE constraint on the table,

1298

# each row must feature a unique combination of values in the columns

1299

# identified by the UNIQUE constraint.

1300

#

1301

# EVIDENCE-OF: R-47733-51480 If an INSERT or UPDATE statement attempts

1302

# to modify the table content so that two or more rows feature identical

1303

# values in a set of columns that are subject to a UNIQUE constraint, it

1304

# is a constraint violation.

1305

#

1306

do_execsql_test 4.7.0 {

1307

INSERT INTO t1 VALUES(1, 2);

1308

INSERT INTO t1 VALUES(4.3, 5.5);

1309

INSERT INTO t1 VALUES('reveal', 'variableness');

1310

INSERT INTO t1 VALUES(X'123456', X'654321');

1311

1312

INSERT INTO t4 VALUES('xyx', 1, 1);

1313

INSERT INTO t4 VALUES('xyx', 2, 1);

1314

INSERT INTO t4 VALUES('uvw', 1, 1);

1315

}

1316

do_createtable_tests 4.7.1 -error { %s not unique } {

1317

1 "INSERT INTO t1 VALUES(1, 'one')" {{column a is}}

1318

2 "INSERT INTO t1 VALUES(4.3, 'two')" {{column a is}}

1319

3 "INSERT INTO t1 VALUES('reveal', 'three')" {{column a is}}

1320

4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{column a is}}

1321

1322

5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{column a is}}

1323

6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{column a is}}

1324

7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{column a is}}

1325

8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{column a is}}

1326

1327

9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{columns a, b, c are}}

1328

10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{columns a, b, c are}}

1329

11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{columns a, b, c are}}

1330

1331

12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{columns a, b, c are}}

1332

13 "UPDATE t4 SET b=1 WHERE rowid=2" {{columns a, b, c are}}

1333

14 "UPDATE t4 SET a=0, b=0, c=0" {{columns a, b, c are}}

1334

}

1335

1336

# EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the

1337

# purposes of UNIQUE constraints NULL values are considered distinct

1338

# from all other values (including other NULLs).

1339

#

1340

do_createtable_tests 4.8 {

1341

1 "INSERT INTO t1 VALUES(NULL, NULL)" {}

1342

2 "INSERT INTO t1 VALUES(NULL, NULL)" {}

1343

3 "UPDATE t1 SET a = NULL" {}

1344

4 "UPDATE t1 SET b = NULL" {}

1345

1346

5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}

1347

6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}

1348

7 "UPDATE t4 SET a = NULL" {}

1349

8 "UPDATE t4 SET b = NULL" {}

1350

9 "UPDATE t4 SET c = NULL" {}

1351

}

1352

1353

# EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both

1354

# UNIQUE and PRIMARY KEY constraints are implemented by creating an

1355

# index in the database (in the same way as a "CREATE UNIQUE INDEX"

1356

# statement would).

1357

do_createtable_tests 4.9 -repair drop_all_tables -query {

1358

SELECT count(*) FROM sqlite_master WHERE type='index'

1359

} {

1360

1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1

1361

2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0

1362

3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1

1363

4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2

1364

5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2

1365

}

1366

1367

# EVIDENCE-OF: R-02252-33116 Such an index is used like any other index

1368

# in the database to optimize queries.

1369

#

1370

do_execsql_test 4.10.0 {

1371

CREATE TABLE t1(a, b PRIMARY KEY);

1372

CREATE TABLE t2(a, b, c, UNIQUE(b, c));

1373

}

1374

do_createtable_tests 4.10 {

1375

1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5"

1376

{0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}

1377

1378

2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"

1379

{0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}

1380

1381

3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"

1382

{0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}}

1383

}

1384

1385

# EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a

1386

# column definition or specified as a table constraint. In practice it

1387

# makes no difference.

1388

#

1389

# All the tests that deal with CHECK constraints below (4.11.* and

1390

# 4.12.*) are run once for a table with the check constraint attached

1391

# to a column definition, and once with a table where the check

1392

# condition is specified as a table constraint.

1393

#

1394

# EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the

1395

# table or an existing row is updated, the expression associated with

1396

# each CHECK constraint is evaluated and cast to a NUMERIC value in the

1397

# same way as a CAST expression. If the result is zero (integer value 0

1398

# or real value 0.0), then a constraint violation has occurred.

1399

#

1400

drop_all_tables

1401

do_execsql_test 4.11 {

1402

CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));

1403

CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));

1404

INSERT INTO x1 VALUES('x', 'xx');

1405

INSERT INTO x1 VALUES('y', 'yy');

1406

INSERT INTO t1 SELECT * FROM x1;

1407

1408

CREATE TABLE x2(a CHECK( a||b ), b);

1409

CREATE TABLE t2(a, b, CHECK( a||b ));

1410

INSERT INTO x2 VALUES(1, 'xx');

1411

INSERT INTO x2 VALUES(1, 'yy');

1412

INSERT INTO t2 SELECT * FROM x2;

1413

}

1414

1415

do_createtable_tests 4.11 -error {constraint failed} {

1416

1a "INSERT INTO x1 VALUES('one', 0)" {}

1417

1b "INSERT INTO t1 VALUES('one', -4.0)" {}

1418

1419

2a "INSERT INTO x2 VALUES('abc', 1)" {}

1420

2b "INSERT INTO t2 VALUES('abc', 1)" {}

1421

1422

3a "INSERT INTO x2 VALUES(0, 'abc')" {}

1423

3b "INSERT INTO t2 VALUES(0, 'abc')" {}

1424

1425

4a "UPDATE t1 SET b=-1 WHERE rowid=1" {}

1426

4b "UPDATE x1 SET b=-1 WHERE rowid=1" {}

1427

1428

4a "UPDATE x2 SET a='' WHERE rowid=1" {}

1429

4b "UPDATE t2 SET a='' WHERE rowid=1" {}

1430

}

1431

1432

# EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,

1433

# or any other non-zero value, it is not a constraint violation.

1434

#

1435

do_createtable_tests 4.12 {

1436

1a "INSERT INTO x1 VALUES('one', NULL)" {}

1437

1b "INSERT INTO t1 VALUES('one', NULL)" {}

1438

1439

2a "INSERT INTO x1 VALUES('one', 2)" {}

1440

2b "INSERT INTO t1 VALUES('one', 2)" {}

1441

1442

3a "INSERT INTO x2 VALUES(1, 'abc')" {}

1443

3b "INSERT INTO t2 VALUES(1, 'abc')" {}

1444

}

1445

1446

# EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached

1447

# to a column definition, not specified as a table constraint.

1448

#

1449

drop_all_tables

1450

do_createtable_tests 4.13.1 {

1451

1 "CREATE TABLE t1(a NOT NULL, b)" {}

1452

2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {}

1453

3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {}

1454

}

1455

do_createtable_tests 4.13.2 -error {

1456

near "NOT": syntax error

1457

} {

1458

1 "CREATE TABLE t4(a, b, NOT NULL(a))" {}

1459

2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {}

1460

3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {}

1461

}

1462

1463

# EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the

1464

# associated column may not contain a NULL value. Attempting to set the

1465

# column value to NULL when inserting a new row or updating an existing

1466

# one causes a constraint violation.

1467

#

1468

# These tests use the tables created by 4.13.

1469

#

1470

do_execsql_test 4.14.0 {

1471

INSERT INTO t1 VALUES('x', 'y');

1472

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

1473

1474

INSERT INTO t2 VALUES('x', 'y');

1475

INSERT INTO t2 VALUES('z', NULL);

1476

1477

INSERT INTO t3 VALUES('x', 'y', 'z');

1478

INSERT INTO t3 VALUES(1, 2, 3);

1479

}

1480

do_createtable_tests 4.14 -error {

1481

%s may not be NULL

1482

} {

1483

1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a}

1484

2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a}

1485

3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c}

1486

4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b}

1487

5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a}

1488

}

1489

1490

# EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL

1491

# constraints may be explicitly assigned a default conflict resolution

1492

# algorithm by including a conflict-clause in their definitions.

1493

#

1494

# Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE

1495

#

1496

# Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL

1497

# and UNIQUE constraints, respectively.

1498

#

1499

drop_all_tables

1500

do_execsql_test 4.15.0 {

1501

CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);

1502

CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);

1503

CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);

1504

CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);

1505

CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);

1506

CREATE TABLE t1_xx(a PRIMARY KEY, b);

1507

1508

INSERT INTO t1_ab VALUES(1, 'one');

1509

INSERT INTO t1_ab VALUES(2, 'two');

1510

INSERT INTO t1_ro SELECT * FROM t1_ab;

1511

INSERT INTO t1_ig SELECT * FROM t1_ab;

1512

INSERT INTO t1_fa SELECT * FROM t1_ab;

1513

INSERT INTO t1_re SELECT * FROM t1_ab;

1514

INSERT INTO t1_xx SELECT * FROM t1_ab;

1515

1516

CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);

1517

CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);

1518

CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);

1519

CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);

1520

CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);

1521

CREATE TABLE t2_xx(a, b NOT NULL);

1522

1523

INSERT INTO t2_ab VALUES(1, 'one');

1524

INSERT INTO t2_ab VALUES(2, 'two');

1525

INSERT INTO t2_ro SELECT * FROM t2_ab;

1526

INSERT INTO t2_ig SELECT * FROM t2_ab;

1527

INSERT INTO t2_fa SELECT * FROM t2_ab;

1528

INSERT INTO t2_re SELECT * FROM t2_ab;

1529

INSERT INTO t2_xx SELECT * FROM t2_ab;

1530

1531

CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);

1532

CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);

1533

CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);

1534

CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);

1535

CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);

1536

CREATE TABLE t3_xx(a, b, UNIQUE(a, b));

1537

1538

INSERT INTO t3_ab VALUES(1, 'one');

1539

INSERT INTO t3_ab VALUES(2, 'two');

1540

INSERT INTO t3_ro SELECT * FROM t3_ab;

1541

INSERT INTO t3_ig SELECT * FROM t3_ab;

1542

INSERT INTO t3_fa SELECT * FROM t3_ab;

1543

INSERT INTO t3_re SELECT * FROM t3_ab;

1544

INSERT INTO t3_xx SELECT * FROM t3_ab;

1545

}

1546

1547

foreach {tn tbl res ac data} {

1548

1 t1_ab {1 {column a is not unique}} 0 {1 one 2 two 3 three}

1549

2 t1_ro {1 {column a is not unique}} 1 {1 one 2 two}

1550

3 t1_fa {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string}

1551

4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}

1552

5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}

1553

6 t1_xx {1 {column a is not unique}} 0 {1 one 2 two 3 three}

1554

} {

1555

catchsql COMMIT

1556

do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

1557

1558

do_catchsql_test 4.15.$tn.2 "

1559

INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;

1560

" $res

1561

1562

do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac

1563

do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data

1564

}

1565

foreach {tn tbl res ac data} {

1566

1 t2_ab {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three}

1567

2 t2_ro {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two}

1568

3 t2_fa {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx}

1569

4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}

1570

5 t2_re {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three}

1571

6 t2_xx {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three}

1572

} {

1573

catchsql COMMIT

1574

do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

1575

1576

do_catchsql_test 4.16.$tn.2 "

1577

INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl

1578

" $res

1579

1580

do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac

1581

do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data

1582

}

1583

foreach {tn tbl res ac data} {

1584

1 t3_ab {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}

1585

2 t3_ro {1 {columns a, b are not unique}} 1 {1 one 2 two}

1586

3 t3_fa {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three}

1587

4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}

1588

5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}

1589

6 t3_xx {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}

1590

} {

1591

catchsql COMMIT

1592

do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

1593

1594

do_catchsql_test 4.17.$tn.2 "

1595

INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl

1596

" $res

1597

1598

do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac

1599

do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data

}

catchsql COMMIT

# EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not

1604

# include a conflict-clause or it is a CHECK constraint, the default

1605

# conflict resolution algorithm is ABORT.

1606

#

1607

# The first half of the above is tested along with explicit ON

1608

# CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx

1609

# and t3_xx). The following just tests that the default conflict

1610

# handling for CHECK constraints is ABORT.

1611

#

1612

do_execsql_test 4.18.1 {

1613

CREATE TABLE t4(a, b CHECK (b!=10));

1614

INSERT INTO t4 VALUES(1, 2);

1615

INSERT INTO t4 VALUES(3, 4);

1616

}

1617

do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }

1618

do_catchsql_test 4.18.3 {

1619

INSERT INTO t4 SELECT a+4, b+4 FROM t4

1620

} {1 {constraint failed}}

1621

do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0

1622

do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}

1623

1624

# EVIDENCE-OF: R-19114-56113 Different constraints within the same table

1625

# may have different default conflict resolution algorithms.

1626

#

1627

do_execsql_test 4.19.0 {

1628

CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);

1629

}

1630

do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}

1631

do_execsql_test 4.19.2 { SELECT * FROM t5 } {}

1632

do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \

1633

{1 {t5.b may not be NULL}}

1634

do_execsql_test 4.19.4 { SELECT * FROM t5 } {}

1635

1636

#------------------------------------------------------------------------

1637

# Tests for INTEGER PRIMARY KEY and rowid related statements.

1638

#

1639

1640

# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one

1641

# of the special case-independent names "rowid", "oid", or "_rowid_" in

1642

# place of a column name.

1643

#

1644

drop_all_tables

1645

do_execsql_test 5.1.0 {

1646

CREATE TABLE t1(x, y);

1647

INSERT INTO t1 VALUES('one', 'first');

1648

INSERT INTO t1 VALUES('two', 'second');

1649

INSERT INTO t1 VALUES('three', 'third');

1650

}

1651

do_createtable_tests 5.1 {

1652

1 "SELECT rowid FROM t1" {1 2 3}

1653

2 "SELECT oid FROM t1" {1 2 3}

1654

3 "SELECT _rowid_ FROM t1" {1 2 3}

1655

4 "SELECT ROWID FROM t1" {1 2 3}

1656

5 "SELECT OID FROM t1" {1 2 3}

1657

6 "SELECT _ROWID_ FROM t1" {1 2 3}

1658

7 "SELECT RoWiD FROM t1" {1 2 3}

1659

8 "SELECT OiD FROM t1" {1 2 3}

1660

9 "SELECT _RoWiD_ FROM t1" {1 2 3}

1661

}

1662

1663

# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column

1664

# named "rowid", "oid" or "_rowid_", then that name always refers the

1665

# explicitly declared column and cannot be used to retrieve the integer

1666

# rowid value.

1667

#

1668

do_execsql_test 5.2.0 {

1669

CREATE TABLE t2(oid, b);

1670

CREATE TABLE t3(a, _rowid_);

1671

CREATE TABLE t4(a, b, rowid);

1672

1673

INSERT INTO t2 VALUES('one', 'two');

1674

INSERT INTO t2 VALUES('three', 'four');

1675

1676

INSERT INTO t3 VALUES('five', 'six');

1677

INSERT INTO t3 VALUES('seven', 'eight');

1678

1679

INSERT INTO t4 VALUES('nine', 'ten', 'eleven');

1680

INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');

1681

}

1682

do_createtable_tests 5.2 {

1683

1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2}

1684

2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight}

1685

3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2}

}

# Argument $tbl is the name of a table in the database. Argument $col is

1690

# the name of one of the tables columns. Return 1 if $col is an alias for

1691

# the rowid, or 0 otherwise.

1692

#

1693

proc is_integer_primary_key {tbl col} {

1694

lindex [db eval [subst {

1695

DELETE FROM $tbl;

1696

INSERT INTO $tbl ($col) VALUES(0);

1697

SELECT (rowid==$col) FROM $tbl;

DELETE FROM $tbl;

}]] 0

}

# EVIDENCE-OF: R-53738-31673 With one exception, if a table has a

1703

# primary key that consists of a single column, and the declared type of

1704

# that column is "INTEGER" in any mixture of upper and lower case, then

1705

# the column becomes an alias for the rowid.

1706

#

1707

# EVIDENCE-OF: R-45951-08347 if the declaration of a column with

1708

# declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does

1709

# not become an alias for the rowid and is not classified as an integer

1710

# primary key.

1711

#

1712

do_createtable_tests 5.3 -tclquery {

1713

is_integer_primary_key t5 pk

1714

} -repair {

1715

catchsql { DROP TABLE t5 }

1716

} {

1717

1 "CREATE TABLE t5(pk integer primary key)" 1

1718

2 "CREATE TABLE t5(pk integer, primary key(pk))" 1

1719

3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1

1720

4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0

1721

5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0

1722

6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0

1723

7 "CREATE TABLE t5(pk int primary key, v integer)" 0

1724

8 "CREATE TABLE t5(pk inTEger primary key)" 1

1725

9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1

1726

10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1

1727

}

1728

1729

# EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or

1730

# "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary

1731

# key column to behave as an ordinary table column with integer affinity

1732

# and a unique index, not as an alias for the rowid.

1733

#

1734

do_execsql_test 5.4.1 {

1735

CREATE TABLE t6(pk INT primary key);

1736

CREATE TABLE t7(pk BIGINT primary key);

1737

CREATE TABLE t8(pk SHORT INTEGER primary key);

1738

CREATE TABLE t9(pk UNSIGNED INTEGER primary key);

1739

}

1740

do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0

1741

do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0

1742

do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0

1743

do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0

1744

1745

do_execsql_test 5.4.3 {

1746

INSERT INTO t6 VALUES('2.0');

1747

INSERT INTO t7 VALUES('2.0');

1748

INSERT INTO t8 VALUES('2.0');

1749

INSERT INTO t9 VALUES('2.0');

1750

SELECT typeof(pk), pk FROM t6;

1751

SELECT typeof(pk), pk FROM t7;

1752

SELECT typeof(pk), pk FROM t8;

1753

SELECT typeof(pk), pk FROM t9;

1754

} {integer 2 integer 2 integer 2 integer 2}

1755

1756

do_catchsql_test 5.4.4.1 {

1757

INSERT INTO t6 VALUES(2)

1758

} {1 {column pk is not unique}}

1759

do_catchsql_test 5.4.4.2 {

1760

INSERT INTO t7 VALUES(2)

1761

} {1 {column pk is not unique}}

1762

do_catchsql_test 5.4.4.3 {

1763

INSERT INTO t8 VALUES(2)

1764

} {1 {column pk is not unique}}

1765

do_catchsql_test 5.4.4.4 {

1766

INSERT INTO t9 VALUES(2)

1767

} {1 {column pk is not unique}}

1768

1769

# EVIDENCE-OF: R-56094-57830 the following three table declarations all

1770

# cause the column "x" to be an alias for the rowid (an integer primary

1771

# key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE

1772

# t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,

1773

# z, PRIMARY KEY(x DESC));

1774

#

1775

# EVIDENCE-OF: R-20149-25884 the following declaration does not result

1776

# in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY

1777

# KEY DESC, y, z);

1778

#

1779

do_createtable_tests 5 -tclquery {

1780

is_integer_primary_key t x

1781

} -repair {

1782

catchsql { DROP TABLE t }

1783

} {

1784

5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1

1785

5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1

1786

5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1

1787

6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0

1788

}

1789

1790

# EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an

1791

# UPDATE statement in the same way as any other column value can, either

1792

# using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by

1793

# using an alias created by an integer primary key.

1794

#

1795

do_execsql_test 5.7.0 {

1796

CREATE TABLE t10(a, b);

1797

INSERT INTO t10 VALUES('ten', 10);

1798

1799

CREATE TABLE t11(a, b INTEGER PRIMARY KEY);

1800

INSERT INTO t11 VALUES('ten', 10);

1801

}

1802

do_createtable_tests 5.7.1 -query {

1803

SELECT rowid, _rowid_, oid FROM t10;

1804

} {

1805

1 "UPDATE t10 SET rowid = 5" {5 5 5}

1806

2 "UPDATE t10 SET _rowid_ = 6" {6 6 6}

1807

3 "UPDATE t10 SET oid = 7" {7 7 7}

1808

}

1809

do_createtable_tests 5.7.2 -query {

1810

SELECT rowid, _rowid_, oid, b FROM t11;

1811

} {

1812

1 "UPDATE t11 SET rowid = 5" {5 5 5 5}

1813

2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}

1814

3 "UPDATE t11 SET oid = 7" {7 7 7 7}

1815

4 "UPDATE t11 SET b = 8" {8 8 8 8}

1816

}

1817

1818

# EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide

1819

# a value to use as the rowid for each row inserted.

1820

#

1821

do_createtable_tests 5.8.1 -query {

1822

SELECT rowid, _rowid_, oid FROM t10;

1823

} -repair {

1824

execsql { DELETE FROM t10 }

1825

} {

1826

1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15}

1827

2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16}

1828

3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17}

1829

4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3}

1830

}

1831

do_createtable_tests 5.8.2 -query {

1832

SELECT rowid, _rowid_, oid, b FROM t11;

1833

} -repair {

1834

execsql { DELETE FROM t11 }

1835

} {

1836

1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15}

1837

2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16}

1838

3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17}

1839

4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2}

1840

}

1841

1842

# EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer

1843

# primary key or rowid column must contain integer values. Integer

1844

# primary key or rowid columns are not able to hold floating point

1845

# values, strings, BLOBs, or NULLs.

1846

#

1847

# This is considered by the tests for the following 3 statements,

1848

# which show that:

1849

#

1850

# 1. Attempts to UPDATE a rowid column to a non-integer value fail,

1851

# 2. Attempts to INSERT a real, string or blob value into a rowid

1852

# column fail, and

1853

# 3. Attempting to INSERT a NULL value into a rowid column causes the

1854

# system to automatically select an integer value to use.

#

# EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an

1859

# integer primary key or rowid column to a NULL or blob value, or to a

1860

# string or real value that cannot be losslessly converted to an

1861

# integer, a "datatype mismatch" error occurs and the statement is

# aborted.

#

drop_all_tables

do_execsql_test 5.9.0 {

1866

CREATE TABLE t12(x INTEGER PRIMARY KEY, y);

1867

INSERT INTO t12 VALUES(5, 'five');

1868

}

1869

do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {

1870

1 "UPDATE t12 SET x = 4" {integer 4}

1871

2 "UPDATE t12 SET x = 10.0" {integer 10}

1872

3 "UPDATE t12 SET x = '12.0'" {integer 12}

1873

4 "UPDATE t12 SET x = '-15.0'" {integer -15}

1874

}

1875

do_createtable_tests 5.9.2 -error {

1876

datatype mismatch

1877

} {

1878

1 "UPDATE t12 SET x = 4.1" {}

1879

2 "UPDATE t12 SET x = 'hello'" {}

1880

3 "UPDATE t12 SET x = NULL" {}

1881

4 "UPDATE t12 SET x = X'ABCD'" {}

1882

5 "UPDATE t12 SET x = X'3900'" {}

1883

6 "UPDATE t12 SET x = X'39'" {}

1884

}

1885

1886

# EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a

1887

# blob value, or a string or real value that cannot be losslessly

1888

# converted to an integer into an integer primary key or rowid column, a

1889

# "datatype mismatch" error occurs and the statement is aborted.

1890

#

1891

do_execsql_test 5.10.0 { DELETE FROM t12 }

1892

do_createtable_tests 5.10.1 -error {

1893

datatype mismatch

1894

} {

1895

1 "INSERT INTO t12(x) VALUES(4.1)" {}

1896

2 "INSERT INTO t12(x) VALUES('hello')" {}

1897

3 "INSERT INTO t12(x) VALUES(X'ABCD')" {}

1898

4 "INSERT INTO t12(x) VALUES(X'3900')" {}

1899

5 "INSERT INTO t12(x) VALUES(X'39')" {}

1900

}

1901

do_createtable_tests 5.10.2 -query {

1902

SELECT typeof(x), x FROM t12

1903

} -repair {

1904

execsql { DELETE FROM t12 }

1905

} {

1906

1 "INSERT INTO t12(x) VALUES(4)" {integer 4}

1907

2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10}

1908

3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12}

1909

4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000}

1910

5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}

1911

}

1912

1913

# EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a

1914

# NULL value into a rowid or integer primary key column, the system

1915

# chooses an integer value to use as the rowid automatically.

1916

#

1917

do_execsql_test 5.11.0 { DELETE FROM t12 }

1918

do_createtable_tests 5.11 -query {

1919

SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)

1920

} {

1921

1 "INSERT INTO t12 DEFAULT VALUES" {integer 1}

1922

2 "INSERT INTO t12(y) VALUES(5)" {integer 2}

1923

3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3}

1924

4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12"

1925

{integer 4 integer 5 integer 6}

1926

5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"

1927

{integer 7 integer 8 integer 9}

1928

}

1929

1930

finish_test

wasCSharpSQLite – Blame information for rev 1