Empty result for condition WHERE json.array IN (value)

[sashahohloma]

Empty result for condition WHERE json.array IN (value)

[tomatolog]

fixed at e0fd6c0

Now filter select * from idx where j.color in ( 'blue' ) should work the exactly as select *, j.color in ( 'blue' ) as cnd from ifx where cnd=1
Prior to the fix filter IN ( string list ) by json attribute assumes that json.field as always a single string now it should work properly with the multiple kinds of json.field

[sanikolaev]

but there's no case for json field with array of strings

Right. What was done in 88e7179 changes the behaviour for non-arrays. Moreover the behaviour for arrays and scalars is different even when going the ... as cond way:

mysql> drop table if exists t; CREATE TABLE t (j json); INSERT INTO t(j) values ('{"a": [1,2]}'),('{"a": 1}'); SELECT * FROM t WHERE j.a IN (1); select *, ANY(x=1 FOR x IN j.a) cond from t;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.02 sec)

--------------
CREATE TABLE t (j json)
--------------

Query OK, 0 rows affected (0.03 sec)

--------------
INSERT INTO t(j) values ('{"a": [1,2]}'),('{"a": 1}')
--------------

Query OK, 2 rows affected (0.00 sec)

--------------
SELECT * FROM t WHERE j.a IN (1)
--------------

+---------------------+---------+
| id                  | j       |
+---------------------+---------+
| 1515687300166582363 | {"a":1} |
+---------------------+---------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---

--------------
select *, ANY(x=1 FOR x IN j.a) cond from t
--------------

+---------------------+-------------+------+
| id                  | j           | cond |
+---------------------+-------------+------+
| 1515687300166582362 | {"a":[1,2]} |    1 |
| 1515687300166582363 | {"a":1}     |    0 |
+---------------------+-------------+------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---

So I'm not sure it's a bug since WHERE j.a IN (...) is a little ambiguous while using FOR makes it more clear. WHERE ANY/ALL(j.a) IN (...) would make it more clear too, so perhaps this is the syntax which makes sense in the context of this task and comparison with arrays.

[sashahohloma]

OK, thank you.

This behavior seemed like a bug because

1. Example table of original issue contains JSON column with array attribute
2. Query with WHERE json.array IN (...) does not return an error

Query with WHERE ANY/ALL(json.array) IN (...) sounds great, because virtual conditional column cannot be included in HTTP search request

[tomatolog]

related to #778