Problem with MATCH_SUBSTRUCT

[fredrikw]

I have found a very strange problem with MATCH_SUBSTRUCT when installing on Ubuntu 14.04 and MySQL 5.5.43-0ubuntu0.14.04.1 (Ubuntu).

Basically, when I use a molfile or a serializedmolecule that is stored in the database, I get the error message ERROR 1123 (HY000) at line 20: Can't initialize function 'match_substruct'; The second argument is null.

To show the problem, I made a docker image that can be found at https://registry.hub.docker.com/u/eclabnote/mychem/

Loading that docker image and running the command /testmychem.sh will first run the usual tests for mychem that all pass and then show the following output.

*************************** 1. row ***************************
1: SELECT MATCH_SUBSTRUCT('c1ccccc1', MOLECULE_TO_SERIALIZEDOBMOL(SMILES_TO_MOLECULE('c1ccccc1O'))) AS match_from_smiles FROM `compound_mol`
match_from_smiles
1
1
1
*************************** 1. row ***************************
2: SELECT MATCH_SUBSTRUCT('c1ccccc1', MOLECULE_TO_SERIALIZEDOBMOL(`mol`)) AS match_from_moleculefield FROM `compound_mol`
ERROR 1123 (HY000) at line 20: Can't initialize function 'match_substruct'; The second argument is null
*************************** 1. row ***************************
3: SELECT MATCH_SUBSTRUCT('c1ccccc1', `serializedOBmol`) AS match_from_sermolfield FROM `compound_ser_mol`
ERROR 1123 (HY000) at line 23: Can't initialize function 'match_substruct'; The second argument is null
*************************** 1. row ***************************
4: SET @mol = SMILES_TO_MOLECULE('c1ccccc1O');
5: SELECT MATCH_SUBSTRUCT('c1ccccc1', MOLECULE_TO_SERIALIZEDOBMOL(@mol));
MATCH_SUBSTRUCT('c1ccccc1', MOLECULE_TO_SERIALIZEDOBMOL(@mol))
1

For each command, it first shows the command that is about to be run, followed by the output from that command. The SQL-commands that created this can be found at https://gist.github.com/fredrikw/991afe40f00cbca0f258#file-testmychemmatch-sql

[Pansanel]

Hi Frederik, I will look at your commit next week. It is effectively a strange problem. Jerome

[Pansanel]

Hi Frederik, I found the problem. If you look at: https://dev.mysql.com/doc/refman/5.0/en/udf-arguments.html, you can read: For a nonconstant argument, args->args[i] is 0. A nonconstant argument is an expression that refers to values that may change from row to row, such as column names or functions that are called with nonconstant arguments. When you check against one molecule, the argument is a constant. If you check against a column, it is like non constant and is NULL by default. Therefore it is working. I will apply your fix and check if another place in the code require the same fix. Jerome