When using ODBC to query to a 'string' that matches the value of a field name, it translate to an incorrect 'same as' query that could returning the incorrect rows.
Steps to Reproduce
- Create an incident with short_description set as 'number' (string).
2. Now open iSQL and run query:
> select number from incident where short_description='number'.
The query does not return any result. This is caused by the way ODBC translate SQL commands into SOAP requests.
The actual encoded query section causing the problem is 'short_descriptionSAMEASnumber' instead of 'short_description=number'.
This issue can be resolved using the ODBC component version 1.0.14 which is native in the London release. However, once available, the ODBC 1.0.14 driver can be downloaded and installed on any instance.
As a workaround, modify the queries to not include the value of a field name, e.g.: select number from incident where short_description LIKE 'number';
Here is a more complex example query that fails:
FROM sys_dictionary a,
AND a.element=b.element // <== this fails because the data contains 'field' names on the table.
Here is the optimised version:
// without the problem
FROM (SELECT name+':'+element key1, name, element FROM sys_dictionary WHERE name='task') a,
(SELECT name+':'+element key2, name, element FROM sys_documentation WHERE name = 'task') b
WHERE a.key1 = b.key2
Related Problem: PRB1162624