dumbfounded by a simple query, perhaps due to a simple subquery

[christopheleroy]

Hello,

the relatively simply query below fails. I have no idea why.

This works in SQL:
update COUNTRY_SITE_PERSON
SET mod_time = systimestamp, is_active=0, mod_emp521 = (select emp521 from person where person_id=-1)
WHERE country_site_person_id = 144468 and is_active=1

but during a batch:

{ Status: 500,
Help: 'https://github.com/Novartis/YADA#other',
Message: 'Index: 1, Size: 1',
Query: 'update COUNTRY_SITE_PERSON\nSET mod_time = systimestamp, is_active=0, mod_emp521 = (select emp521 from person where person_id=?n)\nWHERE country_site_person_id = ?n and is_active=1',
StackTrace:
[ 'java.lang.IndexOutOfBoundsException: Index: 1, Size: 1',
'at java.util.ArrayList.rangeCheck(ArrayList.java:653)',
'at java.util.ArrayList.get(ArrayList.java:429)',
'at com.novartis.opensource.yada.adaptor.JDBCAdaptor.execute(JDBCAdaptor.java:457)',
'at com.novartis.opensource.yada.Service._execute(Service.java:730)',
'at com.novartis.opensource.yada.Service.execute(Service.java:596)',
'at org.apache.jsp.yada_jsp._jspService(yada_jsp.java:222)',
'at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)',
...
...

 'at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)',
 'at java.lang.Thread.run(Thread.java:745)' ],

StatusText: 'Internal Server Error',
Params:
[ { PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144468]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144469]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144458]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144459]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144460]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144461]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144720]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144467]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144464]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144465]' },
{ PERSON_ID: '[-1]', COUNTRY_SITE_PERSON_ID: '[144466]' } ],
Source: 'https://github.com/Novartis/YADA',
Exception: 'java.lang.IndexOutOfBoundsException',
Type: 'POST',
Qname: 'DSMT remove person site assignment',

It seems Yada doesn't see the 2nd parameter (person_id) in the sub-query

---

INSERT/UPDATE/DELETE statement to execute:

update COUNTRY_SITE_PERSON
SET mod_time = systimestamp, is_active=0, mod_emp521 = (select emp521 from person where person_id=?)
WHERE country_site_person_id = ? and is_active=1

2018-04-14 08:40:23,182 ajp-nio-8010-exec-1 DEBUG (QueryUtils.setPositionalParameterValues:1010) Column [1: country_site_person_id] has value [144468]
2018-04-14 08:40:23,182 ajp-nio-8010-exec-1 DEBUG (QueryManager.prepQueryForExecution: 762)

INSERT/UPDATE/DELETE statement to execute:

update COUNTRY_SITE_PERSON
SET mod_time = systimestamp, is_active=0, mod_emp521 = (select emp521 from person where person_id=?)
WHERE country_site_person_id = ? and is_active=1

[varontron]

Have you tried requests with both standard and json params, and was there any difference?

[christopheleroy]

I tried with JSON Params only, but once with the positional params: {YADA_1: -1, YADA_2: 144468}
etc.
The tomcat logs do show that it eventually tries to set only one parameters, not 2.

So, it parses well enough to replace the 2 ?n params into two ? ... but then ... forgets one.

[christopheleroy]

I tried an update on inner join - but Oracle seems too slow when I tried, so I just forgot the PERSON_ID parameter, because I'm working back-end where person_id is always -1.

I'll pass along the update inner join

[christopheleroy]

Oracle has this:

UPDATE (select a.is_active, p.emp521
from country_site_person a inner join person p on a.is_active=1 /* dummy on statement to make it work*/
where p.person_id = ?n and a.country_site_person_id = ?n and a.is_active=1) t
SET
t.is_active=0, t.mod_emp521 = p.emp521, t.mod_time=systimestamp

More standard SQL could probably do:

UPDATE country_site_person
set mod_emp521 = p.emp521, mod_time = systimestamp, is_Active=0
FROM country_site_person cra join person p on (cra.is_Active=1)
WHERE cra.country_site_person_id = ?n and p.person_id = ?n

This is a poor-man's join -- just to prevent a subquery for Yada to make it work.
Eventually, I got a crash here and a daft slowness there, so I abandoned the complexity for now...

[christopheleroy]

REMINDER: I believe resolution for Issue #69 would probably solve this kind of issues.

[christopheleroy]

about to add a new comment to #69 -- hopefully you'll chime in ...