Incompatible implementation of jsonpath extraction

[zyguan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* t */ drop table if exists t;
/* t */ create table t (a varchar(100));
/* t */ insert into t values ('{"labels":[{"name":"foo"}]}'),('{"labels":[{"name":"foo"},{"name":"bar"}]}');
/* t */ select a->'$.labels[*].name' from t;

2. What did you expect to see? (Required)

/* t */ drop table if exists t;
-- t >> 0 rows affected
/* t */ create table t (a varchar(100));
-- t >> 0 rows affected
/* t */ insert into t values ('{"labels":[{"name":"foo"}]}'),('{"labels":[{"name":"foo"},{"name":"bar"}]}');
-- t >> 2 rows affected
/* t */ select a->'$.labels[*].name' from t;
-- t >> +-----------------------+
-- t    | a->'$.labels[*].name' |
-- t    +-----------------------+
-- t    | ["foo"]               |
-- t    | ["foo", "bar"]        |
-- t    +-----------------------+

3. What did you see instead (Required)

/* t */ drop table if exists t;
-- t >> 0 rows affected
/* t */ create table t (a varchar(100));
-- t >> 0 rows affected
/* t */ insert into t values ('{"labels":[{"name":"foo"}]}'),('{"labels":[{"name":"foo"},{"name":"bar"}]}');
-- t >> 2 rows affected
/* t */ select a->'$.labels[*].name' from t;
-- t >> +-----------------------+
-- t    | a->'$.labels[*].name' |
-- t    +-----------------------+
-- t    | "foo"                 | <- not an array
-- t    | ["foo", "bar"]        |
-- t    +-----------------------+

4. What is your TiDB version? (Required)

master (6475e89)

[bb7133]

I think this is related to the type system and json implementations. /cc @zyguan @XuHuaiyu

[ywqzzy]

In tidb the current extract implementation is the following code:

func (bj BinaryJSON) Extract(pathExprList []PathExpression) (ret BinaryJSON, found bool) {
	buf := make([]BinaryJSON, 0, 1)
	for _, pathExpr := range pathExprList {
		buf = bj.extractTo(buf, pathExpr)
	}
	if len(buf) == 0 {
		found = false
	} else if len(pathExprList) == 1 && len(buf) == 1 {
		// If pathExpr contains asterisks, len(elemList) won't be 1
		// even if len(pathExprList) equals to 1.
		found = true
		ret = buf[0]
	} else {
		found = true
		ret = buildBinaryArray(buf)
	}
	return
}

When the buf len is 1 and pathExprList = 1, It will not construct an array, which is not compatible with MySQL.
Modify the code into the following code will fix the issue.

func (bj BinaryJSON) Extract(pathExprList []PathExpression) (ret BinaryJSON, found bool) {
	buf := make([]BinaryJSON, 0, 1)
	for _, pathExpr := range pathExprList {
		buf = bj.extractTo(buf, pathExpr)
	}
	if len(buf) == 0 {
		found = false
	} else {
		found = true
		ret = buildBinaryArray(buf)
	}
	return
}

[mengxin9014]

In tidb the current extract implementation is the following code:

func (bj BinaryJSON) Extract(pathExprList []PathExpression) (ret BinaryJSON, found bool) {
	buf := make([]BinaryJSON, 0, 1)
	for _, pathExpr := range pathExprList {
		buf = bj.extractTo(buf, pathExpr)
	}
	if len(buf) == 0 {
		found = false
	} else if len(pathExprList) == 1 && len(buf) == 1 {
		// If pathExpr contains asterisks, len(elemList) won't be 1
		// even if len(pathExprList) equals to 1.
		found = true
		ret = buf[0]
	} else {
		found = true
		ret = buildBinaryArray(buf)
	}
	return
}

When the buf len is 1 and pathExprList = 1, It will not construct an array, which is not compatible with MySQL. Modify the code into the following code will fix the issue.

func (bj BinaryJSON) Extract(pathExprList []PathExpression) (ret BinaryJSON, found bool) {
	buf := make([]BinaryJSON, 0, 1)
	for _, pathExpr := range pathExprList {
		buf = bj.extractTo(buf, pathExpr)
	}
	if len(buf) == 0 {
		found = false
	} else {
		found = true
		ret = buildBinaryArray(buf)
	}
	return
}

It is not exactly. If the buf len is 1 and pathExprList = 1, it will not construct an array if pathExpr does not contain asterisks in MYSQL.
for example:

/* t */ drop table if exists t;
-- t >> 0 rows affected
/* t */ create table t (a varchar(100));
-- t >> 0 rows affected
/* t */ insert into t values ('{"labels":[{"name":"foo"}]}'),('{"labels":[{"name":"foo"},{"name":"bar"}]}');
-- t >> 2 rows affected
/* t */ select a->'$.labels[0].name' from t;
-- t >> +-----------------------+
-- t    | a->'$.labels[0].name' |
-- t    +-----------------------+
-- t    |  "foo"                |
-- t    |  "foo"                |
-- t    +-----------------------+
/* t */ select a->'$.labels[1].name' from t;
-- t >> +-----------------------+
-- t    | a->'$.labels[0].name' |
-- t    +-----------------------+
-- t    |  NULL                 |
-- t    |  "bar"                |
-- t    +-----------------------+