Using active objects alias causes error ORA-00904 invalid identifier

Jens Kisters //SeibertSolutions
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 23, 2017

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

2 answers

1 accepted

0 votes
Answer accepted
Jens Kisters //SeibertSolutions
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 2, 2018

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.

1 vote
Eduard M
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 23, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 23, 2017

Hi Eduard,

no the ID column did not change.

How can the SQL be traced?

Eduard M
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 24, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 26, 2017

Hi Eduard,

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

Unbenannt.PNG

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 27, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 27, 2017

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 27, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 14, 2017

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 15, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 15, 2017

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 17, 2017

We will try making the alias uppercase.

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

fail.PNG

The query works once they are removed:

work.PNG

Cheers 

Jens

radosun July 19, 2019

Can you post the code?

Jens Kisters //SeibertSolutions
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 21, 2019

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

Suggest an answer

Log in or Sign up to answer