1. Topics
  2. Ambiguous column names in subquery

Ambiguous column names in subquery

mayank.chawla@...
 

Hello,
 
As part of migration from 2.x to 5.x, we are facing a error because the select statement is having ambiguous column name. Same code was working with previous version, looks like we have to change few things. 
 
Below is the query example-
 
  SELECT subq.PK_X,subq.xxx,subq.xxx,subq.xxx,subq.PK_X,subq.JDOLOCKX,subq.xxx,subq.JDOLOCKX,subq.xxx FROM (SELECT A0.PK_X,B1.xxx,B1.xxx,B1.xxx,B0.PK_X,B1.JDOLOCKX,B1.xxx,A0.JDOLOCKX,A0.xxx,row_number()over() rn FROM DB.APP A0 LEFT OUTER JOIN DB.J2EE B0 ON A0.PK__PARENTJ2EE_X = B0.PK_X LEFT OUTER JOIN DB."COLL" B1 ON B0.PK_X = B1.PK_X WHERE ((A0.xxx = 'weblogic.WebLogicServer' OR A0.xxx = 'weblogic.WebLogicAdminServer')) AND A0.VERSION_X = 0 AND A0.DELETED_X = 0) subq WHERE subq.rn<=1000
 
Here, JDOLOCKX is being referenced from subquery from two different tables A0 and B1.
 
We are creating Query using extent, further adding FetchPlan, declareVariables. 
 
By bit searching, I feel below two could be the probable cause/ fix,
1. I can define declareImports of Query object.
2. From migration doc- RDBMS : where you have a query that has a collection member in the FetchPlan it previously would have been ignored. Now it is used to attempt a bulk-fetch of the collection. Since this is new functionality there may be cases where the syntax is not optimal; remove the collection field from the query FetchPlan to get the previous behaviour.
 
Could you please advice how to fix this issue. I hope I have provided sufficient information, please let me know if anything else needs to be shared.
 
Thanks.
Andy
 
Edited

You post some SQL but don't say how that was executed ... an SQL query? If that SQL is "generated" from JDOQL/JPQL then visibility of the origin of it (aka JDOQL/JPQL and the classes) is sort of crucial since, without it, it would be impossible to say if anything is generated "incorrectly" or not. There was an error, but no visibility of WHERE that came from (aka a stack trace).
Only you know what "JDOLOCKX" is, or "PK_X" for that matter. Some database is in use but who knows which one?

What happened in "2.x" is of no interest, that was many years ago with a very different codebase. Was that before the JDOQL generation process was rewritten?
mayank.chawla@...
 
Edited

Hello Andy,
 
Please find below my response,
You post some SQL but don't say how that was executed ... an SQL query? - Yes, it executes as a SQL query.
 
It is a JDOQL query, we create a query object and set filter using below code,
 
final Query query = persistenceManager.newQuery
    (
      persistenceManager.getExtent( JDO, subclasses)
    );
    final StringBuilder buffer = StringX.value( null, filters);
    if( null != buffer )
      query.setFilter
      (
        buffer.toString()
      );
In query object we get - 
"SELECT FROM ConfigurationManagement WHERE lastModifiedTime_ > 1608360447673 && version_ == 0 && deleted_ == false RANGE 0,1000"
 
Class is ConfigurationManagement
 
Then we have following value of FetchGroups,   
 
[FetchGroup : 200 for ConfigurationManagement members=guid_, pk_, masterNode_, locationTag_, nodes_, modifiable=true, postLoad=false, FetchGroup : 201 for ComputerSystem members=ipInterfaces_, signature_, pk_, modifiable=true, postLoad=false, FetchGroup : 202 for ConfigurationManagementNode members=realizedOS_, guid_, pk_, parentConfigurationManagementNode_, locationTag_, name_, modifiable=true, postLoad=false, FetchGroup : 115 for IpInterface members=ipAddress_, pk_, modifiable=true, postLoad=false, FetchGroup : 203 for OperatingSystem members=pk_, parentOperatingSystem_, modifiable=true, postLoad=false, FetchGroup : 64 for IpAddress members=pk_, stringNotation_, modifiable=true, postLoad=false]
 
Query in error: 
SELECT subq.GUID_X,subq.LOCATIONTAG_X,subq.PK_X,subq.GUID_X,subq.LOCATIONTAG_X,subq.NAME_X,subq.PK_X,subq.PK__PARENTCONFIGNODE_7C872E69X,subq.PK__REALIZEDOS_X,subq.JDOLOCKX,subq.JDOCLASSX,subq.JDOLOCKX,subq.JDOCLASSX FROM (SELECT A0.GUID_X,A0.LOCATIONTAG_X,A0.PK_X,B1.GUID_X,B1.LOCATIONTAG_X,B1.NAME_X,B0.PK_X,B0.PK__PARENTCONFIGNODE_7C872E69X,B0.PK__REALIZEDOS_X,B1.JDOLOCKX,B1.JDOCLASSX,A0.JDOLOCKX,A0.JDOCLASSX,row_number()over() rn FROM DB2INST1."COLLECTION" A0 LEFT OUTER JOIN DB2INST1.CMNODE B0 ON A0.PK__MASTERNODE_X = B0.PK_X LEFT OUTER JOIN DB2INST1."FUNCTION" B1 ON B0.PK_X = B1.PK_X WHERE A0.JDOCLASSX = 'ConfigurationManagement' AND A0.LASTMODIFIEDTIME_X > 1606811235834 AND A0.VERSION_X = 0 AND A0.DELETED_X = 0) subq WHERE subq.rn<=1000
 
 
StackTrace:
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-203, SQLSTATE=42702, SQLERRMC=SUBQ.GUID_X, DRIVER=3.64.106
at com.ibm.db2.jcc.am.bd.a(bd.java:679)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:127)
at com.ibm.db2.jcc.am.io.c(io.java:2706)
at com.ibm.db2.jcc.am.io.d(io.java:2694)
at com.ibm.db2.jcc.am.io.a(io.java:2143)
at com.ibm.db2.jcc.am.jo.a(jo.java:7353)
at com.ibm.db2.jcc.t4.ab.h(ab.java:141)
at com.ibm.db2.jcc.t4.ab.b(ab.java:41)
at com.ibm.db2.jcc.t4.p.a(p.java:32)
at com.ibm.db2.jcc.t4.qb.i(qb.java:135)
at com.ibm.db2.jcc.am.io.gb(io.java:2112)
at com.ibm.db2.jcc.am.jo.rc(jo.java:3526)
at com.ibm.db2.jcc.am.jo.xc(jo.java:3658)
at com.ibm.db2.jcc.am.jo.a(jo.java:588)
at com.ibm.db2.jcc.am.Connection.prepareStatementX(Connection.java:2355)
at com.ibm.db2.jcc.am.Connection.prepareStatementX(Connection.java:2301)
at com.ibm.db2.jcc.am.Connection.prepareStatement(Connection.java:1848)
at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:318)
at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:318)
at org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:349)
at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:224)
at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:629)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1973)
at org.datanucleus.store.query.Query.executeWithArray(Query.java:1862)
at org.datanucleus.store.query.Query.execute(Query.java:1844)
at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:439)
at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:263)
at xxx.CloseAll.execute
 
We are using DB2 database. Please let me know if any further info is required.
 
 
Thanks.
 
1 - 3 of 3