Thread: BUG #14398: Order of Joins results in different results returned
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM5OApMb2dnZWQgYnk6ICAg ICAgICAgIFZpamF5IENoZW1idXJrYXIKRW1haWwgYWRkcmVzczogICAgICB2 amNoZW1AZ21haWwuY29tClBvc3RncmVTUUwgdmVyc2lvbjogOS41LjIKT3Bl cmF0aW5nIHN5c3RlbTogICBPUyBYIDEwLjExLjYKRGVzY3JpcHRpb246ICAg ICAgICAKClRoaXMgYXBwZWFycyB0byB1bHRpbWF0ZWx5IGJlIGEgY2FzdGlu ZyBpc3N1ZSwgYnV0IGl0IHdhcyBzdXJwcmlzaW5nIHRvIHVzCmFuZCB0aHVz IEkgdGhvdWdodCBJJ2QgZmlsZSBhIGJ1ZyByZXBvcnQuIEkgaGF2ZSBhIHF1 ZXJ5IHRoYXQgaGFzIHR3byBKT0lOcywKd2hlcmUgZWFjaCBKT0lOIHVzZXMg cGFyYW1ldGVycyAkMSBhbmQgJDIuIFRoZSBvcmRlciBvZiB0aGUgSk9JTiBj aGFuZ2VzIHRoZQpyZXN1bHRzIHJldHVybmVkLiBJIGNhbiBnZXQgdGhlIHJl c3VsdHMgSSBleHBlY3QgYnkgZXhwbGljaXRseSBjYXN0aW5nICQxCmFuZCAk MiBvciBieSBjaGFuZ2luZyB0aGUgb3JkZXIgb2YgdGhlIEpPSU5zLiBJcyB0 aGlzIGV4cGVjdGVkIGJlaGF2aW9yPyBJdApmZWVscyBsaWtlIEkgc2hvdWxk IGdldCBhbiBlcnJvciBpZiB0aGUgY2FzdGluZyBpcyBhbWJpZ3VvdXMgb3Ig dGhhdCB0aGUKb3JkZXIgb2YgdGhlIEpPSU5zIHNob3VsZCBub3QgY2hhbmdl IHRoZSByZXN1bHRzLg0KDQpIZXJlJ3MgYSBzaW1wbGlmaWVkIHZlcnNpb24g dGhhdCByZXByb3MgdGhpcy4gIGdldF9hcnRpY2xlc18yIHJldHVybnMgdGhl CnJlc3VsdHMgSSdkIGV4cGVjdC4gSSBjYW4gbWFrZSAgZ2V0X2FydGljbGVz XzEgcmV0dXJuIHRoZSBzYW1lIHJlc3VsdHMgaWYgSQpjYXN0IHRoZSBwYXJh bWV0ZXJzIGFzIGRhdGVzIG9yIG1ha2UgdGhlbSBkYXRlcyB3aGVuIEkgY2Fs bCBleGVjdXRlLg0KDQoNCkNSRUFURSBUQUJMRSBhcnRpY2xlKA0KICAgIGlk IFNFUklBTCBOT1QgTlVMTCBVTklRVUUsDQogICAgUFJJTUFSWSBLRVkgKGlk KQ0KKTsNCg0KQ1JFQVRFIFRBQkxFIGFydGljbGVfbWV0cmljICgNCiAgaWQg U0VSSUFMIE5PVCBOVUxMIFVOSVFVRSwNCg0KICBhcnRpY2xlX2lkIElOVEVH RVIgTk9UIE5VTEwsDQogIGRhdGFfZGF0ZSBEQVRFIE5PVCBOVUxMLA0KICBt ZXRyaWNfbmFtZSBDSEFSQUNURVIgVkFSWUlORygxMDApIE5PVCBOVUxMLA0K ICB2YWx1ZSBKU09OQiBOT1QgTlVMTCwNCiAgUFJJTUFSWSBLRVkgKGlkKSwN CiAgQ09OU1RSQUlOVCBhcnRpY2xlX21ldHJpY19rZXkgVU5JUVVFIChhcnRp Y2xlX2lkLCBkYXRhX2RhdGUsCm1ldHJpY19uYW1lKSwNCiAgQ09OU1RSQUlO VCBhcnRpY2xlX21ldHJpY19ma2V5IEZPUkVJR04gS0VZIChhcnRpY2xlX2lk KQ0KICAgIFJFRkVSRU5DRVMgYXJ0aWNsZShpZCkgT04gVVBEQVRFIENBU0NB REUgT04gREVMRVRFIENBU0NBREUNCik7DQoNCg0KSU5TRVJUIElOVE8gYXJ0 aWNsZShpZCkgVkFMVUVTKDEpLCAoMik7DQoNCklOU0VSVCBJTlRPIGFydGlj bGVfbWV0cmljKGFydGljbGVfaWQsIGRhdGFfZGF0ZSwgbWV0cmljX25hbWUs IHZhbHVlKQ0KVkFMVUVTDQooMSwnMjAxNi0wOS0yNCcsJ2FsbF92aWV3X2R1 cmF0aW9uc19hdmVyYWdlJywneyJ2YWx1ZSI6IDEwMDB9JyksDQooMSwnMjAx Ni0xMC0wMScsJ2FsbF92aWV3X2R1cmF0aW9uc19hdmVyYWdlJywneyJ2YWx1 ZSI6IDM3fScpLA0KKDEsJzIwMTYtMTAtMDgnLCdhbGxfdmlld19kdXJhdGlv bnNfYXZlcmFnZScsJ3sidmFsdWUiOiAxN30nKTsNCg0KDQpJTlNFUlQgSU5U TyBhcnRpY2xlX21ldHJpYyhhcnRpY2xlX2lkLCBkYXRhX2RhdGUsIG1ldHJp Y19uYW1lLCB2YWx1ZSkNClZBTFVFUw0KKDEsJzIwMTYtMTAtMScsJ2FsbF92 aWV3cycsICd7InZhbHVlIjoxfScpLCgxLCcyMDE2LTEwLTInLCdhbGxfdmll d3MnLAoneyJ2YWx1ZSI6Mn0nKSwoMSwnMjAxNi0xMC0zJywnYWxsX3ZpZXdz JywKJ3sidmFsdWUiOjN9JyksKDEsJzIwMTYtMTAtNCcsJ2FsbF92aWV3cycs Cid7InZhbHVlIjo0fScpLCgxLCcyMDE2LTEwLTUnLCdhbGxfdmlld3MnLAon eyJ2YWx1ZSI6NX0nKSwoMSwnMjAxNi0xMC02JywnYWxsX3ZpZXdzJywKJ3si dmFsdWUiOjZ9JyksKDEsJzIwMTYtMTAtNycsJ2FsbF92aWV3cycsCid7InZh bHVlIjo3fScpLCgxLCcyMDE2LTEwLTgnLCdhbGxfdmlld3MnLAoneyJ2YWx1 ZSI6OH0nKSwoMSwnMjAxNi0xMC05JywnYWxsX3ZpZXdzJywKJ3sidmFsdWUi Ojl9JyksKDEsJzIwMTYtMTAtMTAnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTB9JyksKDEsJzIwMTYtMTAtMTEnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTF9JyksKDEsJzIwMTYtMTAtMTInLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTJ9JyksKDEsJzIwMTYtMTAtMTMnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTN9JyksKDEsJzIwMTYtMTAtMTQnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTR9JyksKDEsJzIwMTYtMTAtMTUnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTV9JyksKDEsJzIwMTYtMTAtMTYnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTZ9JyksKDEsJzIwMTYtMTAtMTcnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTd9JyksKDEsJzIwMTYtMTAtMTgnLCdhbGxfdmlld3MnLAoneyJ2YWx1ZSI6 MTh9JyksKDEsJzIwMTYtMTAtMTknLCdhbGxfdmlld3MnLCAneyJ2YWx1ZSI6 MTl9Jyk7DQoNCklOU0VSVCBJTlRPIGFydGljbGVfbWV0cmljKGFydGljbGVf aWQsIGRhdGFfZGF0ZSwgbWV0cmljX25hbWUsIHZhbHVlKQ0KVkFMVUVTDQoo MiwnMjAxNi0xMC0wMycsJ2FsbF92aWV3X2R1cmF0aW9uc19hdmVyYWdlJywn eyJ2YWx1ZSI6IDEwMDB9JyksDQooMiwnMjAxNi0xMC0xMCcsJ2FsbF92aWV3 X2R1cmF0aW9uc19hdmVyYWdlJywneyJ2YWx1ZSI6IDExOX0nKTsNCg0KSU5T RVJUIElOVE8gYXJ0aWNsZV9tZXRyaWMoYXJ0aWNsZV9pZCwgZGF0YV9kYXRl LCBtZXRyaWNfbmFtZSwgdmFsdWUpDQpWQUxVRVMNCigyLCcyMDE2LTEwLTUn LCdhbGxfdmlld3MnLCd7InZhbHVlIjo0fScpLCgyLCcyMDE2LTEwLTYnLCdh bGxfdmlld3MnLCd7InZhbHVlIjo1fScpLCgyLCcyMDE2LTEwLTcnLCdhbGxf dmlld3MnLCd7InZhbHVlIjo2fScpLCgyLCcyMDE2LTEwLTgnLCdhbGxfdmll d3MnLCd7InZhbHVlIjo3fScpLCgyLCcyMDE2LTEwLTknLCdhbGxfdmlld3Mn LCd7InZhbHVlIjo4fScpLCgyLCcyMDE2LTEwLTEwJywnYWxsX3ZpZXdzJywn eyJ2YWx1ZSI6OX0nKSwoMiwnMjAxNi0xMC0xMScsJ2FsbF92aWV3cycsJ3si dmFsdWUiOjEwfScpLCgyLCcyMDE2LTEwLTEyJywnYWxsX3ZpZXdzJywneyJ2 YWx1ZSI6MTF9JyksKDIsJzIwMTYtMTAtMTMnLCdhbGxfdmlld3MnLCd7InZh bHVlIjoxMn0nKSwoMiwnMjAxNi0xMC0xNCcsJ2FsbF92aWV3cycsJ3sidmFs dWUiOjEzfScpLCgyLCcyMDE2LTEwLTE1JywnYWxsX3ZpZXdzJywneyJ2YWx1 ZSI6MTR9JyksKDIsJzIwMTYtMTAtMTYnLCdhbGxfdmlld3MnLCd7InZhbHVl IjoxNX0nKSwoMiwnMjAxNi0xMC0xNycsJ2FsbF92aWV3cycsJ3sidmFsdWUi OjE2fScpLCgyLCcyMDE2LTEwLTE4JywnYWxsX3ZpZXdzJywneyJ2YWx1ZSI6 MTd9JyksKDIsJzIwMTYtMTAtMTknLCdhbGxfdmlld3MnLCd7InZhbHVlIjox OH0nKSwoMiwnMjAxNi0xMC0yMCcsJ2FsbF92aWV3cycsJ3sidmFsdWUiOjE5 fScpOw0KDQoNClBSRVBBUkUgZ2V0X2FydGljbGVzXzEgQVMNClNFTEVDVA0K ICBmaWEuaWQsDQogIGFsbF92aWV3cy50b3RhbCBBUyB2aWV3c19pbl9wZXJp b2QNCkZST00NCiAgYXJ0aWNsZSBmaWENCkpPSU4gKA0KU0VMRUNUDQpkYWls eV92aWV3cy5hcnRpY2xlX2lkDQpGUk9NIChTRUxFQ1QNCm1wLmRhdGUgQVMg Z2VuZXJhdGVkX2RhdGUNCkZST00gZ2VuZXJhdGVfc2VyaWVzKCQxLCAkMiwg JzEgZGF5Jzo6aW50ZXJ2YWwpIG1wKQ0KZGF0ZXMNCkpPSU4gYXJ0aWNsZV9t ZXRyaWMgZGFpbHlfdmlld3MNCk9OIGRhaWx5X3ZpZXdzLmRhdGFfZGF0ZSA9 IGRhdGVzLmdlbmVyYXRlZF9kYXRlDQpXSEVSRQ0KZGFpbHlfdmlld3MubWV0 cmljX25hbWUgPSAnYWxsX3ZpZXdzJw0KR1JPVVAgQlkgZGFpbHlfdmlld3Mu YXJ0aWNsZV9pZCkNCmF2ZXJhZ2VfcGFydHMNCk9OIGF2ZXJhZ2VfcGFydHMu YXJ0aWNsZV9pZCA9IGZpYS5pZA0KSk9JTg0KKFNFTEVDVA0KYXJ0aWNsZV9p ZCwNClNVTSgodmFsdWUtPj4ndmFsdWUnKTo6SU5UKSBBUyB0b3RhbA0KRlJP TQ0KYXJ0aWNsZV9tZXRyaWMNCldIRVJFIG1ldHJpY19uYW1lID0gJ2FsbF92 aWV3cycNCkFORCBkYXRhX2RhdGUgQkVUV0VFTiAkMSBBTkQgJDINCkdST1VQ IEJZIGFydGljbGVfaWQpIGFsbF92aWV3cw0KT04gYWxsX3ZpZXdzLmFydGlj bGVfaWQgPSBmaWEuaWQ7DQoNClBSRVBBUkUgZ2V0X2FydGljbGVzXzIgQVMN ClNFTEVDVA0KICBmaWEuaWQsDQogIGFsbF92aWV3cy50b3RhbCBBUyB2aWV3 c19pbl9wZXJpb2QNCkZST00NCiAgYXJ0aWNsZSBmaWENCkpPSU4NCihTRUxF Q1QNCmFydGljbGVfaWQsDQpTVU0oKHZhbHVlLT4+J3ZhbHVlJyk6OklOVCkg QVMgdG90YWwNCkZST00NCmFydGljbGVfbWV0cmljDQpXSEVSRSBtZXRyaWNf bmFtZSA9ICdhbGxfdmlld3MnDQpBTkQgZGF0YV9kYXRlIEJFVFdFRU4gJDEg QU5EICQyDQpHUk9VUCBCWSBhcnRpY2xlX2lkKSBhbGxfdmlld3MNCk9OIGFs bF92aWV3cy5hcnRpY2xlX2lkID0gZmlhLmlkDQpKT0lOICgNClNFTEVDVA0K ZGFpbHlfdmlld3MuYXJ0aWNsZV9pZA0KRlJPTSAoU0VMRUNUDQptcC5kYXRl IEFTIGdlbmVyYXRlZF9kYXRlDQpGUk9NIGdlbmVyYXRlX3NlcmllcygkMSwg JDIsICcxIGRheSc6OmludGVydmFsKSBtcCkNCmRhdGVzDQpKT0lOIGFydGlj bGVfbWV0cmljIGRhaWx5X3ZpZXdzDQpPTiBkYWlseV92aWV3cy5kYXRhX2Rh dGUgPSBkYXRlcy5nZW5lcmF0ZWRfZGF0ZQ0KV0hFUkUNCmRhaWx5X3ZpZXdz Lm1ldHJpY19uYW1lID0gJ2FsbF92aWV3cycNCkdST1VQIEJZIGRhaWx5X3Zp ZXdzLmFydGljbGVfaWQpDQphdmVyYWdlX3BhcnRzDQpPTiBhdmVyYWdlX3Bh cnRzLmFydGljbGVfaWQgPSBmaWEuaWQ7DQoNCkVYRUNVVEUgZ2V0X2FydGlj bGVzXzEoJzIwMTYtMDktMjhUMDA6MDA6MDAuMDAwKzAwOjAwJywKJzIwMTYt MTAtMTZUMDA6MDA6MDAuMDAwKzAwOjAwJyk7DQpFWEVDVVRFIGdldF9hcnRp Y2xlc18yKCcyMDE2LTA5LTI4VDAwOjAwOjAwLjAwMCswMDowMCcsCicyMDE2 LTEwLTE2VDAwOjAwOjAwLjAwMCswMDowMCcpOw0KDQoKCg==
vjchem@gmail.com writes:
> This appears to ultimately be a casting issue, but it was surprising to us
> and thus I thought I'd file a bug report. I have a query that has two JOINs,
> where each JOIN uses parameters $1 and $2. The order of the JOIN changes the
> results returned. I can get the results I expect by explicitly casting $1
> and $2 or by changing the order of the JOINs. Is this expected behavior? It
> feels like I should get an error if the casting is ambiguous or that the
> order of the JOINs should not change the results.
I think the point is that you've got two separate uses of $1 and $2
in contexts that will lead to different conclusions about what their
types are (date or timestamptz, respectively). Whichever one the
parser comes to first will determine its choice, and the other context
is not so incompatible as to result in an error; nor is the provided
input string. But you'll get different results depending on that
choice.
Yes, it's ambiguous, but throwing an error would probably not make
more people happy than it makes unhappy.
regards, tom lane