Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30623

JSON_TABLE in subquery not working

    XMLWordPrintable

Details

    Description

      CREATE TEMPORARY TABLE t1 (t1_id int, t1_json text);
      INSERT t1 VALUES (1, '[[10,20]]'), (2, '[[100,200]]'), (3, '[[1000,2000]]');
       
      SELECT
      	t1_id,
      	t2_json,
      	(SELECT SUM(x2) 
      	FROM 
      	  JSON_TABLE(t2_json, '$[*]' COLUMNS ( x2 int PATH '$' )) t3
      	) t2_json_sum
      FROM 
        t1
        CROSS JOIN 
        JSON_TABLE(t1_json, '$[*]' COLUMNS (t2_json json PATH '$' )) t2;
      

      Gives the result:

      +-------+-------------+-------------+
      | t1_id | t2_json     | t2_json_sum |
      +-------+-------------+-------------+
      |     1 | [10,20]     |          30 |
      |     2 | [100,200]   |          30 |
      |     3 | [1000,2000] |          30 |
      +-------+-------------+-------------+
      
      

      In the subquery the t2_json column from first row is used on every row.

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              tgj1970 Thomas G. Jensen
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.