Search code examples
mysqlmysql-5.6

Changing order of where clauses breaks the query

I ran into this case using MySQL 5.6:

This query works and returns expected results:

select *
from some_table
where a = 'b'
and metadata->>"$.country" is not null;

However, this query (the only difference is the order of where clauses) returns an error

select *
from some_table
where metadata->>"$.country" is not null
and a = 'b';

The error MySQL returns is

Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 0.

Why?

Solution

  • The value of the metdata column contains malformed JSON for at least one row in the table.

    We'd expect that entirely removing the a = 'b' condition, we would also observe the same error.

    I suspect that the difference in behavior is due to the order of operations being performed. When the a = 'b' condition is evaluated first, that excludes rows before the JSON_EXTRACT(metadata) expression is evaluated. Since the row doesn't match the a = 'b' condition, MySQL takes a shortcut, it doesn't evaluate the JSON_EXTRACT, it already knows the row is going to be excluded.

    When the comparisons are done in a different order, with the JSON_EXTRACT function executed first, the error is raised when the expression is evaluated for a rows with invalid JSON in metadata.

    Summary:

    There's at least one row in the table that has malformed JSON stored in metadata column.

    The difference in the observed behavior of the two queries is due to a different order of operations.

    Suggestion:

    Consider using the JSON_VALID function to identify rows with invalid values.

    Excerpt from MySQL Reference Manual

    JSON_EXTRACT

    Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.

    https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract

    JSON_VALID

    https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-valid