[CT-2853] dbt show --output json converts integers to scale-1 numerics

[jtcohen6]

This is quite the throwback :) #3499

It looks like dbt show --output json is incorrectly serializing integer values to scale-1 numeric (decimal) values. That is, JSON number instead of JSON integer.

-- models/my_model.sql
select '0005' as stringnum, 'false' as stringbool, 10 as intfield, false as boolfield, '01T00000aabbccdd' as dtstring
union all
select '0006' as stringnum, 'true' as stringbool, 20 as intfield, true as boolfield, '01T00000aabbccde' as dtstring

$ dbt show -s my_model
12:51:50  Running with dbt=1.5.2
12:51:50  Registered adapter: postgres=1.5.2
12:51:50  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 308 macros, 0 operations, 1 seed file, 0 sources, 0 exposures, 0 metrics, 0 groups
12:51:50
12:51:50  Concurrency: 5 threads (target='dev')
12:51:50
12:51:50  Previewing node 'my_model':
| stringnum | stringbool | intfield | boolfield | dtstring         |
| --------- | ---------- | -------- | --------- | ---------------- |
| 0005      | false      |       10 |     False | 01T00000aabbccdd |
| 0006      | true       |       20 |      True | 01T00000aabbccde |

$ dbt show -s my_model --output json
12:55:15  Running with dbt=1.5.2
12:55:15  Registered adapter: postgres=1.5.2
12:55:15  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 308 macros, 0 operations, 1 seed file, 0 sources, 0 exposures, 0 metrics, 0 groups
12:55:15
12:55:15  Concurrency: 5 threads (target='dev')
12:55:15
12:55:15  {
  "node": "my_model",
  "show": [
    {
      "stringnum": "0005",
      "stringbool": "false",
      "intfield": 10.0,
      "boolfield": false,
      "dtstring": "01T00000aabbccdd"
    },
    {
      "stringnum": "0006",
      "stringbool": "true",
      "intfield": 20.0,
      "boolfield": true,
      "dtstring": "01T00000aabbccde"
    }
  ]
}

What's going on?

We're just using the agate library's type system and JSON serialization:

dbt-core/core/dbt/task/show.py

Lines 78 to 83 in eeb0570

	table = result.agate_table
	# Hack to get Agate table output as string
	output = io.StringIO()
	if self.args.output == "json":
	table.to_json(path=output)

This is being returned and saved as a decimal.Decimal type, rather than a Python int.

ipdb> table
<agate.table.Table object at 0x1078d1360>
ipdb> [list(row) for row in table]
[['0005', 'false', Decimal('10'), False, '01T00000aabbccdd'], ['0006', 'true', Decimal('20'), True, '01T00000aabbccde']]
ipdb> table.print_json()
[{"stringnum": "0005", "stringbool": "false", "intfield": 10.0, "boolfield": false, "dtstring": "01T00000aabbccdd"}, {"stringnum": "0006", "stringbool": "true", "intfield": 20.0, "boolfield": true, "dtstring": "01T00000aabbccde"}]
ipdb> type(table[0][2])
<class 'decimal.Decimal'>

This is not a regression

It's always been a problem for the dbt Cloud IDE. Here's query in dbt-core v1.4 (+ dbt-rpc):

And using dbt-core v1.6.0-rc1:

But it would be nice to finally fix this, especially now that the power to do so is fully within our grasp :)

[jtcohen6]

@dave-connors-3 beat me to it!! #8153