InfluxDB V2 flux query to find last record is wrong

[VandaagEnDan]

InfluxDB V2 flux query to find last record is wrong. You get a error code and uploader stops. In influxDB_v2_uploader.cpp

Line 49: reqData.printf_P(PSTR("%s\n (r[\"_measurement\"] == \"%s\""), connectOr.c_str(), varStr(_measurement, script).c_str());

Should be: reqData.printf_P(PSTR("%s\n r._measurement == \"%s\""), connectOr.c_str(), varStr(_measurement, script).c_str());

Now code results in this query:

from(bucket: "bs_energy")
  |> range(start: 1635523200, stop: 1635786020)
  |> filter(fn: (r) =>
    (r["_measurement"] == "L1_Volts" or
    (r["_measurement"] == "L2_Volts" or
    (r["_measurement"] == "L3_Volts" or
    (r["_measurement"] == "Q2_L1" or
    (r["_measurement"] == "Q2_Total")
  |> last()
  |> map(fn: (r) => ({_measurement: r._measurement, _time: (uint(v:r._time)) / uint(v:1000000000)}))
  |> sort (columns: ["_time"], desc: true)

Correct query would be:

from(bucket: "bs_energy")
  |> range(start: 1635523200, stop: 1635786020)
  |> filter(fn: (r) =>
    r._measurement == "L1_Volts" or
    r._measurement == "L2_Volts" or
    r._measurement == "L3_Volts" or
    r._measurement == "Q2_L1" or
    r._measurement == "Q2_Total"
  ) 
  |> last()
  |> map(fn: (r) => ({_measurement: r._measurement, _time: (uint(v:r._time)) / uint(v:1000000000)}))
  |> sort (columns: ["_time"], desc: true)

See https://docs.influxdata.com/flux/v0.x/stdlib/universe/filter/#filter-based-on-influxdb-measurement-field-and-tag

[nfrankish]

Im seeing this, which i assume is related 11/07/21 09:12:08 influxDB_v1: Start posting at 10/29/21 19:37:50 11/07/21 09:14:30 influxDB_v2: stopped, Last post 01/01/70 10:00:00 11/07/21 09:23:38 influxDB_v2: stopped, Last post 01/01/70 10:00:00

[boblemaire]

@VandaagEnDan,

I just noticed this issue today. The problem was next on my list, so very timely. I looked into this somewhat and of course you are right about the r._measurement vs r["_measurement"]. If you look further down, and as per the docs that you referenced, the tags are also not properly referenced and the _field key is not properly referenced. There is also a problem with the _field and closing parenthesis when there are no tag fields. These are all now fixed and will appear in release 02_07_04 over the next week or so.

The whole query code is now:

 while(script)
    {
        trace(T_influx2,21);
        reqData.printf_P(PSTR("%s\n    (r._measurement == \"%s\""), connectOr.c_str(), varStr(_measurement, script).c_str());
        if(_tagSet){
            trace(T_influx2, 22);
            influxTag* tag = _tagSet;
            while(tag){
                reqData.printf_P(PSTR(" and r.%s == \"%s\""), tag->key, varStr(tag->value, script).c_str());
                tag = tag->next;
            }
        }
        reqData.printf_P(PSTR(" and r._field == \"%s\")"), varStr(_fieldKey, script).c_str());
        connectOr = " or";
        script = script->next();
    }
    trace(T_influx2,20);
    reqData.print(F(")\n  |> last()\n  |> map(fn: (r) => ({_measurement: r._measurement, _time: (uint(v:r._time)) / uint(v:1000000000)}))\n"));
    reqData.print(F("  |> sort (columns: [\"_time\"], desc: true)\n"));

Thanks for the flux lesson.

[boblemaire]

@nfrankish,

Not enough info to say definitively, but probably one of the fixes due out in 02_07_04 will fix it. There is more about this in the forum. #