Home » Forum » Database specific

Please use the google group to ask questions - thanks.

by Claudio 06 May 11:12
Oracle column alias missing with 1.1.0

Hi Rob, I am migrating an Oracle based project from 1.0.3 to 1.1.0 and found a problem:

the query with 1.0.3 was:

select * from (
select row_number() over (order by p.data_erogazione desc) as rn, p.id c0, p.data_erogazione c1
, t.id c2, t.descrizione c3
, b.id c4, b.cognome c5, b.nome c6
from CISE_PRESTAZIONE p
join CISE_TIPO_PRESTAZIONE t on p.ID_TIPO = t.ID
join PERSONE b on p.ID_BENEFICIARIO = b.ID
order by p.data_erogazione desc
) where rn <= 101

with 1.1.0 the query has no column alias:

select * from (
select row_number() over (order by p.data_erogazione desc) as rn, p.id, p.data_erogazione
, t.id, t.descrizione
, b.id, b.cognome, b.nome
from CISE_PRESTAZIONE p
join CISE_TIPO_PRESTAZIONE t on p.ID_TIPO = t.ID
join PERSONE b on p.ID_BENEFICIARIO = b.ID
order by p.data_erogazione desc
) where rn <= 101


The log:

INFO: Ebean Version[1.1.0-090330] Java Version[1.6.0_10]
...
...
Exception in thread "AWT-EventQueue-0" javax.persistence.PersistenceException: Query threw SQLException:ORA-00918: column ambiguously defined
Query was:
select * from (
select row_number() over (order by p.data_erogazione desc) as rn, p.id, p.data_erogazione
, t.id, t.descrizione
, b.id, b.cognome, b.nome
from CISE_PRESTAZIONE p
join CISE_TIPO_PRESTAZIONE t on p.ID_TIPO = t.ID
join PERSONE b on p.ID_BENEFICIARIO = b.ID
order by p.data_erogazione desc
) where rn <= 101


at com.avaje.ebean.server.query.CQueryEngine.findMany(CQueryEngine.java:110)
at com.avaje.ebean.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:93)
at com.avaje.ebean.server.core.QueryRequest.findList(QueryRequest.java:207)
at com.avaje.ebean.server.core.DefaultServer.findList(DefaultServer.java:1022)
at com.avaje.ebean.query.DefaultOrmQuery.findList(DefaultOrmQuery.java:490)
at verificheise.EbeanPersistenceDelegate.select(EbeanPersistenceDelegate.java:33)
at it.fc.cesena.comune.datacontrols.datasource.AbstractDatasource.load(AbstractDatasource.java:414)
at verificheise.forms.Prestazioni.init(Prestazioni.java:229)
at verificheise.SmartPanel.setVisible(SmartPanel.java:31)
at javax.swing.JTabbedPane.fireStateChanged(JTabbedPane.java:378)
at javax.swing.JTabbedPane$ModelListener.stateChanged(JTabbedPane.java:253)
at javax.swing.DefaultSingleSelectionModel.fireStateChanged(DefaultSingleSelectionModel.java:116)
at javax.swing.DefaultSingleSelectionModel.setSelectedIndex(DefaultSingleSelectionModel.java:50)
at javax.swing.JTabbedPane.setSelectedIndexImpl(JTabbedPane.java:599)
at javax.swing.JTabbedPane.setSelectedIndex(JTabbedPane.java:574)
at org.jvnet.substance.SubstanceTabbedPaneUI$MouseRolloverHandler.mousePressed(SubstanceTabbedPaneUI.java:468)
at java.awt.AWTEventMulticaster.mousePressed(AWTEventMulticaster.java:262)
at java.awt.AWTEventMulticaster.mousePressed(AWTEventMulticaster.java:262)
at java.awt.Component.processMouseEvent(Component.java:6131)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
at java.awt.Component.processEvent(Component.java:5899)
at java.awt.Container.processEvent(Container.java:2023)
at java.awt.Component.dispatchEventImpl(Component.java:4501)
at java.awt.Container.dispatchEventImpl(Container.java:2081)
at java.awt.Component.dispatchEvent(Component.java:4331)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4301)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3962)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3895)
at java.awt.Container.dispatchEventImpl(Container.java:2067)
at java.awt.Window.dispatchEventImpl(Window.java:2458)
at java.awt.Component.dispatchEvent(Component.java:4331)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:182)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:500)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:942)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:532)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1037)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2916)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2957)
at com.avaje.ebean.server.lib.sql.ExtendedPreparedStatement.executeQuery(ExtendedPreparedStatement.java:146)
at com.avaje.ebean.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:332)
at com.avaje.ebean.server.query.CQueryEngine.findMany(CQueryEngine.java:87)
... 37 more

07 May 10:09
by Rob

Put ebean.columnAliasPrefix in your ebean.properties file and that will add a column alias to every column.

## in ebean.properties
ebean.columnAliasPrefix=as c_


You can refer to this bug.
http://www.avaje.org/bugdetail-67.html

Basically I decided to make the default to only add column alias for embedded beans thinking that that was the only time when a column could be referenced twice in the sql.

07 May 10:18
by Rob

Do you know specifically which column in the SQL oracle is complaining about?

I can't see which one it is by looking at the SQL.

The main reason I don't want to by default turn on the column alias (unless I have to) is because if I take the sql and put it into a query tool it typically will then display headings of c1, c2, c3 etc... which is not as useful as seeing id, name, description etc.

07 May 10:32
by Rob

Ok, looks like the issue is with the outer select .... select * from (

Where it looks like the p.id, t.id and b.id will end up with the same name as far as Oracle is concerned (hence the 0RA-918). That is, if you take of the max 100 rows and only get the inner SQL with the row_number function then you should not see the ORA-918.

Note: This is not a solution but hopefully an explanation of why the error occurs.

Hmmm.

07 May 11:01
by Rob

SQL> ;
1 select * from (
2 select a.id, b.id
3 from my_p a
4 join my_person b on b.id = a.id
5* )
SQL> /
select * from (
*
ERROR at line 1:
ORA-00918: column ambiguously defined

Yup, the problem is with the select * from ( ....

07 May 13:38
by Rob

Logged bug http://www.avaje.org/bugdetail-111.html

07 May 13:54
by Claudio

Thanks Rob,

I had to set ebean.alwaysUseColumnAlias to true to make it work.
ebean.columnAliasPrefix=as c_ is the default so I dont'need to set it.

Am I correct?

Claudio

08 May 01:42
by Rob

Yes, you are correct.

Create a New Topic

Title:
Body:
 
Introduction User Guide (pdf) Install/Configure Public JavaDoc Whitepapers
General Database Specific Byte Code Deployment Annotations Features
Top Bugs Top Enhancements
woResponse