Igor Gaponenko added a comment - 09/Mar/17 8:17 PM Synopsis To see the problem one had to connect directly to the MySQL/MariaDB service of the PDAC master node and pick one of the object identifiers from the corresponding index table, or just by picking any random key from that table. For example: SELECT * FROM qservMeta.wise_00__Object LIMIT 10; + ----------------------+---------+------------+ | source_id | chunkId | subChunkId | + ----------------------+---------+------------+ | 0000m016_ac51-000001 | 112201 | 759 | | 0000m016_ac51-000003 | 112881 | 138 | | 0000m016_ac51-000005 | 112201 | 760 | | 0000m016_ac51-000006 | 112201 | 557 | | 0000m016_ac51-000008 | 112880 | 11 | | 0000m016_ac51-000009 | 112200 | 770 | | 0000m016_ac51-000010 | 112201 | 760 | | 0000m016_ac51-000011 | 112201 | 691 | | 0000m016_ac51-000012 | 112201 | 690 | | 0000m016_ac51-000013 | 112201 | 621 | + ----------------------+---------+------------+ Then one has to connect to the Qservr mysql-proxy service (port 4040 ) on that node and do: SELECT COUNT (*) FROM wise_00.Object WHERE source_id= '0000m016_ac51-000001' ; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 0 | + ----------------+ 1 row in set (0.55 sec) or: SELECT COUNT (*) FROM wise_00.Object WHERE source_id IN ( '0000m016_ac51-000001' ); + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 0 | + ----------------+ 1 row in set (0.53 sec) In contrast with that the full scan query would always return the right object: SELECT COUNT (*) FROM wise_00.Object WHERE source_id LIKE '0000m016_ac51-000001' ; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 1 | + ----------------+ 1 row in set (6 min 22.42 sec) Unfortunately the performance of that query is very bad because all chunks would need to be scanned.

Igor Gaponenko added a comment - 09/Mar/17 8:26 PM - edited Analyzing the root cause of the problem It turns out this problem is seen due to a bug introduced in an attempt to implement a fix for: https://jira.lsstcorp.org/browse/DM-2887 The following change made in the implementation of method: core/modules/qproc/SecondaryIndex.cc .. std::string makeLookupSql(...) { ... } was incorrect as it was using a utility which was not meant to be used for generating valid SQL statements. Details on the GitHub changes can be found at: https://github.com/lsst/qserv/commit/536b7bc8962d25ed6950cf623286a89ef802d26c#diff-e0708a58f2f8cab8cd9f9ad7c7c02bfaR85 The proposed fix was to reimplement the code to generate the correct SQL and to follow requirements of the original ticked DM-2887 ]

Igor Gaponenko added a comment - 09/Mar/17 8:29 PM - edited Testing the fix Once the new version of the Qserv containers was installed in PADC the following tests ere made to ensure the problematic queries (explained in the Synopsis section above) would work as required. And indeed, they do: SELECT source_id FROM wise_00.Object WHERE source_id= '0000m016_ac51-000001' ; + ----------------------+ | source_id | + ----------------------+ | 0000m016_ac51-000001 | + ----------------------+ 1 row in set (0.40 sec) SELECT source_id FROM wise_00.Object WHERE source_id IN ( '0000m016_ac51-000001' ); + ----------------------+ | source_id | + ----------------------+ | 0000m016_ac51-000001 | + ----------------------+ 1 row in set (0.34 sec) SELECT source_id FROM wise_00.Object WHERE source_id IN ( '0075m016_ac51-028478' , '0000m016_ac51-000001' ); + ----------------------+ | source_id | + ----------------------+ | 0000m016_ac51-000001 | | 0075m016_ac51-028478 | + ----------------------+ 2 rows in set (0.35 sec) Proposing to merge this fix with master .

Igor Gaponenko added a comment - 10/Mar/17 7:55 PM I have reviewed proposed changes and they look right to me.