SQL parsing oddities (with json datatype in Postgres)

[christopheleroy]

The use of Postgres fields is awesome, but Yada does not consistently parse the queries - I'm just making a not of this.

This fails:
UPDATE MAMBA_MAP
SET criteria = ?v::json add_uid = ?v
WHERE id= ?n and is_deleted=-1

This works:
UPDATE MAMBA_MAP
SET criteria = json(?v), add_uid = ?v
WHERE id= ?n and is_deleted=-1

and this works:
INSERT into INDATA_ISSUE(add_uid, resolution, status, add_ts, id, vendor, studyID, field, value, recommendations, is_severe, is_ok, first_seen_ts, last_seen_ts, samCount, earliestDT, latestDT, is_deleted)
SELECT ?v, ?v, ?v::json, now(), id, vendor, studyID, field, value, recommendations, is_severe, is_ok, first_seen_ts, last_seen_ts, samCount, earliestDT, latestDT, -1
FROM INDATA_ISSUE where is_deleted = 0 and id = ?n

[christopheleroy]

Correction: this 2nd INSERT might not be working... hang-on..

[christopheleroy]

Yes, sorry, the INSERT above (with the ::json) is failing:

2017-12-13 23:12:55,539 http-nio-8080-exec-8      DEBUG (Service.handleRequest: 479) current settings:
{"c":["false"],"j":["[{\"qname\":\"BIOT change resolution status of in-data issue\",\"DATA\":[{\"ID\":\"0\",\"ADD_UID\":\"leroych2\",\"RESOLUTION\":\"yes\",\"STATUS\":\"[]\"}]}]"],"pz":["-1"],"m":["update"]}
2017-12-13 23:12:55,539 http-nio-8080-exec-8      DEBUG (YADARequest.setResponse:2411) com.novartis.opensource.yada.format.CountResponse
2017-12-13 23:12:55,539 http-nio-8080-exec-8      DEBUG (YADARequest.setResponse:2413)            Set [response] to [{com.novartis.opensource.yada.format.CountResponse}]
2017-12-13 23:12:55,539 http-nio-8080-exec-8      DEBUG (Finder.getQuery: 359) YADAQuery [BIOT change resolution status of in-data issue] retrieved from cache
2017-12-13 23:12:55,540 http-nio-8080-exec-8      DEBUG (QueryUtils.getAdaptorClass: 342) JDBCAdaptor class is [com.novartis.opensource.yada.adaptor.PostgreSQLAdaptor]
2017-12-13 23:12:55,540 http-nio-8080-exec-8      DEBUG (QueryUtils.getDataTypes: 968) data type of param [1] = v
2017-12-13 23:12:55,540 http-nio-8080-exec-8      DEBUG (QueryUtils.getDataTypes: 968) data type of param [2] = v
2017-12-13 23:12:55,540 http-nio-8080-exec-8      DEBUG (QueryUtils.getDataTypes: 968) data type of param [3] = v
2017-12-13 23:12:55,540 http-nio-8080-exec-8      DEBUG (QueryUtils.getDataTypes: 968) data type of param [4] = n
2017-12-13 23:12:55,543 http-nio-8080-exec-8      DEBUG (ConnectionFactory.getConnection: 509) app: [BIOT], product: [PostgreSQL], driver: [PostgreSQL Native Driver]
2017-12-13 23:12:55,543     Thread-1007      DEBUG (Finder.updateQueryStatistics: 481) Updating Query Stats for [BIOT change resolution status of in-data issue]
2017-12-13 23:12:55,544 http-nio-8080-exec-8      DEBUG (Parser.visit: 323) insert has subselect
2017-12-13 23:12:55,544 http-nio-8080-exec-8      DEBUG (YADAExpressionDeParser.handleFunction: 238) Function contains jdbc parameter
2017-12-13 23:12:55,544 http-nio-8080-exec-8      DEBUG (QueryManager.prepQueryForExecution: 762)
------------------------------------------------------------
   INSERT/UPDATE/DELETE statement to execute:
------------------------------------------------------------
INSERT into INDATA_ISSUE(add_uid, resolution, status, add_ts, id, vendor, studyID, field, value, recommendations, is_severe, is_ok, first_seen_ts, last_seen_ts, samCount, earliestDT, latestDT, is_deleted)
SELECT ?, ?, ?::json,  now(),  id, vendor, studyID, field, value, recommendations, is_severe, is_ok, first_seen_ts, last_seen_ts, samCount, earliestDT, latestDT, -1
FROM INDATA_ISSUE where is_deleted = 0 and id = ?

2017-12-13 23:12:55,545 http-nio-8080-exec-8      ERROR (Service.execute: 665)
java.lang.NullPointerException
        at com.novartis.opensource.yada.util.QueryUtils.setPositionalParameterValues(QueryUtils.java:1008)
        at com.novartis.opensource.yada.QueryManager.prepQueryForExecution(QueryManager.java:778)
        at com.novartis.opensource.yada.QueryManager.prepQueriesForExecution(QueryManager.java:843)

[christopheleroy]

At least, when we switch the INSERT statement to using json(?v) instead of ?v::json, it fails the same way...

INSERT into INDATA_ISSUE(add_uid, resolution, status, add_ts, id, vendor, studyID, field, value, recommendations, is_severe, is_ok, first_seen_ts, last_seen_ts, samCount, earliestDT, l
atestDT, is_deleted)
SELECT ?, ?, json(?),  now(),  id, vendor, studyID, field, value, recommendations, is_severe, is_ok, first_seen_ts, last_seen_ts, samCount, earliestDT, latestDT, -1
FROM INDATA_ISSUE where is_deleted = 0 and id = ?

2017-12-13 23:16:32,027 http-nio-8080-exec-3      ERROR (Service.execute: 665)
java.lang.NullPointerException
        at com.novartis.opensource.yada.util.QueryUtils.setPositionalParameterValues(QueryUtils.java:1008)
        at com.novartis.opensource.yada.QueryManager.prepQueryForExecution(QueryManager.java:778)
        at com.novartis.opensource.yada.QueryManager.prepQueriesForExecution(QueryManager.java:843)

[varontron]

Was this a problem similar to #98, pushing a JSON Array in the JSONParams? If not, is it still an issue?

[christopheleroy]

I use json(?v) instead of ?v::json everywhere now. I'm not sure this was an issue, just an oddity. It is not #98.