Description:
If you look at how-to-repeat, please: I have two blob columns (one is TINYBLOB, other is BLOB, which is supposed to only change the limit on size), with same value 'abc', when I store them into JSON they become different, this doesn't look logical.
The cause is that the data type is encoded in the resulting string. But shouldn't it be encoded as "more generic", i.e. just "blob", for all blob subtypes?
Notice how it's inconsistent with other types: there is no problem with TINYTEXT vs TEXT, or with different sizes of VARCHAR, or of INT.
With BLOB, see the "0" (false) equality result:
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b)
["base64:type249:YWJj"] ["base64:type252:YWJj"] 0
With TEXT, all fine, we get "1" (true):
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b)
["abc"] ["abc"] 1
VARCHAR too:
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b)
["abc"] ["abc"] 1
INT too:
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b)
[123] [123] 1
Reproduced with 9.0 from github, revision:
commit 596f0d238489a9cf9f43ce1ff905984f58d227b6 (HEAD -> trunk, tag: mysql-cluster-9.0.1, tag: mysql-9.0.1, origin/trunk)
Author: Bjorn Munch <bjorn.munch@oracle.com>
Date: Fri Jul 12 21:03:23 2024 +0200
Update License Book
How to repeat:
create table t(a TINYBLOB, b BLOB);
INSERT INTO t VALUES('abc', 'abc');
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
drop table t;
create table t(a TINYTEXT, b TEXT);
INSERT INTO t VALUES('abc', 'abc');
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
drop table t;
create table t(a VARCHAR(10), b VARCHAR(20));
INSERT INTO t VALUES('abc', 'abc');
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
drop table t;
create table t(a TINYINT, b INT);
INSERT INTO t VALUES(123,123);
SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t;
drop table t;
Description: If you look at how-to-repeat, please: I have two blob columns (one is TINYBLOB, other is BLOB, which is supposed to only change the limit on size), with same value 'abc', when I store them into JSON they become different, this doesn't look logical. The cause is that the data type is encoded in the resulting string. But shouldn't it be encoded as "more generic", i.e. just "blob", for all blob subtypes? Notice how it's inconsistent with other types: there is no problem with TINYTEXT vs TEXT, or with different sizes of VARCHAR, or of INT. With BLOB, see the "0" (false) equality result: SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b) ["base64:type249:YWJj"] ["base64:type252:YWJj"] 0 With TEXT, all fine, we get "1" (true): SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b) ["abc"] ["abc"] 1 VARCHAR too: SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b) ["abc"] ["abc"] 1 INT too: SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; JSON_ARRAY(a) JSON_ARRAY(b) JSON_ARRAY(a)=JSON_ARRAY(b) [123] [123] 1 Reproduced with 9.0 from github, revision: commit 596f0d238489a9cf9f43ce1ff905984f58d227b6 (HEAD -> trunk, tag: mysql-cluster-9.0.1, tag: mysql-9.0.1, origin/trunk) Author: Bjorn Munch <bjorn.munch@oracle.com> Date: Fri Jul 12 21:03:23 2024 +0200 Update License Book How to repeat: create table t(a TINYBLOB, b BLOB); INSERT INTO t VALUES('abc', 'abc'); SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; drop table t; create table t(a TINYTEXT, b TEXT); INSERT INTO t VALUES('abc', 'abc'); SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; drop table t; create table t(a VARCHAR(10), b VARCHAR(20)); INSERT INTO t VALUES('abc', 'abc'); SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; drop table t; create table t(a TINYINT, b INT); INSERT INTO t VALUES(123,123); SELECT JSON_ARRAY(a),JSON_ARRAY(b),JSON_ARRAY(a)=JSON_ARRAY(b) FROM t; drop table t;