~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx

1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx

2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx

3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx

4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx

5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx

6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx

7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx

8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx

9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx

(10 rows)

-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1

SELECT onek.unique1, onek.stringu1 FROM onek

WHERE onek.unique1 < 20

ORDER BY unique1 using >;

unique1 | stringu1

---------+----------

19 | TAAAAA

18 | SAAAAA

17 | RAAAAA

16 | QAAAAA

15 | PAAAAA

14 | OAAAAA

13 | NAAAAA

12 | MAAAAA

11 | LAAAAA

10 | KAAAAA

9 | JAAAAA

8 | IAAAAA

7 | HAAAAA

6 | GAAAAA

5 | FAAAAA

4 | EAAAAA

3 | DAAAAA

2 | CAAAAA

1 | BAAAAA

0 | AAAAAA

(20 rows)

-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2

SELECT onek.unique1, onek.stringu1 FROM onek

WHERE onek.unique1 > 980

ORDER BY stringu1 using <;

unique1 | stringu1

---------+----------

988 | AMAAAA

989 | BMAAAA

990 | CMAAAA

991 | DMAAAA

992 | EMAAAA

993 | FMAAAA

994 | GMAAAA

995 | HMAAAA

996 | IMAAAA

997 | JMAAAA

998 | KMAAAA

999 | LMAAAA

981 | TLAAAA

982 | ULAAAA

983 | VLAAAA

984 | WLAAAA

985 | XLAAAA

986 | YLAAAA

987 | ZLAAAA

(19 rows)

-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |

-- sort +1d -2 +0nr -1

SELECT onek.unique1, onek.string4 FROM onek

WHERE onek.unique1 > 980

ORDER BY string4 using <, unique1 using >;

unique1 | string4

---------+---------

999 | AAAAxx

995 | AAAAxx

983 | AAAAxx

982 | AAAAxx

981 | AAAAxx

998 | HHHHxx

997 | HHHHxx

993 | HHHHxx

100

990 | HHHHxx

101

986 | HHHHxx

102

996 | OOOOxx

103

991 | OOOOxx

104

988 | OOOOxx

105

987 | OOOOxx

106

985 | OOOOxx

107

994 | VVVVxx

108

992 | VVVVxx

109

989 | VVVVxx

110

984 | VVVVxx

111

(19 rows)

112

113

114

-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |

115

-- sort +1dr -2 +0n -1

116

117

SELECT onek.unique1, onek.string4 FROM onek

118

WHERE onek.unique1 > 980

119

ORDER BY string4 using >, unique1 using <;

120

unique1 | string4

121

---------+---------

122

984 | VVVVxx

123

989 | VVVVxx

124

992 | VVVVxx

125

994 | VVVVxx

126

985 | OOOOxx

127

987 | OOOOxx

128

988 | OOOOxx

129

991 | OOOOxx

130

996 | OOOOxx

131

986 | HHHHxx

132

990 | HHHHxx

133

993 | HHHHxx

134

997 | HHHHxx

135

998 | HHHHxx

136

981 | AAAAxx

137

982 | AAAAxx

138

983 | AAAAxx

139

995 | AAAAxx

140

999 | AAAAxx

141

(19 rows)

142

143

144

-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |

145

-- sort +0nr -1 +1d -2

146

147

SELECT onek.unique1, onek.string4 FROM onek

148

WHERE onek.unique1 < 20

149

ORDER BY unique1 using >, string4 using <;

150

unique1 | string4

151

---------+---------

152

19 | OOOOxx

153

18 | VVVVxx

154

17 | HHHHxx

155

16 | OOOOxx

156

15 | VVVVxx

157

14 | AAAAxx

158

13 | OOOOxx

159

12 | AAAAxx

160

11 | OOOOxx

161

10 | AAAAxx

162

9 | HHHHxx

163

8 | HHHHxx

164

7 | VVVVxx

165

6 | OOOOxx

166

5 | HHHHxx

167

4 | HHHHxx

168

3 | VVVVxx

169

2 | OOOOxx

170

1 | OOOOxx

171

0 | OOOOxx

172

(20 rows)

173

174

175

-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |

176

-- sort +0n -1 +1dr -2

177

178

SELECT onek.unique1, onek.string4 FROM onek

179

WHERE onek.unique1 < 20

180

ORDER BY unique1 using <, string4 using >;

181

unique1 | string4

182

---------+---------

183

0 | OOOOxx

184

1 | OOOOxx

185

2 | OOOOxx

186

3 | VVVVxx

187

4 | HHHHxx

188

5 | HHHHxx

189

6 | OOOOxx

190

7 | VVVVxx

191

8 | HHHHxx

192

9 | HHHHxx

193

10 | AAAAxx

194

11 | OOOOxx

195

12 | AAAAxx

196

13 | OOOOxx

197

14 | AAAAxx

198

15 | VVVVxx

199

16 | OOOOxx

200

17 | HHHHxx

201

18 | VVVVxx

202

19 | OOOOxx

203

(20 rows)

204

205

206

-- test partial btree indexes

207

208

-- As of 7.2, planner probably won't pick an indexscan without stats,

209

-- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan

210

-- followed by sort, because that could hide index ordering problems.

211

212

ANALYZE onek2;

213

SET enable_seqscan TO off;

214

SET enable_bitmapscan TO off;

215

SET enable_sort TO off;

216

217

-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1

218

219

SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10;

220

221

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

222

0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx

223

1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx

224

2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx

225

3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx

226

4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx

227

5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx

228

6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx

229

7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx

230

8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx

231

9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx

232

(10 rows)

233

234

235

-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1

236

237

SELECT onek2.unique1, onek2.stringu1 FROM onek2

238

WHERE onek2.unique1 < 20

239

ORDER BY unique1 using >;

240

unique1 | stringu1

241

---------+----------

242

19 | TAAAAA

243

18 | SAAAAA

244

17 | RAAAAA

245

16 | QAAAAA

246

15 | PAAAAA

247

14 | OAAAAA

248

13 | NAAAAA

249

12 | MAAAAA

250

11 | LAAAAA

251

10 | KAAAAA

252

9 | JAAAAA

253

8 | IAAAAA

254

7 | HAAAAA

255

6 | GAAAAA

256

5 | FAAAAA

257

4 | EAAAAA

258

3 | DAAAAA

259

2 | CAAAAA

260

1 | BAAAAA

261

0 | AAAAAA

262

(20 rows)

263

264

265

-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2

266

267

SELECT onek2.unique1, onek2.stringu1 FROM onek2

268

WHERE onek2.unique1 > 980;

269

unique1 | stringu1

270

---------+----------

271

981 | TLAAAA

272

982 | ULAAAA

273

983 | VLAAAA

274

984 | WLAAAA

275

985 | XLAAAA

276

986 | YLAAAA

277

987 | ZLAAAA

278

988 | AMAAAA

279

989 | BMAAAA

280

990 | CMAAAA

281

991 | DMAAAA

282

992 | EMAAAA

283

993 | FMAAAA

284

994 | GMAAAA

285

995 | HMAAAA

286

996 | IMAAAA

287

997 | JMAAAA

288

998 | KMAAAA

289

999 | LMAAAA

290

(19 rows)

291

292

RESET enable_seqscan;

293

RESET enable_bitmapscan;

294

RESET enable_sort;

295

SELECT two, stringu1, ten, string4

296

INTO TABLE tmp

297

FROM onek;

298

299

-- awk '{print $1,$2;}' person.data |

300

-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |

301

-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |

302

-- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data

303

304

-- SELECT name, age FROM person*; ??? check if different

305

SELECT p.name, p.age FROM person* p;

306

name | age

307

---------+-----

308

mike | 40

309

joe | 20

310

sally | 34

311

sandra | 19

312

alex | 30

313

sue | 50

314

denise | 24

315

sarah | 88

316

teresa | 38

317

nan | 28

318

leah | 68

319

wendy | 78

320

melissa | 28

321

joan | 18

322

mary | 8

323

jane | 58

324

liza | 38

325

jean | 28

326

jenifer | 38

327

juanita | 58

328

susan | 78

329

zena | 98

330

martie | 88

331

chris | 78

332

pat | 18

333

zola | 58

334

louise | 98

335

edna | 18

336

bertha | 88

337

sumi | 38

338

koko | 88

339

gina | 18

340

rean | 48

341

sharon | 78

342

paula | 68

343

julie | 68

344

belinda | 38

345

karen | 48

346

carina | 58

347

diane | 18

348

esther | 98

349

trudy | 88

350

fanny | 8

351

carmen | 78

352

lita | 25

353

pamela | 48

354

sandy | 38

355

trisha | 88

356

uma | 78

357

velma | 68

358

sharon | 25

359

sam | 30

360

bill | 20

361

fred | 28

362

larry | 60

363

jeff | 23

364

cim | 30

365

linda | 19

366

(58 rows)

367

368

369

-- awk '{print $1,$2;}' person.data |

370

-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |

371

-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |

372

-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |

373

-- sort +1nr -2

374

375

SELECT p.name, p.age FROM person* p ORDER BY age using >, name;

376

name | age

377

---------+-----

378

esther | 98

379

louise | 98

380

zena | 98

381

bertha | 88

382

koko | 88

383

martie | 88

384

sarah | 88

385

trisha | 88

386

trudy | 88

387

carmen | 78

388

chris | 78

389

sharon | 78

390

susan | 78

391

uma | 78

392

wendy | 78

393

julie | 68

394

leah | 68

395

paula | 68

396

velma | 68

397

larry | 60

398

carina | 58

399

jane | 58

400

juanita | 58

401

zola | 58

402

sue | 50

403

karen | 48

404

pamela | 48

405

rean | 48

406

mike | 40

407

belinda | 38

408

jenifer | 38

409

liza | 38

410

sandy | 38

411

sumi | 38

412

teresa | 38

413

sally | 34

414

alex | 30

415

cim | 30

416

sam | 30

417

fred | 28

418

jean | 28

419

melissa | 28

420

nan | 28

421

lita | 25

422

sharon | 25

423

denise | 24

424

jeff | 23

425

bill | 20

426

joe | 20

427

linda | 19

428

sandra | 19

429

diane | 18

430

edna | 18

431

gina | 18

432

joan | 18

433

pat | 18

434

fanny | 8

435

mary | 8

436

(58 rows)

437

438

439

-- Test some cases involving whole-row Var referencing a subquery

440

441

select foo from (select 1) as foo;

442

foo

443

-----

444

(1)

445

(1 row)

446

447

select foo from (select null) as foo;

448

foo

449

-----

450

()

451

(1 row)

452

453

select foo from (select 'xyzzy',1,null) as foo;

454

foo

455

------------

456

(xyzzy,1,)

457

(1 row)

458

459

460

-- Test VALUES lists

461

462

select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j)

463

WHERE onek.unique1 = v.i and onek.stringu1 = v.j;

464

465

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+--------

466

147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA

467

931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA

468

(2 rows)

469

470

-- a more complex case

471

-- looks like we're coding lisp :-)

472

select * from onek,

473

(values ((select i from

474

(values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)

475

order by i asc limit 1))) bar (i)

476

where onek.unique1 = bar.i;

477

478

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---

479

2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2

480

(1 row)

481

482

-- try VALUES in a subquery

483

select * from onek

484

where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))

485

order by unique1;

486

487

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

488

1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx

489

20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx

490

99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx

491

(3 rows)

492

493

-- VALUES is also legal as a standalone query or a set-operation member

494

VALUES (1,2), (3,4+4), (7,77.7);

495

column1 | column2

496

---------+---------

497

1 | 2

498

3 | 8

499

7 | 77.7

500

(3 rows)

501

502

VALUES (1,2), (3,4+4), (7,77.7)

503

UNION ALL

504

SELECT 2+2, 57

505

UNION ALL

506

TABLE int8_tbl;

507

column1 | column2

508

------------------+-------------------

509

1 | 2

510

3 | 8

511

7 | 77.7

512

4 | 57

513

123 | 456

514

123 | 4567890123456789

515

4567890123456789 | 123

516

4567890123456789 | 4567890123456789

517

4567890123456789 | -4567890123456789

518

(9 rows)

519

520

521

-- Test ORDER BY options

522

523

CREATE TEMP TABLE foo (f1 int);

524

INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);

525

SELECT * FROM foo ORDER BY f1;

526

527

----

528

529

530

531

532

533

534

535

(7 rows)

536

537

SELECT * FROM foo ORDER BY f1 ASC; -- same thing

538

539

----

540

541

542

543

544

545

546

547

(7 rows)

548

549

SELECT * FROM foo ORDER BY f1 NULLS FIRST;

550

551

----

552

553

554

555

556

557

558

559

(7 rows)

560

561

SELECT * FROM foo ORDER BY f1 DESC;

562

563

----

564

565

566

567

568

569

570

571

(7 rows)

572

573

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;

574

575

----

576

577

578

579

580

581

582

583

(7 rows)

584

585

-- check if indexscans do the right things

586

CREATE INDEX fooi ON foo (f1);

587

SET enable_sort = false;

588

SELECT * FROM foo ORDER BY f1;

589

590

----

591

592

593

594

595

596

597

598

(7 rows)

599

600

SELECT * FROM foo ORDER BY f1 NULLS FIRST;

601

602

----

603

604

605

606

607

608

609

610

(7 rows)

611

612

SELECT * FROM foo ORDER BY f1 DESC;

613

614

----

615

616

617

618

619

620

621

622

(7 rows)

623

624

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;

625

626

----

627

628

629

630

631

632

633

634

(7 rows)

635

636

DROP INDEX fooi;

637

CREATE INDEX fooi ON foo (f1 DESC);

638

SELECT * FROM foo ORDER BY f1;

639

640

----

641

642

643

644

645

646

647

648

(7 rows)

649

650

SELECT * FROM foo ORDER BY f1 NULLS FIRST;

651

652

----

653

654

655

656

657

658

659

660

(7 rows)

661

662

SELECT * FROM foo ORDER BY f1 DESC;

663

664

----

665

666

667

668

669

670

671

672

(7 rows)

673

674

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;

675

676

----

677

678

679

680

681

682

683

684

(7 rows)

685

686

DROP INDEX fooi;

687

CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);

688

SELECT * FROM foo ORDER BY f1;

689

690

----

691

692

693

694

695

696

697

698

(7 rows)

699

700

SELECT * FROM foo ORDER BY f1 NULLS FIRST;

701

702

----

703

704

705

706

707

708

709

710

(7 rows)

711

712

SELECT * FROM foo ORDER BY f1 DESC;

713

714

----

715

716

717

718

719

720

721

722

(7 rows)

723

724

SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;

725

726

----

727

728

729

730

731

732

733

734

(7 rows)

735

736

737

-- Test some corner cases that have been known to confuse the planner

738

739

-- ORDER BY on a constant doesn't really need any sorting

740

SELECT 1 AS x ORDER BY x;

741

742

---

743

744

(1 row)

745

746

-- But ORDER BY on a set-valued expression does

747

create function sillysrf(int) returns setof int as

748

'values (1),(10),(2),($1)' language sql immutable;

749

select sillysrf(42);

750

sillysrf

751

----------

752

753

754

755

756

(4 rows)

757

758

select sillysrf(-1) order by 1;

759

sillysrf

760

----------

761

-1

762

763

764

765

(4 rows)

766

767

drop function sillysrf(int);

768

-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict

769

-- (see bug #5084)

770

select * from (values (2),(null),(1)) v(k) where k = k order by k;

771

772

---

773

774

775

(2 rows)

776

777

select * from (values (2),(null),(1)) v(k) where k = k;

778

779

---

780

781

782

(2 rows)

783

Older »