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

cast(json_extract(...) as <int-type>) does not convert quoted numbers

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 13.1
    • None
    • JSON
    • None
    • Unexpected results

    Description

      Noticed this while developing in the main branch, so putting down 13.1 as affected version, but likely affecting more GA versions.

      For example, below the first statement fails to convert "3" to an unsigned integer, the second statement is able to do that, and the third statement is able to do the reverse:

      select cast('[1, 42, "3"]'->'$[2]' as unsigned);
      cast('[1, 42, "3"]'->'$[2]' as unsigned)
      0
      Warnings:
      Warning	1292	Truncated incorrect INTEGER value: '"3"'
      select substring('abcde', '[1, 42, "3"]'->'$[2]');
      substring('abcde', '[1, 42, "3"]'->'$[2]')
      cde
      select cast('[1, 42, "3"]'->'$[0]' as char(1));
      cast('[1, 42, "3"]'->'$[0]' as char(1))
      1
      select cast('[1, "42.567", "3"]'->'$[1]' as decimal(9,4));
      cast('[1, "42.567", "3"]'->'$[1]' as decimal(9,4))
      42.5670
      

      Initial observation suggests the cause is that the first statement calls Item_func_json_extract::val_int_from_str, whereas the second calls Item_func_json_extract::val_int.

      TODO: check what MDEV-38740 does, e.g. what happens if we do select cast(json('"3"') as unsigned)?

      Attachments

        Issue Links

          Activity

            People

              ycp Yuchen Pei
              ycp Yuchen Pei
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.