Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #13811: Default ordering colums

BUG #13811: Default ordering colums

From
exru@mail.ru
Date:
The following bug has been logged on the website:

Bug reference:      13811
Logged by:          Vyacheslav
Email address:      exru@mail.ru
PostgreSQL version: 9.4.5
Operating system:   Debian 8.1
Description:

--1) create table and make batch insert of values

CREATE TABLE auction
(
  id serial NOT NULL primary key,
  name text,
  type smallint,
  client_id integer,
  description text,
  category_id integer,
  dt_start timestamp(0) without time zone,
  dt_end timestamp(0) without time zone,
  status smallint DEFAULT 1
);

INSERT INTO "auction" ("id", "name", "type", "client_id", "description",
"status", "category_id", "dt_start", "dt_end") VALUES (0, 'Аукцион1', 1, 1,
'Аукцион0', -1, 5, '2015-12-10 15:38:00', NULL), (1, 'Аукцион1', -1, 1,
'Аукцион1', 0, 5, '2015-12-10 15:38:00', NULL), (2, 'Аукцион2', 1, 1,
'Аукцион2', 1, 5, '2015-12-10 15:38:00', NULL), (3, 'Аукцион3', 1, 1,
'Аукцион3', 1, 5, '2015-12-10 15:38:00', NULL), (4, 'Аукцион4', 1, 1,
'Аукцион4', 3, 7, '2015-12-10 15:38:00', NULL), (5, 'Аукцион5', 1, 1,
'Аукцион5', 4, 5, '2015-12-10 15:38:00', NULL), (6, 'Аукцион6', 1, 1,
'Аукцион6', 5, 6, '2015-12-10 15:38:00', NULL), (7, 'Аукцион7', -1, 1,
'Аукцион7', 6, 6, '2015-12-10 15:38:00', NULL), (8, 'Аукцион7', 1, 1,
'Аукцион7', 7, 7, '2015-12-10 15:38:00', NULL);

2) then i maked a select (SELECT * FROM auction;) i've get the next rows
with orders:

0;"Аукцион1";1;1;"Аукцион0";5;"2015-12-10 15:38:00";"";-1
1;"Аукцион1";-1;1;"Аукцион1";5;"2015-12-10 15:38:00";"";0
2;"Аукцион2";1;1;"Аукцион2";5;"2015-12-10 15:38:00";"";1
3;"Аукцион3";1;1;"Аукцион3";5;"2015-12-10 15:38:00";"";1
4;"Аукцион4";1;1;"Аукцион4";7;"2015-12-10 15:38:00";"";3
5;"Аукцион5";1;1;"Аукцион5";5;"2015-12-10 15:38:00";"";4
6;"Аукцион6";1;1;"Аукцион6";6;"2015-12-10 15:38:00";"";5
7;"Аукцион7";-1;1;"Аукцион7";6;"2015-12-10 15:38:00";"";6
8;"Аукцион7";1;1;"Аукцион7";7;"2015-12-10 15:38:00";"";7

3) the make the:
UPDATE "auction" SET "type"=1, "client_id"=1, "dt_start"='2015-12-10
15:38:00', "status"=-1 WHERE "id"=0; //data in the raw was not changed bat
the (SELECT * FROM auction;) give me the next order of rows:

1;"Аукцион1";-1;1;"Аукцион1";5;"2015-12-10 15:38:00";"";0
2;"Аукцион2";1;1;"Аукцион2";5;"2015-12-10 15:38:00";"";1
3;"Аукцион3";1;1;"Аукцион3";5;"2015-12-10 15:38:00";"";1
4;"Аукцион4";1;1;"Аукцион4";7;"2015-12-10 15:38:00";"";3
5;"Аукцион5";1;1;"Аукцион5";5;"2015-12-10 15:38:00";"";4
6;"Аукцион6";1;1;"Аукцион6";6;"2015-12-10 15:38:00";"";5
7;"Аукцион7";-1;1;"Аукцион7";6;"2015-12-10 15:38:00";"";6
8;"Аукцион7";1;1;"Аукцион7";7;"2015-12-10 15:38:00";"";7
0;"Аукцион1";1;1;"Аукцион0";5;"2015-12-10 15:38:00";"";-1


4) There is no any orders by or any changes in sequences. Why do that???

Re: BUG #13811: Default ordering colums

From
John R Pierce
Date:
On 12/10/2015 4:52 AM, exru@mail.ru wrote:
> 4) There is no any orders by or any changes in sequences. Why do that???

tables represent sets, unordered lists.  if you want query results in a
specific order, use an ORDER BY clause on your SELECT



--
john r pierce, recycling bits in santa cruz

Re: BUG #13811: Default ordering colums

From
"David G. Johnston"
Date:
T24gVGh1LCBEZWMgMTAsIDIwMTUgYXQgNTo1MiBBTSwgPGV4cnVAbWFpbC5ydT4gd3JvdGU6DQoN
Cj4gVGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOg0KPg0K
PiBCdWcgcmVmZXJlbmNlOiAgICAgIDEzODExDQo+IExvZ2dlZCBieTogICAgICAgICAgVnlhY2hl
c2xhdg0KPiBFbWFpbCBhZGRyZXNzOiAgICAgIGV4cnVAbWFpbC5ydQ0KPiBQb3N0Z3JlU1FMIHZl
cnNpb246IDkuNC41DQo+IE9wZXJhdGluZyBzeXN0ZW06ICAgRGViaWFuIDguMQ0KPiBEZXNjcmlw
dGlvbjoNCj4NCj4NCj4gMDsi0JDRg9C60YbQuNC+0L0xIjsxOzE7ItCQ0YPQutGG0LjQvtC9MCI7
NTsiMjAxNS0xMi0xMCAxNTozODowMCI7IiI7LTENCj4gMTsi0JDRg9C60YbQuNC+0L0xIjstMTsx
OyLQkNGD0LrRhtC40L7QvTEiOzU7IjIwMTUtMTItMTAgMTU6Mzg6MDAiOyIiOzANCj4gMjsi0JDR
g9C60YbQuNC+0L0yIjsxOzE7ItCQ0YPQutGG0LjQvtC9MiI7NTsiMjAxNS0xMi0xMCAxNTozODow
MCI7IiI7MQ0KPiAzOyLQkNGD0LrRhtC40L7QvTMiOzE7MTsi0JDRg9C60YbQuNC+0L0zIjs1OyIy
MDE1LTEyLTEwIDE1OjM4OjAwIjsiIjsxDQo+IDQ7ItCQ0YPQutGG0LjQvtC9NCI7MTsxOyLQkNGD
0LrRhtC40L7QvTQiOzc7IjIwMTUtMTItMTAgMTU6Mzg6MDAiOyIiOzMNCj4gNTsi0JDRg9C60YbQ
uNC+0L01IjsxOzE7ItCQ0YPQutGG0LjQvtC9NSI7NTsiMjAxNS0xMi0xMCAxNTozODowMCI7IiI7
NA0KPiA2OyLQkNGD0LrRhtC40L7QvTYiOzE7MTsi0JDRg9C60YbQuNC+0L02Ijs2OyIyMDE1LTEy
LTEwIDE1OjM4OjAwIjsiIjs1DQo+IDc7ItCQ0YPQutGG0LjQvtC9NyI7LTE7MTsi0JDRg9C60YbQ
uNC+0L03Ijs2OyIyMDE1LTEyLTEwIDE1OjM4OjAwIjsiIjs2DQo+IDg7ItCQ0YPQutGG0LjQvtC9
NyI7MTsxOyLQkNGD0LrRhtC40L7QvTciOzc7IjIwMTUtMTItMTAgMTU6Mzg6MDAiOyIiOzcNCj4N
Cg0KDQo+IOKAizxkbyBzdHVmZiBoZXJlPg0KPg0KDQoNCj4NCj4gMTsi0JDRg9C60YbQuNC+0L0x
IjstMTsxOyLQkNGD0LrRhtC40L7QvTEiOzU7IjIwMTUtMTItMTAgMTU6Mzg6MDAiOyIiOzANCj4g
Mjsi0JDRg9C60YbQuNC+0L0yIjsxOzE7ItCQ0YPQutGG0LjQvtC9MiI7NTsiMjAxNS0xMi0xMCAx
NTozODowMCI7IiI7MQ0KPiAzOyLQkNGD0LrRhtC40L7QvTMiOzE7MTsi0JDRg9C60YbQuNC+0L0z
Ijs1OyIyMDE1LTEyLTEwIDE1OjM4OjAwIjsiIjsxDQo+IDQ7ItCQ0YPQutGG0LjQvtC9NCI7MTsx
OyLQkNGD0LrRhtC40L7QvTQiOzc7IjIwMTUtMTItMTAgMTU6Mzg6MDAiOyIiOzMNCj4gNTsi0JDR
g9C60YbQuNC+0L01IjsxOzE7ItCQ0YPQutGG0LjQvtC9NSI7NTsiMjAxNS0xMi0xMCAxNTozODow
MCI7IiI7NA0KPiA2OyLQkNGD0LrRhtC40L7QvTYiOzE7MTsi0JDRg9C60YbQuNC+0L02Ijs2OyIy
MDE1LTEyLTEwIDE1OjM4OjAwIjsiIjs1DQo+IDc7ItCQ0YPQutGG0LjQvtC9NyI7LTE7MTsi0JDR
g9C60YbQuNC+0L03Ijs2OyIyMDE1LTEyLTEwIDE1OjM4OjAwIjsiIjs2DQo+IDg7ItCQ0YPQutGG
0LjQvtC9NyI7MTsxOyLQkNGD0LrRhtC40L7QvTciOzc7IjIwMTUtMTItMTAgMTU6Mzg6MDAiOyIi
OzcNCj4gMDsi0JDRg9C60YbQuNC+0L0xIjsxOzE7ItCQ0YPQutGG0LjQvtC9MCI7NTsiMjAxNS0x
Mi0xMCAxNTozODowMCI7IiI7LTENCj4NCj4NCj4gNCkgVGhlcmUgaXMgbm8gYW55IG9yZGVycyBi
eSBvciBhbnkgY2hhbmdlcyBpbiBzZXF1ZW5jZXMuIFdoeSBkbyB0aGF0Pw0KPg0KDQrigItXaHkg
cXVlc3Rpb25zIGFyZSBub3QgdHlwaWNhbGx5IGJ1Z3MsIGFuZCB0aGlzIG9uZSBkZWZpbml0ZWx5
IGlzIG5vdCwgYnV0DQphbnl3YXkuLi4NCg0KUTog4oCLV2h5IGRvZXMgdGhlIG9yZGVyIGNoYW5n
ZT/igIsNCkE6IEJlY2F1c2UgdGhlIGV4ZWN1dG9yIHdhc24ndCB0b2xkIHRvIGNhcmUgYWJvdXQg
b3JkZXIgaW4gdGhlIHF1ZXJ5IGFuZA0KdGhpcyBpcyB3aGF0IGNhbWUgdGhyb3VnaCBuYXR1cmFs
bHkuDQoNCkFsc28sIHVwZGF0aW5nIGEgcm93LCBldmVuIGlmIHlvdSBkbyBub3QgY2hhbmdlIHRo
ZSBhY3R1YWwgdmFsdWVzIG9mIHRoZQ0KYW55IG9mIGNvbHVtbnMsIHN0aWxsIGNhdXNlcyB0aGUg
c3lzdGVtIHRvIHBlcmZvcm0gYSBkZWxldGUvaW5zZXJ0IHBhaXIgKGluDQpNVkNDIHRlcm1zKS4g
IFRoZSByb3cgd2l0aCBJRCAwIGluIHRoZSBmaXJzdCByZXN1bHQgaXMgcGh5c2ljYWxseSBkaWZm
ZXJlbnQNCnRoYW4gdGhlIG9uZSByZXR1cm5lZCBpbiB0aGUgc2Vjb25kIHJlc3VsdC4NCg0KRGF2
aWQgSi4NCg==