maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hello Igor,

I was looking at the example you've posted earlier this week on Slack:

Igor> @holyfoot, @spetrunia: here' serious problem with identification for JSON_TABLEs:

create table t1 (item_name varchar(32), item_props varchar(1024));
insert into t1 values ('Laptop', '{"color": "black", "price": 1000}');
insert into t1 values ('Jeans',  '{"color": "blue", "price": 50}');

create table t2 (item_name varchar(32), item_props varchar(1024));
insert into t2 values ('Laptop', '{"color": "black", "price": 1200}');
insert into t2 values ('Shirt',  '{"color": "blue", "price": 20}');

MariaDB [test]> select * from t1 as t2 where item_name not in    (select item_name from t2 as s, json_table(t2.item_props,'$' columns( color varchar(100) path '$.color')) as t);
+-----------+--------------------------------+
| item_name | item_props                     |
+-----------+--------------------------------+
| Jeans     | {"color": "blue", "price": 50} |
+-----------+--------------------------------+

Igor> t2.item_props should not be valid, because it refers to t2 as s. 

Let me format the query:

select * from t1 as t2
where 
  item_name not in
   (select item_name 
    from 
      t2 as s, 
      json_table(t2.item_props,
                 '$' columns( color varchar(100) path '$.color')
                 ) as t
    );

My reply is: "t2.item_props" does NOT refer to (t2_as_s).item_props.  It refers
to the top level select's  (t1_as_t2).item_props.

Proof: let's rename the t1-as-t2 in the top-level select:

select * from t1 as t2qqqqqqqqq
where 
  item_name not in
   (select item_name 
    from 
      t2 as s, 
      json_table(t2.item_props,
                 '$' columns( color varchar(100) path '$.color')
                 ) as t
    );
ERROR 1054 (42S22): Unknown column 't2.item_props' in 'JSON_TABLE argument'


Igor> This easily can be seen after adding

MariaDB [test]> insert into t2 values ('Jeans',  '{"color": "black", "price": 20}');
Query OK, 1 row affected (0.004 sec)

MariaDB [test]> select * from t1 as t2 where item_name not in    (select item_name from t2 as s, json_table(t2.item_props,'$' columns( color varchar(100) path '$.color')) as t);

I get an empty set for this query. Is there any issue?

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net