← Back to team overview

maria-developers team mailing list archive

Re: MDEV-17399: wrong result when in SP

 

On Fri, Feb 05, 2021 at 02:01:22PM +0300, Sergey Petrunia wrote:
> I've also found this:
> 
> CREATE FUNCTION FN_COUNT_ROWS(X JSON)
> RETURNS INT DETERMINISTIC
> RETURN (
>   SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der
> );
> SELECT FN_COUNT_ROWS('[1, 2]') CNT;
> 
> This produces 
> +------+
> | CNT  |
> +------+
> |    0 |
> +------+
> 
> while it should produce 2. 

Ok, there is no issue with SP. A testcase without SP:

CREATE TABLE t (j JSON) engine=myisam;
INSERT INTO t VALUES
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'),
('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]');
SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt;

will produce "0".

The problem where is that the code in opt_sum_query() assumes it can use
records() to get # rows from the ha_json_table object.

It needs to be changed to take into account that ha_json_table doesn't have
records available, yet. 

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



References