Repository: duckdb/duckdb (language: python)

Issue:
Invalid json output for cli -json
### What happens?

Example cli output:

```
┌──────────────────────┬───────────────┐
│          a           │       b       │
│        double        │ decimal(38,2) │
├──────────────────────┼───────────────┤
│ 0.013835631366566025 │     -29641.68 │
│ 0.014779952408172961 │     -10296.42 │
│ 0.003024374814074319 │      -5077.02 │
│ 0.010121712177079566 │      -2664.09 │
│ 0.034948037785923564 │       -795.75 │
│                  0.0 │       -531.77 │
│                  0.0 │       -437.56 │
│                  0.0 │        -75.62 │
│                 -nan │          0.00 │
│                 -nan │          0.00 │
├──────────────────────┴───────────────┤
│ 10 rows                    2 columns │
└──────────────────────────────────────┘

```
becomes with -json

```json
[{"a":0.013835631366566025,"b":-29641.68},
{"a":0.014779952408172961,"b":-10296.42},
{"a":0.003024374814074319,"b":-5077.02},
{"a":0.010121712177079566,"b":-2664.09},
{"a":0.034948037785923564,"b":-795.75},
{"a":0.0,"b":-531.77},
{"a":0.0,"b":-437.56},
{"a":0.0,"b":-75.62},
{"a":-nan,"b":0.00},
{"a":-nan,"b":0.00}]
```

E.g. php json parser does not accept "-nan" as a number.

https://datatracker.ietf.org/doc/html/rfc8259 says: Numeric values that cannot be represented in the grammar below (such
   as Infinity and NaN) are not permitted.

### To Reproduce

```bash
duckdb :memory: "select 0.0 / 0.0" -json
```

```text
[{"(0.0 / 0.0)":-nan}]
```

### OS:

Ubuntu

### DuckDB Version:

1.1.3

### DuckDB Client:

cli

### Hardware:

_No response_

### Full Name:

Thomas Bley

### Affiliation:

myself

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [ ] Yes, I have


Discussion / hints:
Also sorting is a big mess:

```
D SELECT cast(id as double) FROM (VALUES (1), (2), (0.0/0.0), (null)) cities(id) order by id asc;
┌────────────────────┐
│ CAST(id AS DOUBLE) │
│       double       │
├────────────────────┤
│                1.0 │
│                2.0 │
│               -nan │
│                    │
└────────────────────┘
D SELECT cast(id as double) FROM (VALUES (1), (2), (0.0/0.0), (null)) cities(id) order by id desc;
┌────────────────────┐
│ CAST(id AS DOUBLE) │
│       double       │
├────────────────────┤
│               -nan │
│                2.0 │
│                1.0 │
│                    │
└────────────────────┘
```
It's reasonable to expect that DuckDB's cast to JSON will return JSON, especially since JSON's `null` is available as an option.  For comparison:
```
$ jq -n '[-infinite, nan, infinite]'
[
  -1.7976931348623157e+308,
  null,
  1.7976931348623157e+308
]

$ gojq -n '[-infinite, nan, infinite]'
[
  -1.7976931348623157e+308,
  null,
  1.7976931348623157e+308
]

$ jaq -n '[-infinite, nan, infinite]'
[
  null,
  null,
  null
]
```
@thbley Thanks for reporting this. Indeed the JSON standard doesn't allow `nan` values. We'll see what we can come up with an alternative (such as using `null` for `nan`).

Sorting is not a "big mess", what you're seeing is the effect of the [`NULLS LAST` NULL order modifier](https://duckdb.org/docs/sql/query_syntax/orderby#null-order-modifier).

See e.g.:

```sql
SET null_order = 'NULLS_FIRST_ON_ASC_LAST_ON_DESC';
SELECT cast(id as double) FROM (VALUES (1), (2), (0.0/0.0), (null)) cities(id) order by id asc;
SELECT cast(id as double) FROM (VALUES (1), (2), (0.0/0.0), (null)) cities(id) order by id desc;
```

```text
D SET null_order = 'NULLS_FIRST_ON_ASC_LAST_ON_DESC';
D SELECT cast(id as double) FROM (VALUES (1), (2), (0.0/0.0), (null)) cities(id) order by id asc;
┌────────────────────┐
│ CAST(id AS DOUBLE) │
│       double       │
├────────────────────┤
│                    │
│                1.0 │
│                2.0 │
│                nan │
└────────────────────┘
D SELECT cast(id as double) FROM (VALUES (1), (2), (0.0/0.0), (null)) cities(id) order by id desc;
┌────────────────────┐
│ CAST(id AS DOUBLE) │
│       double       │
├────────────────────┤
│                nan │
│                2.0 │
│                1.0 │
│                    │
└────────────────────┘
```
> such as using null for nan

For those coming from MariaDB/MySQL, this would be preferred.

> SET null_order = 'NULLS_FIRST_ON_ASC_LAST_ON_DESC';

Thanks, I only read https://duckdb.org/docs/configuration/overview.html .
```
D SET null_order = 'NULLS_FIRST_ON_ASC_LAST_ON_DESC';
D SELECT cast(id as double) FROM (VALUES (1), (2), (0.0/0.0), (null)) cities(id) order by id asc;
│                    │
│                1.0 │
│                2.0 │
│                nan │
```

Maybe you could add a configuration setting for "nan_order" ?

Fix patch:
diff --git a/tools/shell/shell.cpp b/tools/shell/shell.cpp
index 4d1c3f1f8726..89c4a5b81b78 100644
--- a/tools/shell/shell.cpp
+++ b/tools/shell/shell.cpp
@@ -1621,19 +1621,57 @@ class DuckBoxRenderer : public duckdb::BaseResultRenderer {
 	bool highlight = true;
 };
 
+class TrashRenderer : public duckdb::BaseResultRenderer {
+public:
+	TrashRenderer() {
+	}
+
+	void RenderLayout(const string &) override {
+	}
+
+	void RenderColumnName(const string &) override {
+	}
+
+	void RenderType(const string &) override {
+	}
+
+	void RenderValue(const string &, const duckdb::LogicalType &) override {
+	}
+
+	void RenderNull(const string &, const duckdb::LogicalType &) override {
+	}
+
+	void RenderFooter(const string &) override {
+	}
+
+	void PrintText(const string &, HighlightElementType) {
+	}
+};
+
 /*
 ** Run a prepared statement
 */
-void ShellState::ExecutePreparedStatement(sqlite3_stmt *pStmt /* Statment to run */
-) {
+void ShellState::ExecutePreparedStatement(sqlite3_stmt *pStmt) {
 	if (cMode == RenderMode::DUCKBOX) {
-		size_t max_rows = outfile.empty() || outfile[0] == '|' ? this->max_rows : (size_t)-1;
-		size_t max_width = outfile.empty() || outfile[0] == '|' ? this->max_width : (size_t)-1;
+		size_t max_rows = this->max_rows;
+		size_t max_width = this->max_width;
+		if (!outfile.empty() && outfile[0] != '|') {
+			max_rows = (size_t)-1;
+			max_width = (size_t)-1;
+		}
+		if (!stdout_is_console) {
+			max_width = (size_t)-1;
+		}
 		DuckBoxRenderer renderer(*this, HighlightResults());
 		sqlite3_print_duckbox(pStmt, max_rows, max_width, nullValue.c_str(), columns, thousand_separator,
 		                      decimal_separator, &renderer);
 		return;
 	}
+	if (cMode == RenderMode::TRASH) {
+		TrashRenderer renderer;
+		sqlite3_print_duckbox(pStmt, 1, 80, "", false, '\0', '\0', &renderer);
+		return;
+	}
 
 	if (ShellRenderer::IsColumnar(cMode)) {
 		ExecutePreparedStatementColumnar(pStmt);
@@ -1663,27 +1701,25 @@ void ShellState::ExecutePreparedStatement(sqlite3_stmt *pStmt /* Statment to run
 
 	// iterate over the rows
 	do {
-		if (renderer) {
-			/* extract the data and data types */
-			for (int i = 0; i < nCol; i++) {
-				result.types[i] = sqlite3_column_type(pStmt, i);
-				if (result.types[i] == SQLITE_BLOB && cMode == RenderMode::INSERT) {
-					result.data[i] = "";
-				} else {
-					result.data[i] = (const char *)sqlite3_column_text(pStmt, i);
-				}
-				if (!result.data[i] && result.types[i] != SQLITE_NULL) {
-					// OOM
-					rc = SQLITE_NOMEM;
-					break;
-				}
+		/* extract the data and data types */
+		for (int i = 0; i < nCol; i++) {
+			result.types[i] = sqlite3_column_type(pStmt, i);
+			if (result.types[i] == SQLITE_BLOB && cMode == RenderMode::INSERT) {
+				result.data[i] = "";
+			} else {
+				result.data[i] = (const char *)sqlite3_column_text(pStmt, i);
+			}
+			if (!result.data[i] && result.types[i] != SQLITE_NULL) {
+				// OOM
+				rc = SQLITE_NOMEM;
+				break;
 			}
 		}
 
 		/* if data and types extracted successfully... */
 		if (SQLITE_ROW == rc) {
 			/* call the supplied callback with the result row data */
-			if (renderer && RenderRow(*renderer, result)) {
+			if (RenderRow(*renderer, result)) {
 				rc = SQLITE_ABORT;
 			} else {
 				rc = sqlite3_step(pStmt);
@@ -1691,9 +1727,7 @@ void ShellState::ExecutePreparedStatement(sqlite3_stmt *pStmt /* Statment to run
 		}
 	} while (SQLITE_ROW == rc);
 
-	if (renderer) {
-		renderer->RenderFooter(result);
-	}
+	renderer->RenderFooter(result);
 }
 
 /*
@@ -4739,11 +4773,15 @@ int SQLITE_CDECL wmain(int argc, wchar_t **wargv) {
 				azCmd[nCmd - 1] = z;
 			}
 		}
-		if (z[1] == '-')
+		if (z[1] == '-') {
 			z++;
+		}
 		if (strcmp(z, "-separator") == 0 || strcmp(z, "-nullvalue") == 0 || strcmp(z, "-newline") == 0 ||
-		    strcmp(z, "-cmd") == 0 || strcmp(z, "-c") == 0 || strcmp(z, "-s") == 0) {
+		    strcmp(z, "-cmd") == 0) {
 			(void)cmdline_option_value(argc, argv, ++i);
+		} else if (strcmp(z, "-c") == 0 || strcmp(z, "-s") == 0) {
+			(void)cmdline_option_value(argc, argv, ++i);
+			stdin_is_interactive = false;
 		} else if (strcmp(z, "-init") == 0) {
 			zInitFile = cmdline_option_value(argc, argv, ++i);
 		} else if (strcmp(z, "-batch") == 0) {
diff --git a/tools/shell/shell_highlight.cpp b/tools/shell/shell_highlight.cpp
index e17ce09344cc..7749aff3a5cb 100644
--- a/tools/shell/shell_highlight.cpp
+++ b/tools/shell/shell_highlight.cpp
@@ -19,7 +19,7 @@ static HighlightElement highlight_elements[] = {{"error", PrintColor::RED, Print
                                                 {"numeric_constant", PrintColor::YELLOW, PrintIntensity::STANDARD},
                                                 {"string_constant", PrintColor::YELLOW, PrintIntensity::STANDARD},
                                                 {"line_indicator", PrintColor::STANDARD, PrintIntensity::BOLD},
-                                                {"column_name", PrintColor::STANDARD, PrintIntensity::BOLD},
+                                                {"column_name", PrintColor::STANDARD, PrintIntensity::STANDARD},
                                                 {"column_type", PrintColor::STANDARD, PrintIntensity::STANDARD},
                                                 {"numeric_value", PrintColor::STANDARD, PrintIntensity::STANDARD},
                                                 {"string_value", PrintColor::STANDARD, PrintIntensity::STANDARD},
diff --git a/tools/shell/shell_renderer.cpp b/tools/shell/shell_renderer.cpp
index db2a914db074..d597583b8fd7 100644
--- a/tools/shell/shell_renderer.cpp
+++ b/tools/shell/shell_renderer.cpp
@@ -618,6 +618,10 @@ class ModeJsonRenderer : public RowRenderer {
 					state.Print("1e999");
 				} else if (strcmp(data[i], "-inf") == 0) {
 					state.Print("-1e999");
+				} else if (strcmp(data[i], "nan") == 0) {
+					state.Print("null");
+				} else if (strcmp(data[i], "-nan") == 0) {
+					state.Print("null");
 				} else {
 					state.Print(data[i]);
 				}
@@ -830,9 +834,6 @@ unique_ptr<RowRenderer> ShellState::GetRowRenderer(RenderMode mode) {
 		return unique_ptr<RowRenderer>(new ModeSemiRenderer(*this));
 	case RenderMode::PRETTY:
 		return unique_ptr<RowRenderer>(new ModePrettyRenderer(*this));
-	case RenderMode::TRASH:
-		// no renderer
-		return nullptr;
 	default:
 		throw std::runtime_error("Unsupported mode for GetRowRenderer");
 	}


Test patch:
diff --git a/tools/shell/tests/test_highlighting.py b/tools/shell/tests/test_highlighting.py
index 2d37f64c8bb8..719589b73496 100644
--- a/tools/shell/tests/test_highlighting.py
+++ b/tools/shell/tests/test_highlighting.py
@@ -14,11 +14,10 @@ def test_highlight_column_header(shell):
     test = (
         ShellTest(shell)
         .statement(".highlight_results on")
-        .statement(lineitem_ddl)
-        .statement('select * from lineitem;')
+        .statement('select NULL AS r;')
     )
     result = test.run()
-    result.check_stdout('\x1b[1ml_comment\x1b[0m')
+    result.check_stdout('\x1b[90mNULL\x1b[0m')
 @pytest.mark.skipif(os.name == 'nt', reason="Windows highlighting does not use shell escapes")
 def test_custom_highlight(shell):
     test = (
diff --git a/tools/shell/tests/test_shell_basics.py b/tools/shell/tests/test_shell_basics.py
index 75ef600668fd..af29161ffa5e 100644
--- a/tools/shell/tests/test_shell_basics.py
+++ b/tools/shell/tests/test_shell_basics.py
@@ -593,12 +593,11 @@ def test_mode_json_infinity(shell):
     test = (
         ShellTest(shell)
         .statement(".mode json")
-        .statement("SELECT 'inf'::DOUBLE AS inf, '-inf'::DOUBLE AS ninf, 'nan'::DOUBLE AS nan;")
+        .statement("SELECT 'inf'::DOUBLE AS inf, '-inf'::DOUBLE AS ninf, 'nan'::DOUBLE AS nan, '-nan'::DOUBLE AS nnan;")
     )
     result = test.run()
-    result.check_stdout('[{"inf":1e999,"ninf":-1e999,"nan":nan}]')
+    result.check_stdout('[{"inf":1e999,"ninf":-1e999,"nan":null,"nnan":null}]')
 
-# Original comment: FIXME sqlite3_column_blob
 def test_mode_insert(shell):
     test = (
         ShellTest(shell)