Magic params flag fails with negative numbers

[teebr]

Environment details

Colab (Ubuntu 18.04 LTS, Python 3.6.9, pip 19.3.1, bigquery 1.21.0)

Issue/ Example

For a parameterised query, the magic fails to parse negative numbers.

For example, this parameter dict:

params = {
    "threshold": -5.0,
    "min_year": 2000
}

and this query:

%%bigquery  --project my-project-name --params $params
SELECT
    year, MIN(mean_temp) as min, COUNTIF(snow) / COUNT(snow) * 100 AS pc_snowing
FROM
     `bigquery-public-data.samples.gsod`
WHERE mean_temp < @threshold
AND year >= @min_year
GROUP BY year
ORDER BY year

runs fine if threshold is >= 0, but if it's negative, the Magic gets an exception:

UsageError: unrecognized arguments: -5.0, 'min_year': 2000}

This happens whether it's a dictionary or a JSON string, and happens with ints as well as floats.

[teebr]

It seems this happens in the IPython's magic_arguments call to arg_split. That uses shlex, and I think that interprets whitespace followed by a hyphen as a new argument. Anyway, dumping the dictionary to JSON with no whitespace does work:

pj = json.dumps(params, separators=(',', ':'))
...
%%bigquery  --project my-project-name --params $pj

[plamut]

@teebr Interesting, thanks for the report!

I was able to reproduce it, as well as confirming that the workaround works. We now need to see if there's an easy fix to that.

[plamut]

This doesn't seem easily solvable, unfortunately. Using the following minimal example, for instance:

>>> params = {"num": -17}
%%bigquery --params $params
SELECT @num as num

passes the following line contents to the BigQuery's _cell_magic() function:

>>> line
"--params {'num': -17}"

which is tokenized in IPython's MagicArgumentParser.parse_argstring() method as follows:

>>> argv
['--params', '{"num":', '-17}']

The tokenizer can't really know that it should treat the tokens after "--params" as a single entity representing a Python dict, thus it's understandable why the above fails - -17} is treated as a yet another argument, and not as part of the value of another argument.

BigQuery's magic actually has to manually reconstruct the dict from these fragments, which is a bit of a hack that deals with the symptom:

ast.literal_eval("".join(args.params))

In order to force the parser to treat the params dict as a single entity, the dict should be passed in as a JSON string as @teebr noted, and this limitation should be documented in bigquery cell magic docs.

Even if not perfect, this solution probably has the best benefit/cost ratio.