Hi. I have I'm connected against two instances of Oracle 11 EE. One allows "SELECT a.x FROM foo a JOIN bar b ON & ORDER BY b.y" , the other throws the expected "ORA-01791: not a SELECTed expression" Where does this difference come from?
going to crosspost to #sql
the it works on is Oracle Database 11g Release 184.108.40.206.0 - 64bit Production. The one it doesn't is Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production. Damn it, is it really the EE part (ignoring that I lied before)?
Hm, this seems to be related to the optimizer. Again, I cheated. The actual statement is more like "SELECT a.x FROM foo a JOIN foo b ON a.id = b.id ORDER BY b.id" (please don't ask, it's java hibernate in front of it).So one optimizer (non EE, 18.104.22.168.0) seems to recognize this redundancy, the other (EE, 22.214.171.124.0) doesn't?
for one: the given statement is still not close to reality. Here's the actual stmt: http://hastebin.com/oguxojiguv.sql
next: this statement was produced by Hibernate (legacy) and triggers https://hibernate.atlassian.net/browse/HHH-5926
- then http://bit.ly/1SLHCn1 (askTom) kicks in and I see the things I'm seeing
solved by #sql