0

Take a look at the following three queries. I cannot understand how a condition that evaluates to TRUE will not return rows when attached to where clause. I expect to get User1 in the second query, as the first query shows that the condition evaluates to TRUE.

cr> select full_name, labels, not 'autogenerated' = ANY(labels), not 'autogenerated' = ANY(labels) or labels = [] from testdb_master_core_users;
+----------------+-------------------+-------------------------------------+--------------------------------------------------------+
| full_name      | labels            | (NOT 'autogenerated' = ANY(labels)) | ((NOT 'autogenerated' = ANY(labels)) OR (labels = [])) |
+----------------+-------------------+-------------------------------------+--------------------------------------------------------+
| User2 Lastname | ["otherlabel"]    | TRUE                                | TRUE                                                   |
| User3 Lastname | ["autogenerated"] | FALSE                               | FALSE                                                  |
| User1 Lastname | []                | TRUE                                | TRUE                                                   |
+----------------+-------------------+-------------------------------------+--------------------------------------------------------+
SELECT 3 rows in set (0.003 sec)
cr> select full_name, labels, not 'autogenerated' = ANY(labels) from testdb_master_core_users where not 'autogenerated' = ANY(labels);
+----------------+----------------+-------------------------------------+
| full_name      | labels         | (NOT 'autogenerated' = ANY(labels)) |
+----------------+----------------+-------------------------------------+
| User2 Lastname | ["otherlabel"] | TRUE                                |
+----------------+----------------+-------------------------------------+
SELECT 1 row in set (0.002 sec)
cr> select full_name, labels, not 'autogenerated' = ANY(labels) from testdb_master_core_users where not 'autogenerated' = ANY(labels) or labels = [];
+----------------+----------------+-------------------------------------+
| full_name      | labels         | (NOT 'autogenerated' = ANY(labels)) |
+----------------+----------------+-------------------------------------+
| User2 Lastname | ["otherlabel"] | TRUE                                |
| User1 Lastname | []             | TRUE                                |
+----------------+----------------+-------------------------------------+
SELECT 2 rows in set (0.002 sec)
Cœur
  • 37,241
  • 25
  • 195
  • 267
pjotr_dolphin
  • 1,207
  • 9
  • 34

1 Answers1

1

Your expectation is right - the second query should also return User1.

This behaviour is caused by the fact that the expressions in the select are evaluated differently than the one in the where clause. The latter, which utilizes the underlying lucene index, seems to do a wrong conversion of NOT which prevents the empty list from being found.

This issue will be fixed with the upcoming releases 1.0.6 and 1.1.1.

christian
  • 56
  • 3
  • This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. – Mohammad Akbari Mar 21 '17 at 09:22