Обсуждение: BUG #14188: "FOR SELECT DISTINCT, ORDER BY expressions must appear IN SELECT list" error and table alias
BUG #14188: "FOR SELECT DISTINCT, ORDER BY expressions must appear IN SELECT list" error and table alias
От
gabrimonfa@gmail.com
Дата:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE4OApMb2dnZWQgYnk6ICAg ICAgICAgIEdhYnJpZWxlIE1vbmZhcmRpbmkKRW1haWwgYWRkcmVzczogICAg ICBnYWJyaW1vbmZhQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDku NS4zCk9wZXJhdGluZyBzeXN0ZW06ICAgRGViaWFuCkRlc2NyaXB0aW9uOiAg ICAgICAgCgpDUkVBVEUgVEFCTEUgdGFibGUxIChpZCBpbnRlZ2VyIHByaW1h cnkga2V5LCBuYW1lIHZhcmNoYXIpOw0KQ1JFQVRFIFRBQkxFIHRhYmxlMiAo aWQgaW50ZWdlciBwcmltYXJ5IGtleSwgaG9tZSB2YXJjaGFyKTsNCg0KU0VM RUNUIERJU1RJTkNUIG5hbWUgRlJPTSB0YWJsZTEgdDEgSU5ORVIgSk9JTiB0 YWJsZTIgdDIgT04gKHQxLmlkID0gdDIuaWQpCk9SREVSIEJZIHQxLm5hbWU7 DQpFUlJPUjogIGZvciBTRUxFQ1QgRElTVElOQ1QsIE9SREVSIEJZIGV4cHJl c3Npb25zIG11c3QgYXBwZWFyIGluIHNlbGVjdApsaXN0DQpSSUdBIDE6IC4u LnQxIElOTkVSIEpPSU4gdGFibGUyIHQyIE9OICh0MS5pZCA9IHQyLmlkKSBP UkRFUiBCWSB0MS5uYW1lOw0KDQpTRUxFQ1QgRElTVElOQ1QgbmFtZSBGUk9N IHRhYmxlMSB0MSBJTk5FUiBKT0lOIHRhYmxlMiB0MiBPTiAodDEuaWQgPSB0 Mi5pZCkKT1JERVIgQlkgbmFtZTsNCiBuYW1lIA0KLS0tLS0tDQooMCByaWdo ZSkNCg0KU3VyZSBuYW1lIG1heSBiZSBxdWFsaWZpZWQgaW4gU0VMRUNUIGxp c3QgYnV0IGl0IGlzIG5vdCBhbWJpZ3VvdXMuDQoNCk90aGVyIGV4YW1wbGU6 DQpDUkVBVEUgVEFCTEUgdGFibGUxIChpZCBJTlRFR0VSLCBOQU1FIFZBUkNI QVIpOw0KQ1JFQVRFIFRBQkxFIHRhYmxlMiAoaWQgSU5URUdFUiwgaG9tZSBW QVJDSEFSKTsNCiANClNFTEVDVCBESVNUSU5DVCAqIEZST00gdGFibGUxIHQx IElOTkVSIEpPSU4gdGFibGUyIHQyIE9OICh0MS5pZCA9IHQyLmlkKQpPUkRF UiBCWSBOQU1FOw0KIGlkIHwgTkFNRSB8IGlkIHwgaG9tZQ0KLS0tLSstLS0t LS0rLS0tLSstLS0tLS0NCigwIHJpZ2hlKQ0KIA0KU0VMRUNUIERJU1RJTkNU ICogRlJPTSB0YWJsZTEgdDEgSU5ORVIgSk9JTiB0YWJsZTIgdDIgT04gKHQx LmlkID0gdDIuaWQpCk9SREVSIEJZIHQxLk5BTUU7DQpFUlJPUjogIEZPUiBT RUxFQ1QgRElTVElOQ1QsIE9SREVSIEJZIGV4cHJlc3Npb25zIG11c3QgYXBw ZWFyIElOIFNFTEVDVApsaXN0DQpSSUdBIDE6IC4uLnQxIElOTkVSIEpPSU4g dGFibGUyIHQyIE9OICh0MS5pZCA9IHQyLmlkKSBPUkRFUiBCWSB0MS5OQU1F Ow0KIA0KU0VMRUNUIERJU1RJTkNUIHQxLiosdDIuKiBGUk9NIHRhYmxlMSB0 MSBJTk5FUiBKT0lOIHRhYmxlMiB0MiBPTiAodDEuaWQgPQp0Mi5pZCkgT1JE RVIgQlkgdDEuTkFNRTsNCiBpZCB8IE5BTUUgfCBpZCB8IGhvbWUNCi0tLS0r LS0tLS0tKy0tLS0rLS0tLS0tDQooMCByaWdoZSkNCg0KDQpTdXJlbHkgcXVl cnkgbWF5IGJlIG1hZGUgc21hcnRlciBhbHdheXMgcXVhbGlmeWluZyBhdHRy aWJ1dGVzIGluIFNFTEVDVCBsaXN0CmJ1dCBJIHRoaW5rIGl0IGlzIG1heSBi ZSBjb25zaWRlcmVkIHZhbGlkIFNRTC4NCg0KT3IgaXQgaXMgdG8gYmUgY29u c2lkZXJlZCBpbnZhbGlkIHNpbmNlICJuYW1lIiB3aXRob3V0IHF1YWxpZmlj YXRpb24gaW4KU0VMRUNUIGlzIGNvbnNpZGVyZWQgbm90IGFuIGF0dHJpYnV0 ZSBvZiB0MSBidXQgYW4gYXR0cmlidXRlIG9mICIodDEgam9pbgp0MikiIGFu ZCB0aHVzIHQxLm5hbWUgaXMgbm90IGluIHNlbGVjdCBsaXN0Pw0KCgo=
gabrimonfa@gmail.com writes:
> CREATE TABLE table1 (id integer primary key, name varchar);
> CREATE TABLE table2 (id integer primary key, home varchar);
> SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
> ORDER BY t1.name;
> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
> SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
> ORDER BY name;
> [ok]
The reason for the discrepancy is that "t1.name" refers to an output
column of t1, while "name" refers to an output column of the unnamed JOIN.
While those are semantically equivalent in this particular case, they are
not so in general --- in particular, had this been a FULL JOIN, they
would definitely not be equivalent. PG's parser treats them as different
variables and therefore sees "ORDER BY t1.name" as unrelated to the value
being distinct'ed on.
We might someday try to make the parser smarter about recognizing such
equivalences earlier, but I'm not terribly excited about it.
regards, tom lane
Re: BUG #14188: "FOR SELECT DISTINCT, ORDER BY expressions must appear IN SELECT list" error and table alias
От
Gabriele Monfardini
Дата:
On Tue, Jun 14, 2016 at 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > gabrimonfa@gmail.com writes: > > CREATE TABLE table1 (id integer primary key, name varchar); > > CREATE TABLE table2 (id integer primary key, home varchar); > > > SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = > t2.id) > > ORDER BY t1.name; > > ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select > list > > SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = > t2.id) > > ORDER BY name; > > [ok] > > The reason for the discrepancy is that "t1.name" refers to an output > column of t1, while "name" refers to an output column of the unnamed JOIN. > While those are semantically equivalent in this particular case, they are > not so in general --- in particular, had this been a FULL JOIN, they > would definitely not be equivalent. PG's parser treats them as different > variables and therefore sees "ORDER BY t1.name" as unrelated to the value > being distinct'ed on. > > We might someday try to make the parser smarter about recognizing such > equivalences earlier, but I'm not terribly excited about it. > yes, it would probably not worth the effort. Thank you for the explanation. Best regards, Gabriele Monfardini