Grafana with Athena Query converts my NULLs to zeros

I use Grafana Cloud with Athena plugin - and my query somehow converts NULLs to zeros (0). When I run the same query in Athena I get the NULL values as expected, but in Grafana they are COALESCEd for me.

  • What Grafana version and what operating system are you using?

Grafana Cloud
Plugins: Amazon Athena v.1.0.1

  • What are you trying to achieve?

Disconnect dots in a time series → retrieve NULL values from athena.

  • How are you trying to achieve it?

My query selects a “value” field which contains NULL

  • What happened?

The data retrieved converts my NULL to zero (0)

  • What did you expect to happen?

That the NULL will remain NULL

  • Can you copy/paste the configuration(s) that you are having problems with?

From Athena:

SELECT COUNT(*) FROM wall.wall_data WHERE value IS NULL # I get the records I expect

Highlights from the query inspector JSON:

{
  "request": {
    "url": "api/ds/query",
    "method": "POST",
    "data": {
      "queries": [
        {
          "column": "value",
          "connectionArgs": {
            "catalog": "__default",
            "database": "__default",
            "region": "__default"
          },
          "datasource": {
            "type": "grafana-athena-datasource"
          },
          "format": 0,
          "hide": false,
          "rawSQL": "SELECT date_parse(\"timestamp-utc\", '%Y-%m-%d %H:%i:%s UTC') AS dt,\r\n\"home-name\", \"wall-name\", \"sensor-name\", \r\nvalue \r\nFROM wall_data \r\nWHERE \"measurement-type\"='temp_c'\r\nORDER BY 1 ASC;",
          "refId": "Temperature",
          "table": "wall_data",
          "datasourceId": 20,
          "intervalMs": 30000,
          "maxDataPoints": 848
        },
      ],
      "range": {
        "from": "2021-12-26T03:05:46.200Z",
        "to": "2021-12-26T09:05:46.200Z",
        "raw": {
          "from": "now-6h",
          "to": "now"
        }
      },
      "from": "1640487946200",
      "to": "1640509546200"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "Temperature": {
        "frames": [
          {
            "schema": {
              "name": "Temperature",
              "refId": "Temperature",
              "meta": {
                "type": "timeseries-wide",
                "executedQueryString": "SELECT date_parse(\"timestamp-utc\", '%Y-%m-%d %H:%i:%s UTC') AS dt,\r\n\"home-name\", \"wall-name\", \"sensor-name\", \r\nvalue \r\nFROM wall_data \r\nWHERE \"measurement-type\"='temp_c'\r\nORDER BY 1 ASC;"
              },
              "fields": [
                {
                  "name": "dt",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time"
                  }
                },
                {
                  "name": "value",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float32",
                    "nullable": true
                  },
                  "labels": {
                    "home-name": "erez-home",
                    "sensor-name": "air-in",
                    "wall-name": "research-wall"
                  }
                },
                {
                  "name": "value",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float32",
                    "nullable": true
                  },
                  "labels": {
                    "home-name": "erez-home",
                    "sensor-name": "air-out",
                    "wall-name": "research-wall"
                  }
                },
                {
                  "name": "value",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float32",
                    "nullable": true
                  },
                  "labels": {
                    "home-name": "erez-home",
                    "sensor-name": "wall-down-deeper",
                    "wall-name": "research-wall"
                  }
                },
                {
                  "name": "value",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float32",
                    "nullable": true
                  },
                  "labels": {
                    "home-name": "erez-home",
                    "sensor-name": "wall-up-less-deep",
                    "wall-name": "research-wall"
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1639942092000,
                  1639942107000,
                  1639942201000,
                  1639942802000,
                  1640501941000,
                  1640502001000,
                  1640502602000,
                  1640503201000,
                  1640503802000,
                  1640504401000,
                  1640505002000,
                  1640505602000,
                  1640506202000,
                  1640506801000,
                  1640507401000,
                  1640508002000,
                  1640508601000,
                  1640509202000
                ],
                [
                  12.375,
                  12.348,
                  12.332,
                  12.319,
                  12.319,
                  12.292,
                  12.332,
                  0,  // <<<< Should have been NULL
                  8.7,
                  9.039,
                  9.475,
                  9.8,
                  10.051,
                  10.278,
                  10.489,
                  10.727,
                  10.967,
                  11.178,
                  11.347,
                  11.485,
                  11.656
                ]
              ]
            }
          }
        ],
        "refId": "Temperature"
      }
    }
  }
}
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No

  • Did you follow any online instructions? If so, what is the URL?

I couldn’t find any. Most online resources are about the opposite problem i.e. how to COALESCE - I just want my NULLs to stay NULLs.

1 Like

I have the same problem :frowning: