Besides, here is still the same problem with a slightly different example, so the behavior is already inconsistent even within the current JSON_TABLE implementation:

MariaDB [test]> select * from json_table('{"a":"foo"}', '$.a' columns (v char(1) path '$')) t;

+------+

| v    |

+------+

| f    |

+------+

1 row in set (0.000 sec)

MySQL returns NULL which indicates an error:

MySQL [test]> select * from json_table('{"a":"foo"}', '$.a' columns (v char(1) path '$')) t;

+------+

| v    |

+------+

| NULL |

+------+

1 row in set (0.001 sec)

Raising to a technical blocker. While actual fixing can very well be delayed (even indefinitely), the general approach must be defined, agreed upon, documented and later complied with; otherwise individual fixes upon assorted complaints will only create more discrepancies.

What needs to be decided is:

• should usual implicit type conversion apply to JSON_TABLE, specifically
  • while casting retrieved values to the defined datatypes
  • while casting defined "default/on error/empty" values to the defined datatypes
• if implicit type conversion applies, should warnings be issued
• if implicit type conversion applies, should it be raised to an error when ERROR ON ERROR is set;
• should implicit type conversion for JSON_TABLE be affected by SQL_MODE STRICT_*_TABLES modes, and if it should, when and by which.

The standard specifies is:

• if the value is an array or object — it's an error "scalar required"
• if it's a scalar — same rules as CAST(value AS type)

So, in the example from the bug description, the result should be 0, 123, 5678, 5678. In the example of the first comment the correct result is "f".

Perhaps there should be warnings, as with CAST.

Better to actually reuse the CAST code, so that if we'll ever get some strict mode variant where CAST("asd" AS INT) is an error then JSON_TABLE would automatically use the new behavior.

Agree with serg's comment. I think there should be a warning.

The problem with reusing the code from CAST function is that there's no single piece of code to reuse: CAST is implemented by many Items:

Item_func_signed

Item_func_unsigned

Item_decimal_typecast

Item_char_typecast

Item_decimal_typecast

Item_real_typecast

also search for create_typecast_item

That's the change i propose to begin with

--- a/sql/json_table.cc

+++ b/sql/json_table.cc

@@ -399,7 +399,7 @@ int ha_json_table::rnd_next(uchar *buf)

     they are in).

   */

   cf_orig= table->in_use->count_cuted_fields;

-  table->in_use->count_cuted_fields= CHECK_FIELD_EXPRESSION;

+  table->in_use->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL;

   res= fill_column_values(buf, NULL);

If we take this patch and run the example from the bug description, we get

+------+--------+

| id   | intcol |

+------+--------+

|    1 |   5678 |

|    2 |    123 |

|    3 |   5678 |

|    4 |   5678 |

+------+--------+

4 rows in set, 1 warning (0.000 sec)

| Warning | 1366 | Incorrect integer value: 'asd' for column ``.`(temporary)`.`intcol` at row 1 |

That is, converting the first "a":"asd" into integer now produces a warning, but the default '5678' on error rule is still fired.

This is not what was described in Serg's comment above.

Candidate patch: https://gist.github.com/spetrunia/5f4f13ef5fdf91ca1f343bcb34f3c815

Here is my version of the fix.
https://github.com/MariaDB/server/commit/cbe2d62b55b1654af35f702861297dc97d713301
so we can compare.

Review input provided on Slack

Ok to push