Using active objects alias causes error ORA-00904 invalid identifier

[Jens Kisters __SeibertSolutions]

Hello,

on a customers system our plugin (version 0.5 to 1.1.1) and jira (?? to 7.5.0) were upgraded.

Since then the plugin started throwing exceptions when we access a Active Objects table like this:

IssueShare[] foundResult = this.activeObjects.find(IssueShare.class, Query.select()
.alias(IssueShare.class, "issueShare")
.where("issueShare.ISSUE_ID = ?", issue.getId())
.order("issueShare.ID DESC")
);

 The error message is

java.sql.SQLSyntaxErrorException: ORA-00904: "issueShare"."ID": invalid identifier

It looks to me like the alias is not resolved when the sql is built, but why?

The same piece of code worked on the old system before the update and on other systems with the same Plugin Version that was installed during the upgrade.

Two fields were added to the IssueShare table between version 0.5 and 1.1.1, thats everything that  has changed.

Thanks in Advance!

Jens

[Jens Kisters __SeibertSolutions · Accepted solution]

Changing the alias to uppercase seems to fix the problem for oracle 12.

The lowercase alias causes the same problems.

We will do some regression testing to see if this causes trouble for MySQL, PSQL or HSQL.

[Eduard M]

Hello Jens,

The ORA-00904 invalid identifier means that Oracle didn't like the column name. Since it's printed in the error as a quoted identifier, I would examine how Oracle is reporting the table/column name and possibly if it changed recently. Also you may try to trace the invalid SQL to look for anything unexpected. Hope you can find something interesting. 

[Jens Kisters __SeibertSolutions]

Hi Eduard,

no the ID column did not change.

How can the SQL be traced?

[Eduard M]

Hi Jens,

OK, what I was wondering is if Oracle's dictionary is storing the tablename in uppercase, that would clash with the quoted identifier usage in the code. You can check with DESCRIBE command in sqlplus if that seems to be the case. 'SHOW DESCRIBE' will give you the current settings and 'DESCRIBE issueShare' will give info about that table.

There are some examples of tracefiles on the net although an Oracle dba might be needed if they are not enabled by default. The example I found was pointing to an existing trace file:

SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
---------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc

SQL> host tail -10 /u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
-------------------------------------------------------------
Error information for ORA-28113:
Logon user     : SCOTT
Table/View     : CHA.EMP
Policy name    : EMP_POLICY
Policy function: CHA.EMP_RESTRICT
RLS view  :
SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "CHA"."EMP"   "EMP" WHERE ('SCOTT' = enamee)
ORA-00904: "ENAMEE": invalid identifier
-------------------------------------------------------------

 The pre-upgrade JVM version and current Oracle version and odbc driver version might be relevant info as well.

[Jens Kisters __SeibertSolutions]

Hi Eduard,

we checked the traces and found this example of a not working query:

It seems like the problem is caused by the double quotes in the order by clause.

How can we get rid of those?

The current Oracle Version is Oracle 12.1.0.2.0  und the driver filename is ojdbc7.jar

The pre-update system is not accessible any more as the machine is now running the upgraded version of Jira.

The oracle version was not changed.

best regards

Jens

[Eduard M]

Hi Jens,

The double quotes are forcing case-sensitivity, without them Oracle would not throw the error. Does the query run correctly if you change to:

ORDER BY "ISSUESHARE"."ID" DESC

If so, I would either look at updating the java code (but then you'll need to do regression testing) or checking again in the old version how that worked (I would guess an older odbc driver converted the case automatically, which won't be a workaround). 

[Jens Kisters __SeibertSolutions]

Hi Eduard, thanks for the fast reply!

we will try that.

Is there any point in raising that as an issue somewhere?

If so where?

kind regards

Jens

[Eduard M]

Hi Jens,

There isn't any resolution available other than observing the behaviour which leads to the problem. Oracle will match case-sensitivity whenever the identifiers are double-quoted, as the exact case must match the description of the objects as they are referenced in Oracle (usually all upper case). In the contrary case where quoting is not used then this is not a problem, but it looks from the code that the quoting is enforced by java as it builds the query so you have no option but to match the case exactly.

Regards,

Eduard

[Jens Kisters __SeibertSolutions]

Hi Eduard,

we tried both your suggestions:

Using 

Ojdbc7.jar.14112017 again instead of Ojdbc7.jar.28112017 did not help.

We also changed the alias in the java code to

IssueShare[] foundResult = this.activeObjects.find(IssueShare.class, Query.select()
.alias(IssueShare.class, "issueshare")
.where("issueshare.ISSUE_ID = ?", issue.getId())
.order("issueshare.ID DESC")
);

but that didnt help either.

Do you have any more ideas?

I will try to find out what SQL was generated that is causing the error.

[Eduard M]

Hi Jens,

It would be worth a try to change the code to use all upper case for the tablename and check for a different result.

For the SQL generated, it's possible to enable SQL trace logging in Jira:

https://confluence.atlassian.com/jirakb/how-to-enable-detailed-sql-logging-in-jira-665224933.html

If possible do this also in the previous version where it worked, to see if there is any visible difference.

[Jens Kisters __SeibertSolutions]

Hi Eduard,

i tried enabling the logs but i have the impression that SQL statements that fail to parse are not getting logged.

I see the exception in the jira log but not corresponding sql in the sql log, but the sql log contains lots of queries with a matching timestamp.

Cheers

Jens

[Jens Kisters __SeibertSolutions]

We will try making the alias uppercase.

Using lowercase problem is that the alias is enclosed in double quotes in the order by clause:

The query works once they are removed:

Cheers 

Jens

[radosun]

Can you post the code?

[Jens Kisters __SeibertSolutions]

Hi Roy,

here you go

IssueShare[] foundResult = this.activeObjects.find(IssueShare.class, Query.select()
.alias(IssueShare.class, "ISSUESHARE") //alias must be uppercase as a workaround for an driver issue (PH-669)
.where("ISSUESHARE.ISSUE_ID = ?", issue.getId())
.order("ISSUESHARE.ID DESC")
);

kind regards

Jens