`dolt sql -r json` when connected to a running server returns numeric values as strings

[PavelSafronov]

Setup repo:

rm -rf db
mkdir db
cd db
dolt init
dolt sql << SQL
CREATE TABLE test (a int primary key,v LONGTEXT);
insert into test values (1, "{""key"": ""value""}");
insert into test values (2, """Hello""");
SQL

Query the db directly:

dolt --verbose-engine-setup --data-dir db sql -q "select * from test" -r json
verbose: starting local mode
{"rows": [{"a":1,"v":"{\"key\": \"value\"}"},{"a":2,"v":"\"Hello\""}]}

Start dolt sql-server in the DB directory:

dolt sql-server

Query the server:

dolt --verbose-engine-setup sql -q "select * from test" -r json
verbose: starting remote mode
{"rows": [{"a":"1","v":"{\"key\": \"value\"}"},{"a":"2","v":"\"Hello\""}]}

Notice that in the "remote mode" output, when we are querying sql-server, the value of a is "1", but it should be 1, as it appears in the "local mode" output.

[NathanGabrielson]

I spend a day poking around this issue and found some of the causes. The first is the different row iterators. When running the command without a server you get a TrackedRowIter. With a server, however, you get sql.RowIter. This means that when you get to the line: https://github.com/dolthub/dolt/blob/426950a26167000e44366eaf05758caa29e9d6b6/go/cmd/dolt/commands/engine/sql_print.go#L181 they each have their own next() functions, which each call different children functions, leading to differing print results.

There's also a couple other oddities. The schema setup when running the command with a server active looks like following: https://github.com/dolthub/dolt/blob/426950a26167000e44366eaf05758caa29e9d6b6/go/cmd/dolt/commands/sqlserver/queryist_utils.go#L118-L127 First, when we set iRow[i] = &vRow[i], we are changing the type of the former from an interface{} to a string. Similarly, we define the schema with Type: types.LongText, which I believe later down the line once again converts everything to a string.

Even if you were to fix these issues, I think this line would still cause problems https://github.com/dolthub/dolt/blob/426950a26167000e44366eaf05758caa29e9d6b6/go/libraries/doltcore/table/typed/json/writer.go#L234 The marshalling, as far as I can tell, converts non-string values into strings, and we can't change the function.

You could fix this by merging the row iterators, but that would probably be more work than it's worth. I think some sort of smart conversion near the end of the process might be best?