Adding dimensionless unit through add_units() leads to an error

[francescolovat]

When adding an empty unit value to any of the IIASA DBs (ene_ixmp, ixmp_dev) backend raises the following error:

Code sample or context

import ixmp
ixmp.Platform("ene_ixmp")
# Add empty unit with a comment to emphasize that is dimensionless
mp.add_units("", comment="dimensionless")

Error log:

Traceback (most recent call last):
  File "Platform.java", line 678, in at.ac.iiasa.ixmp.Platform.addUnitToDB
  File "DbDAO.java", line 1332, in at.ac.iiasa.ixmp.database.DbDAO.addUnitToDB
  File "HikariProxyPreparedStatement.java", line -1, in com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate
  File "ProxyPreparedStatement.java", line 61, in com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate
  File "OraclePreparedStatementWrapper.java", line 1061, in oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate
  File "OraclePreparedStatement.java", line 3710, in oracle.jdbc.driver.OraclePreparedStatement.executeUpdate
  File "OraclePreparedStatement.java", line 3730, in oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate
  File "T4CPreparedStatement.java", line 1384, in oracle.jdbc.driver.T4CPreparedStatement.executeInternal
  File "OraclePreparedStatement.java", line 3640, in oracle.jdbc.driver.OraclePreparedStatement.executeInternal
  File "OracleStatement.java", line 1136, in oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout
  File "T4CPreparedStatement.java", line 928, in oracle.jdbc.driver.T4CPreparedStatement.executeForRows
  File "T4CPreparedStatement.java", line 86, in oracle.jdbc.driver.T4CPreparedStatement.doOall8
  File "T4CPreparedStatement.java", line 253, in oracle.jdbc.driver.T4CPreparedStatement.doOall8
  File "T4C8Oall.java", line 610, in oracle.jdbc.driver.T4C8Oall.doOALL
  File "T4CTTIfun.java", line 255, in oracle.jdbc.driver.T4CTTIfun.doRPC
  File "T4CTTIfun.java", line 537, in oracle.jdbc.driver.T4CTTIfun.receive
  File "T4C8Oall.java", line 1052, in oracle.jdbc.driver.T4C8Oall.processError
  File "T4CTTIoer11.java", line 446, in oracle.jdbc.driver.T4CTTIoer11.processError
  File "T4CTTIoer11.java", line 498, in oracle.jdbc.driver.T4CTTIoer11.processError
oracle.jdbc.OracleDatabaseException: Error : 1400, Position : 72, Sql = INSERT INTO ix_unit (id,name, cre_user, cre_date, scheme) VALUES (:1 , :2 , :3 , CURRENT_TIMESTAMP, :4 ), OriginalSql = INSERT INTO ix_unit (id,name, cre_user, cre_date, scheme) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?), Error Msg = ORA-01400: cannot insert NULL into ("IXMP_DEV"."IX_UNIT"."NAME")


The above exception was the direct cause of the following exception:
The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\lovat\.conda\envs\env2\Scripts\mix-models-script.py", line 33, in <module>
    sys.exit(load_entry_point('message-ix-models', 'console_scripts', 'mix-models')())
  File "C:\Users\lovat\.conda\envs\env2\lib\site-packages\click\core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "C:\Users\lovat\.conda\envs\env2\lib\site-packages\click\core.py", line 782, in main
    rv = self.invoke(ctx)
  File "C:\Users\lovat\.conda\envs\env2\lib\site-packages\click\core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "C:\Users\lovat\.conda\envs\env2\lib\site-packages\click\core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "C:\Users\lovat\.conda\envs\env2\lib\site-packages\click\core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "C:\Users\lovat\.conda\envs\env2\lib\site-packages\click\core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "C:\Users\lovat\.conda\envs\env2\lib\site-packages\click\decorators.py", line 33, in new_func
    return f(get_current_context().obj, *args, **kwargs)
  File "c:\users\lovat\documents\github\francescolovat\message_data\message_data\model\transport\cli.py", line 137, in build_cmd
    build.main(context, scenario, **options)
  File "c:\users\lovat\documents\github\francescolovat\message_data\message_data\model\transport\build.py", line 180, in main
    build.apply_spec(scenario, spec, partial(add_data, context=context), **options)
  File "c:\users\lovat\documents\github\francescolovat\message-ix-models\message_ix_models\model\build.py", line 130, in apply_spec
    scenario.platform.add_unit(unit.id, comment=str(unit.name))
  File "c:\users\lovat\documents\github\francescolovat\ixmp\ixmp\core\platform.py", line 259, in add_unit
    self._backend.set_unit(unit, comment)
  File "c:\users\lovat\documents\github\francescolovat\ixmp\ixmp\backend\jdbc.py", line 462, in set_unit
    self.jobj.addUnitToDB(name, comment)
at.ac.iiasa.ixmp.exceptions.IxException: at.ac.iiasa.ixmp.exceptions.IxException: Error assigning an unit-key-id mapping in the database!

Workaround:

Add the following if clause to ixmp\backend\jdbc.py:

Versions

Output of ixmp show-versions

(env2) C:\Users\lovat\Documents\GitHub\francescolovat\message_data>ixmp show-versions

ixmp:        3.3.1.dev25+g8ad0422
     8ad0422 (HEAD -> main, upstream/main, origin/main, origin/HEAD) Merge pull request #422 from iiasa/misc-2021-W27
message_ix:  3.3.1.dev31+gf94304c.d20210721
     f94304c (HEAD -> main, upstream/main, origin/main, origin/HEAD) Adjust objective values for nightly test scenarios (#495)
message_ix_models: 2021.7.27
     80b7d6d (HEAD -> main, tag: v2021.7.27rc1, tag: v2021.7.27, upstream/main, origin/main, origin/HEAD) Mark v2021.7.27 in doc/whatsnew
message_data: 2020.6.21.dev847+g6346e7d6
     3a13199a (HEAD -> master) Create .gitignore

click:       7.1.2
dask:        2.28.0
genno:       installed
graphviz:    0.14.1
jpype:       1.2.1
… JVM path:  C:\Program Files\Java\jre1.8.0_291\bin\server\jvm.dll
openpyxl:    3.0.5
pandas:      1.1.2
pint:        0.16.1
xarray:      0.16.1
yaml:        5.3.1

iam_units:   installed
     7ae006c (HEAD -> main, tag: v2021.7.21, upstream/main, origin/main) Correct README link to MESSAGEix docs
jupyter:     1.0.0
matplotlib:  3.3.2
plotnine:    0.7.1
pyam:        0.10.0+24.g9218247
     9218247 (HEAD -> master) Implement `order` option in line plot (#499)

GAMS:        28.2.0

python:      3.8.5 | packaged by conda-forge | (default, Sep 24 2020, 16:20:24) [MSC v.1916 64 bit (AMD64)]
python-bits: 64
OS:          Windows
OS-release:  10
machine:     AMD64
processor:   Intel64 Family 6 Model 158 Stepping 10, GenuineIntel
byteorder:   little
LC_ALL:      None
LANG:        None
LOCALE:      English_World.utf8

[khaeru]

To be explicit, this appears to happen with JDBCBackend, and a remote Oracle database.

@francescolovat confirmed that it does not happen with JDBCBackend and a HyperSQL local database.

So it appears to arise from some difference in how the Java libraries of oracle.jdbc.driver and HyperSQL handle the empty string value, a difference that the ixmp_source Java code fails to cover.

[khaeru]

As noted at iiasa/message-ix-models#45, the workaround for this can go in downstream packages like message-ix-models, or could also go in the Python code that interacts with the buggy Java parts of JDBCBackend.

[khaeru]

Added "wontfix" because, at least for the moment, we don't have a maintainer for the Java code underlying JDBCBackend.