bson_update with special characters crashes session

Hi all! Following bson_update leads to a failure: # dbaccess charly -Database selected. > create table z_bson_test (b bson);Table created. > insert into z_bson_test values ('{"field1":"Nom","field2":"Chatelain"}'::json::bson);1 row(s) inserted. > select b::JSON from z_bson_test; (expression) {"field1":"Nom","field2":"Chatelain"} 1 row(s) retrieved. > update z_bson_test set b=bson_update(b, '{$set:{"field2":"Châtelain"}}');959: The current transaction has been rolled back due to an internal error. 172: ISAM error: Unexpected internal error Error in line 1Near character position 71 > select b::JSON from z_bson_test; 959: The current transaction has been rolled back due to an internal error. Error in line 1Near character position 71 > select b::JSON from z_bson_test; 349: Database not selected yet. Error in line 1Near character position 31 Online.log 10:52:03 Assert Failed: No Exception Handler 10:52:03 IBM Informix Dynamic Server Version 14.10.FC11ADE 10:52:03 Who: Session(1058, er@vega, 27066702, 700020000573298) Thread(1402, sqlexec, 700020000528c58, 1) File: mtex.c Line: 569 10:52:03 Results: Exception Caught. Type: MT_EX_USR, Context: IX000 10:52:03 Action: Please notify IBM Informix Technical Support. 10:52:03 See Also: /charly/077/log/af.962fbc3 10:52:30 Thread ID 1402 will now be suspended. 10:54:49 Assert Failed: No Exception Handler 10:54:49 IBM Informix Dynamic Server Version 14.10.FC11ADE 10:54:49 Who: Session(1063, er@vega, 27066704, 700020000573298) Thread(1404, sqlexec, 700020000528c58, 1) File: mtex.c Line: 569 10:54:49 Results: Exception Caught. Type: MT_EX_USR, Context: IX000 10:54:49 Action: Please notify IBM Informix Technical Support. 10:54:49 See Also: /charly/077/log/af.964fc69 10:55:16 Thread ID 1404 will now be suspended.If the â in field2 is omitted the bson_update works. Any mistakes on our side or is it an issue for a case? Is there any workaround for that? Because it is used in productive environment and we need a solution. We are using IBM Informix Dynamic Server Version 14.10.FC11 and server locale en_US.819 (if the last one matters). Doesn't work in IBM Informix Dynamic Server Version 15.0.0.2 either. ------------------------------ Emanuel Reisinger ------------------------------

SERVER_LOCALE does not matter but maybe DB_LOCALE. What is the hex value for the char â in your script? But for sure, when there is an issue with this specific char, there should not be an AF. Maybe an SQL error. Best would be to create a case. Please add a SQL file with the statements. Then we can check the hex value for â. Start it with drop table if exists z_bson_test; create table z_bson_test (b bson);then we do not have a need to edit it. When we create it, â might get a different hex value. Depends on locale settings on my machine. Please also share the AF file. drop table if exists z_bson_test; create table z_bson_test (b bson);------------------------------ Hedwig Fuchs ------------------------------

hex value of â is E2 But it's not this special character alone. All characters above 127, starting with € (Euro sign), have the same problem. While preparing the script for a case I've checked the codepage (that is CP1252) and made my tests. Then I've converted the script to UTF8 and look there it worked with dbaccess! I don't know if I can do this in a C code. Server and database are running with ISO-8859-1 (en_US.819) and we have no CLIENT/SERVER/DB_LOCALE set. But as you noted, there should not be an AF. The database has to do the right conversion. We are on AIX 7.2/7.3 I will open a case. ------------------------------ Emanuel Reisinger ------------------------------ SERVER_LOCALE does not matter but maybe DB_LOCALE. What is the hex value for the char â in your script? But for sure, when there is an issue with this specific char, there should not be an AF. Maybe an SQL error. Best would be to create a case. Please add a SQL file with the statements. Then we can check the hex value for â. Start it with drop table if exists z_bson_test; create table z_bson_test (b bson);then we do not have a need to edit it. When we create it, â might get a different hex value. Depends on locale settings on my machine. Please also share the AF file. drop table if exists z_bson_test; create table z_bson_test (b bson);------------------------------ Hedwig Fuchs ------------------------------

Case number TS020771555 ------------------------------ Emanuel Reisinger ------------------------------

Try setting DB_LOCALE. This needs to be the same as when the database was created select * from systables where tabname matches "*GL*"to see with what locale it was created I would expect you should be getting a locale mismatch not a crash so perhaps it is a deffect. Regards Steven ------------------------------ Steven Weiss ------------------------------

Hi Steven, Setting DB_LOCALE does not help either. I'm testing on the AIX server where Informix is running. When setting DB_LOCALE and/or CLIENT_LOCALE then they are the same and no conversion takes place. I think values in JSON/BSON are stored in UTF8, I haven't found any confirmation in Informix documentation so far, so the engine has to convert the strings though. And here lies the problem I assume. ------------------------------ Emanuel Reisinger Axians ICT Austria GmbH ------------------------------

From IBM support: Apologies, we are able to reproduce the issue with your sql files as well and raised a defect for the same. DT456590 - updating of bson values with ASCII characters above 127 may throw 959/172 error(s) causing Assert Failure. and it is expected to be fixed in IDS-14.10.xC12W17 ------------------------------ Emanuel Reisinger Axians ICT Austria GmbH ------------------------------

Hi Emanuel, the way you're writing your bson_update() statement, with an cp1252 single-byte â inside a JSON document, would be a JSON violation, wouldn't it? JSON strictly only allows UTF characters, which is why it works as soon as you're using utf8. All the bson_*() functions expect valid BSON/JSON, whether that's coming from the client or from some database table, i.e. pure ascii data (code points below 128) or a UTF8 database code set (DB_LOCALE). I'm afraid there's no way around this, and there's no way to transform non-UTF8 data to UTF8 while in a non-UTF8 database. Sorry for having to break this bad news, but I think also a defect hardly will be able to change this. BR, Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Hello Andreas, That are interesting news and obvouisly really bad news! Because I haven't found any information in Informix documents and I've read all bson/json information I've found there. Yes, it's documented that the bson functions expect valid BSON/JSON data but nothing about the code sets and how informix engine handles that according to json. A valid bson/json data is for me a syntactically correct data. The database engine has to handle the conversion according to CLIENT and DB_LOCALE although the bson/json data is stored always in UTF8. The database engine has all the information to achieve this, hasn't it? If the user has to provide the bson data in UTF8 although the CLIENT_LOCALE and DB_LOCALE is non-UTF8 is very impracticable. In a program I can manage that but in a client program (dbaccess or any client database tool) I have a mixture of code sets and cannot see all characters correct. I can't believe it. Regards ------------------------------ Emanuel Reisinger Axians ICT Austria GmbH ------------------------------

Yeah, that seems to be some sort of tacit assumption in the documentation (everybody agreeing on what JSON is), and I'd support making it more explicit, with the JSON/BSON data types, with the bson_*() UDRs and also with genBSON(). A clear solution would be to disallow anything JSON/BSON in a non-utf8 database, but that would rule out all those cases where it just works because only plain English characters are used. And yes, I can see how the engine possibly could "know what to do", and maybe something even could be implemented, but it only would lead to more confusion: in your example case, should the converted JSON be converted back to database code set for storing it (which would result in invalid JSON)? or should it be kept as unicode and only be converted back when retrieved by a client? but then what about JSONs containing unicode characters that can't be converted to client's locale? ... Unlike XML, JSON doesn't allow specifying an encoding and strictly is unicode only in strings (s. json.org or RFC ). So I don't think code set conversion really is a solution here, either within the db server or converting between CLIENT_LOCALE and DB_LOCALE (which isn't your case), since a client side JSON containing non-unicode characters actually already is an error there. What you can do, in your setup with no (or same) DB_LOCALE + CLIENT_LOCALE, so character data sent between server and client without any conversion, is sending correct unicode JSON (or BSON) to the database. The database server would not consider or validate such data as character data, at least not outside of BSON functionality, and simply store it as is. Of course you'd get into trouble when then extracting such character data from a JSON and processing it within your database, e.g. comparing it or running other string operations on it. Again, sorry for the disappointment, but I'm afraid that was inevitable. BR, Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

I've to admit you're right. You described it perfectly, and I would welcome more detailed information in the Informix documentation on this topic. Best Regards Emanuel ------------------------------ Emanuel Reisinger Axians ICT Austria GmbH ------------------------------