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

JSON_TABLE: Assertion `join->best_read < double(1.797...) fails upon JSON_TABLE in subquery

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.6.0
    • Component/s: JSON, Optimizer
    • Labels:
      None

      Description

      CREATE TABLE t1 (a INT, b TEXT);
      INSERT INTO t1 VALUES (1,'{}'),(2,'[]');
      SELECT * FROM t1 WHERE EXISTS(SELECT * FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt WHERE jt.o = t1.a);
       
      # Cleanup
      DROP TABLE t1;
      

      bb-10.6-mdev17399-hf a96203921343

      mariadbd: /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:8859: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570814527423731704357e+308L)' failed.
      210410  2:15:04 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007f4382a91f36 in __GI___assert_fail (assertion=0x562cf6e39ea0 "join->best_read < double(1.79769313486231570814527423731704357e+308L)", file=0x562cf6e348c0 "/data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc", line=8859, function=0x562cf6e39e40 "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:101
      #8  0x0000562cf4e5c003 in greedy_search (join=0x62b00003c918, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=4) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:8859
      #9  0x0000562cf4e59bec in choose_plan (join=0x62b00003c918, join_tables=3) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:8416
      #10 0x0000562cf528d772 in optimize_semijoin_nests (join=0x62b00003c918, all_table_map=3) at /data/src/bb-10.6-mdev17399-hf/sql/opt_subselect.cc:2458
      #11 0x0000562cf4e44d98 in make_join_statistics (join=0x62b00003c918, tables_list=..., keyuse_array=0x62b00003cc08) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:5645
      #12 0x0000562cf4e21c3f in JOIN::optimize_inner (this=0x62b00003c918) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:2272
      #13 0x0000562cf4e1b0d8 in JOIN::optimize (this=0x62b00003c918) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:1641
      #14 0x0000562cf4e3be30 in mysql_select (thd=0x62b000069288, tables=0x62b000038a48, fields=..., conds=0x62b00003bd90, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x62b00003c8e8, unit=0x62b00006d460, select_lex=0x62b0000383f8) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:4749
      #15 0x0000562cf4e0d0b4 in handle_select (thd=0x62b000069288, lex=0x62b00006d398, result=0x62b00003c8e8, setup_tables_done_option=0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_select.cc:419
      #16 0x0000562cf4d76bfa in execute_sqlcom_select (thd=0x62b000069288, all_tables=0x62b000038a48) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:6231
      #17 0x0000562cf4d65edc in mysql_execute_command (thd=0x62b000069288) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:3927
      #18 0x0000562cf4d81eb9 in mysql_parse (thd=0x62b000069288, rawbuf=0x62b0000382a8 "SELECT * FROM t1 WHERE EXISTS(SELECT * FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt WHERE jt.o = t1.a)", length=113, parser_state=0x7f4379790bb0) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:8006
      #19 0x0000562cf4d588e0 in dispatch_command (command=COM_QUERY, thd=0x62b000069288, packet=0x629000258289 "SELECT * FROM t1 WHERE EXISTS(SELECT * FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt WHERE jt.o = t1.a)", packet_length=113, blocking=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:1888
      #20 0x0000562cf4d5561b in do_command (thd=0x62b000069288, blocking=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_parse.cc:1399
      #21 0x0000562cf51982be in do_handle_one_connection (connect=0x61100000b388, put_in_cache=true) at /data/src/bb-10.6-mdev17399-hf/sql/sql_connect.cc:1410
      #22 0x0000562cf5197c1b in handle_one_connection (arg=0x61100000b248) at /data/src/bb-10.6-mdev17399-hf/sql/sql_connect.cc:1312
      #23 0x0000562cf5eaba63 in pfs_spawn_thread (arg=0x616000101b08) at /data/src/bb-10.6-mdev17399-hf/storage/perfschema/pfs.cc:2201
      #24 0x00007f4382fa9609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #25 0x00007f4382b7d293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      MySQL returns a result set:

      MySQL 8.0.23

      MySQL [test]> SELECT * FROM t1 WHERE EXISTS(SELECT * FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt WHERE jt.o = t1.a);
      +------+------+
      | a    | b    |
      +------+------+
      |    1 | {}   |
      +------+------+
      1 row in set (0.001 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: