Bug 6786 - function json.isvalid(js json) is not useful, could be removed
Summary: function json.isvalid(js json) is not useful, could be removed
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.33.3 (Apr2019)
Hardware: Other Linux
: Normal minor
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-11-06 20:36 CET by Martin van Dinther
Modified: 2019-11-06 20:36 CET (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2019-11-06 20:36:03 CET
User-Agent:       Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:69.0) Gecko/20100101 Firefox/69.0
Build Identifier: 

file sql/scripts/40_json.sql contains:
  create function json.isvalid(js json)
  returns bool external name json.isvalid;
This function is not useful as a non null JSON object value argument will always return true. It could be replaced with: json_col IS NOT NULL
to get the same output as json.isvalid(json_col).
The function json.isvalid(js json) could be removed.

Reproducible: Always

Steps to Reproduce:
create table t6786 (v varchar(99), j json);
insert into t6786 (v) values (null), (''), ('null'), ('false'), ('true'),
('123'), ('123.45'), ('\"abc123\"'), ('[]'),('{}'), ('[{}]'), ('[{]}');
-- 12 rows inserted
select * from t6786 order by v;
select * from t6786 where json.isvalid(v) order by v;
update t6786 set j = cast(v as json) where json.isvalid(v);
select * from t6786 order by v;
select * from t6786 where json.isvalid(j) order by v;
select v, json.isvalid(v) v_isvalidjson, j, json.isvalid(j) j_isvalidjson, j IS NOT NULL as j_isnotnull from t6786 order by v;
drop table t6786;

select f.name, f.func, f.mod, f.language, f.type, /* f.side_effect, f.varres, f.vararg, f.schema_id, */
f.system, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
from sys.functions f join sys.args a on f.id = a.func_id where f.name = 'isvalid';


Actual Results:  
sql>create table t6786 (v varchar(99), j json);
operation successful
sql>insert into t6786 (v) values (null), (''), ('null'), ('false'), ('true'),
more>('123'), ('123.45'), ('\"abc123\"'), ('[]'),('{}'), ('[{}]'), ('[{]}');
12 affected rows
sql>-- 12 rows inserted
sql>select * from t6786 order by v;
+----------+------+
| v        | j    |
+==========+======+
| null     | null |
|          | null |
| "abc123" | null |
| 123      | null |
| 123.45   | null |
| []       | null |
| [{]}     | null |
| [{}]     | null |
| false    | null |
| null     | null |
| true     | null |
| {}       | null |
+----------+------+
12 tuples
sql>select * from t6786 where json.isvalid(v) order by v;
+------+------+
| v    | j    |
+======+======+
| []   | null |
| [{}] | null |
| {}   | null |
+------+------+
3 tuples
sql>update t6786 set j = cast(v as json) where json.isvalid(v);
3 affected rows
sql>select * from t6786 order by v;
+----------+------+
| v        | j    |
+==========+======+
| null     | null |
|          | null |
| "abc123" | null |
| 123      | null |
| 123.45   | null |
| []       | []   |
| [{]}     | null |
| [{}]     | [{}] |
| false    | null |
| null     | null |
| true     | null |
| {}       | {}   |
+----------+------+
12 tuples
sql>select * from t6786 where json.isvalid(j) order by v;
+------+------+
| v    | j    |
+======+======+
| []   | []   |
| [{}] | [{}] |
| {}   | {}   |
+------+------+
3 tuples
sql>select v, json.isvalid(v) v_isvalidjson, j, json.isvalid(j) j_isvalidjson, j IS NOT NULL as j_isnotnull from t6786 order by v;
+----------+---------------+------+---------------+-------------+
| v        | v_isvalidjson | j    | j_isvalidjson | j_isnotnull |
+==========+===============+======+===============+=============+
| null     | false         | null | false         | false       |
|          | false         | null | false         | false       |
| "abc123" | false         | null | false         | false       |
| 123      | false         | null | false         | false       |
| 123.45   | false         | null | false         | false       |
| []       | true          | []   | true          | true        |
| [{]}     | false         | null | false         | false       |
| [{}]     | true          | [{}] | true          | true        |
| false    | false         | null | false         | false       |
| null     | false         | null | false         | false       |
| true     | false         | null | false         | false       |
| {}       | true          | {}   | true          | true        |
+----------+---------------+------+---------------+-------------+
12 tuples
sql>drop table t6786;
operation successful
sql>
sql>select f.name, f.func, f.mod, f.language, f.type, /* f.side_effect, f.varres, f.vararg, f.schema_id, */
more>f.system, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
more>from sys.functions f join sys.args a on f.id = a.func_id where f.name = 'isvalid';
+---------+--------------------------------------------------------+------+------+------+-------+--------+---------+------+------+------+------+
| name    | func                                                   | mod  | lang | type | syste | name   | type    | type | type | inou | numb |
:         :                                                        :      : uage :      : m     :        :         : _dig : _sca : t    : er   :
:         :                                                        :      :      :      :       :        :         : its  : le   :      :      :
+=========+========================================================+======+======+======+=======+========+=========+======+======+======+======+
| isvalid | create function json.isvalid(js string)                | json |    1 |    1 | true  | js     | clob    |    0 |    0 |    1 |    1 |
:         : returns bool external name json.isvalid;               :      :      :      :       :        :         :      :      :      :      :
| isvalid | create function json.isvalid(js string)                | json |    1 |    1 | true  | result | boolean |    1 |    0 |    0 |    0 |
:         : returns bool external name json.isvalid;               :      :      :      :       :        :         :      :      :      :      :
| isvalid | create function json.isvalid(js json)                  | json |    1 |    1 | true  | js     | json    |    0 |    0 |    1 |    1 |
:         : returns bool external name json.isvalid;               :      :      :      :       :        :         :      :      :      :      :
| isvalid | create function json.isvalid(js json)                  | json |    1 |    1 | true  | result | boolean |    1 |    0 |    0 |    0 |
:         : returns bool external name json.isvalid;               :      :      :      :       :        :         :      :      :      :      :
+---------+--------------------------------------------------------+------+------+------+-------+--------+---------+------+------+------+------+
4 tuples
sql>



The file sql/scripts/40_json.sql also contains:
  create function json.isvalid(js string)
  returns bool external name json.isvalid;
which is useful to test character strings before converting them to a json type.