[ebean] Ambigous ID

[florin.g]

version 2.5.1

I am not a day to day programmer so I don't really know how to help
more than this.

This issue comes up on a self referencing table. I have one object,
Campaign.java, with a property 'predecessor' as a ManyToOne persistent
relationship. When I display the the Campaign.java in a web page form,
it works. The wired part is that when I return to the page in less
than 5 minutes (session not expired), I get the following error. In
the web page, the campaign is calling its predecessor resulting in a
join: campaign.getPredecessor().getName(). I tried to look into the
eBean source yet it's too much for me.

If I can get help, fine, if not I'll remove the persistence ManyToOne
relationship and do a manual .find() on the foreign key. I only post
this in case it is an issue that can be a trouble for others.



Thanks.


[10-04-20 09:17:59.923] {http://*:80-4} WebApp[http://default]
javax.persistence.PersistenceException: Query threw
SQLException:Ambiguous column name "ID"; SQL statement:
[10-04-20 09:17:59.923] {http://*:80-4} select c.id c0, c.status c1,
c.user_target c2, c.ended c3, c.suspended c4, c.c_type c5, c.title c6,
c.stopped c7, c.start c8, c.created c9, c.description c10, c.priority
c11, c.name c12, c.started c13, c.resumed c14, c.notes c15, c.end c16
[10-04-20 09:17:59.923] {http://*:80-4} , cp.id c17, cp.title
c18
[10-04-20 09:17:59.923] {http://*:80-4} from campaign c
[10-04-20 09:17:59.923] {http://*:80-4} left outer join campaign cp on
cp.id = c.predecessor_id
[10-04-20 09:17:59.923] {http://*:80-4} where Id = ? [90059-131]
Query was:
[10-04-20 09:17:59.923] {http://*:80-4} select c.id c0, c.status c1,
c.user_target c2, c.ended c3, c.suspended c4, c.c_type c5, c.title c6,
c.stopped c7, c.start c8, c.created c9, c.description c10, c.priority
c11, c.name c12, c.started c13, c.resumed c14, c.notes c15, c.end c16
[10-04-20 09:17:59.923] {http://*:80-4} , cp.id c17, cp.title
c18
[10-04-20 09:17:59.923] {http://*:80-4} from campaign c
[10-04-20 09:17:59.923] {http://*:80-4} left outer join campaign cp on
cp.id = c.predecessor_id
[10-04-20 09:17:59.923] {http://*:80-4} where Id = ?
[10-04-20 09:17:59.923] {http://*:80-4}
[10-04-20 09:17:59.923] {http://*:80-4}
[10-04-20 09:17:59.923] {http://*:80-4}
javax.persistence.PersistenceException: Query threw
SQLException:Ambiguous column name "ID"; SQL statement:
[10-04-20 09:17:59.923] {http://*:80-4} select c.id c0, c.status c1,
c.user_target c2, c.ended c3, c.suspended c4, c.c_type c5, c.title c6,
c.stopped c7, c.start c8, c.created c9, c.description c10, c.priority
c11, c.name c12, c.started c13, c.resumed c14, c.notes c15, c.end c16
[10-04-20 09:17:59.923] {http://*:80-4} , cp.id c17, cp.title
c18
[10-04-20 09:17:59.923] {http://*:80-4} from campaign c
[10-04-20 09:17:59.923] {http://*:80-4} left outer join campaign cp on
cp.id = c.predecessor_id
[10-04-20 09:17:59.923] {http://*:80-4} where Id = ? [90059-131]
Query was:
[10-04-20 09:17:59.923] {http://*:80-4} select c.id c0, c.status c1,
c.user_target c2, c.ended c3, c.suspended c4, c.c_type c5, c.title c6,
c.stopped c7, c.start c8, c.created c9, c.description c10, c.priority
c11, c.name c12, c.started c13, c.resumed c14, c.notes c15, c.end c16
[10-04-20 09:17:59.923] {http://*:80-4} , cp.id c17, cp.title
c18
[10-04-20 09:17:59.923] {http://*:80-4} from campaign c
[10-04-20 09:17:59.923] {http://*:80-4} left outer join campaign cp on
cp.id = c.predecessor_id
[10-04-20 09:17:59.923] {http://*:80-4} where Id = ?
[10-04-20 09:17:59.923] {http://*:80-4}
[10-04-20 09:17:59.923] {http://*:80-4}
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:
203)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:
89)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:
282)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:
1358)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.core.DefaultServer.findUnique(DefaultServer.java:
1154)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.querydefn.DefaultOrmQuery.findUnique(DefaultOrmQuery.java:
818)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.util.DefaultExpressionList.findUnique(DefaultExpressionList.java:
158)
[10-04-20 09:17:59.923] {http://*:80-4} at
template.page.admin.nletters.EditorCampaignPage.onInit(EditorCampaignPage.java:
26)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.apache.click.ClickServlet.performOnInit(ClickServlet.java:571)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.apache.click.ClickServlet.processPage(ClickServlet.java:531)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.apache.click.ClickServlet.handleRequest(ClickServlet.java:363)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.apache.click.ClickServlet.doGet(ClickServlet.java:266)
[10-04-20 09:17:59.923] {http://*:80-4} at
javax.servlet.http.HttpServlet.service(HttpServlet.java:119)
[10-04-20 09:17:59.923] {http://*:80-4} at
javax.servlet.http.HttpServlet.service(HttpServlet.java:96)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:
109)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.apache.click.extras.filter.PerformanceFilter.doFilter(PerformanceFilter.java:
354)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.dispatch.FilterFilterChain.doFilter(FilterFilterChain.java:
88)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.security.SecurityFilterChain.doFilter(SecurityFilterChain.java:
131)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.webapp.WebAppFilterChain.doFilter(WebAppFilterChain.java:
183)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.webapp.AccessLogFilterChain.doFilter(AccessLogFilterChain.java:
103)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.dispatch.ServletInvocation.service(ServletInvocation.java:
286)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:780)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.connection.TcpConnection.dispatchRequest(TcpConnection.java:
600)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.connection.TcpConnection.handleRequestsImpl(TcpConnection.java:
566)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.connection.TcpConnection.handleRequests(TcpConnection.java:
519)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.connection.TcpConnection
$AcceptTask.doTask(TcpConnection.java:1100)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.connection.TcpConnection
$ConnectionReadTask.runThread(TcpConnection.java:1037)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.caucho.server.connection.TcpConnection
$AcceptTask.run(TcpConnection.java:1068)
[10-04-20 09:17:59.923] {http://*:80-4} at com.caucho.util.ThreadPool
$PoolThread.runTasks(ThreadPool.java:901)
[10-04-20 09:17:59.923] {http://*:80-4} at com.caucho.util.ThreadPool
$PoolThread.run(ThreadPool.java:866)
[10-04-20 09:17:59.923] {http://*:80-4} Caused by:
org.h2.jdbc.JdbcSQLException: Ambiguous column name "ID"; SQL
statement:
[10-04-20 09:17:59.923] {http://*:80-4} select c.id c0, c.status c1,
c.user_target c2, c.ended c3, c.suspended c4, c.c_type c5, c.title c6,
c.stopped c7, c.start c8, c.created c9, c.description c10, c.priority
c11, c.name c12, c.started c13, c.resumed c14, c.notes c15, c.end c16
[10-04-20 09:17:59.923] {http://*:80-4} , cp.id c17, cp.title
c18
[10-04-20 09:17:59.923] {http://*:80-4} from campaign c
[10-04-20 09:17:59.923] {http://*:80-4} left outer join campaign cp on
cp.id = c.predecessor_id
[10-04-20 09:17:59.923] {http://*:80-4} where Id = ? [90059-131]
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.message.DbException.get(DbException.java:167)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.message.DbException.get(DbException.java:144)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.expression.ExpressionColumn.mapColumn(ExpressionColumn.java:
104)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.expression.ExpressionColumn.mapColumns(ExpressionColumn.java:
81)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.expression.Comparison.mapColumns(Comparison.java:426)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.dml.Select.init(Select.java:689)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.Parser.parseSelect(Parser.java:1363)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.Parser.parsePrepared(Parser.java:396)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.Parser.parse(Parser.java:279)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.Parser.parse(Parser.java:251)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.Parser.prepareCommand(Parser.java:223)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.engine.Session.prepareLocal(Session.java:420)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.server.TcpServerThread.process(TcpServerThread.java:224)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.server.TcpServerThread.run(TcpServerThread.java:135)
[10-04-20 09:17:59.923] {http://*:80-4} at
java.lang.Thread.run(Unknown Source)
[10-04-20 09:17:59.923] {http://*:80-4}
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.engine.SessionRemote.done(SessionRemote.java:505)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.CommandRemote.prepare(CommandRemote.java:70)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.command.CommandRemote.<init>(CommandRemote.java:48)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:401)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1070)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:
71)
[10-04-20 09:17:59.923] {http://*:80-4} at
org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.lib.sql.PooledConnection.prepareStatement(PooledConnection.java:
393)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.lib.sql.PooledConnection.prepareStatement(PooledConnection.java:
363)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:
397)
[10-04-20 09:17:59.923] {http://*:80-4} at
com.avaje.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:
161)


--
Subscription settings: http://groups.google.com/group/ebean/subscribe?hl=en

[Rob Bygrave]

I believe the issue here is that the SQL contains .... "where Id = ?"
... and I'd probably have expected it to be "where c.id = ?" instead.


Note the "c" prefix is the "table alias" for the campaign table ....  "from campaign c"

This could be a case of the Uppercase "I" in "Id" should really be "id".

Cheers, Rob.

[florin.g]

thanks

On Apr 20, 5:32 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> I believe the issue here is that the SQL contains .... "where Id = ?"
> ... and I'd probably have expected it to be "where c.id = ?" instead.
>
> Note the "c" prefix is the "table alias" for the campaign table ....  "from
> campaign c"
>
> This could be a case of the Uppercase "I" in "Id" should really be "id".
>
> Cheers, Rob.
>

> ...
>
> read more »