I am using the latest Influx Cloud build and I want to do a full outer join on 2 queries, but i get a runtime error…
import "join"
good = from(bucket: "cp")
|> range(start: 2020-01-01T01:00:00Z)
|> filter(fn: (r) => r["_measurement"] == "cycle" and r["_field"] == "result" and r["_value"] == true)
|> group(columns: ["tag1", "tag2", "tag3", "tag4", "tag5"])
|> rename(columns: { _value: "good"})
|> keep(columns: ["tag1", "tag2", "tag3", "tag4", "good", "tag5"])
|> count(column: "good")
bad = from(bucket: "cp")
|> range(start: 2020-01-01T01:00:00Z)
|> filter(fn: (r) => r["_measurement"] == "cycle" and r["_field"] == "result" and r["_value"] == false)
|> group(columns: ["tag1", "tag2", "tag3", "tag4", "tag5"])
|> rename(columns: { _value: "bads"})
|> keep(columns: ["tag1", "tag2", "tag3", "tag4", "bad", "tag5"])
|> count(column: "bad")
join.full(left: good, right: bad,
on: (l, r) => l.tag1 == r.tag1 and l.tag2 == r.tag2 and l.tag3 == r.tag3 and l.tag4 == r.tag4 and l.tag5 == r.tag5,
as: (l, r) => {
tag1 = if exists l.tag1 then l.tag1 else r.tag1
tag2 = if exists l.tag2 then l.tag2 else r.tag2
tag3 = if exists l.tag3 then l.tag3 else r.tag3
tag4 = if exists l.tag4 then l.tag4 else r.tag4
tag5 = if exists l.tag5 then l.tag5 else r.tag5
return {tag1: tag1, tag2: tag2, tag3: tag3, tag4: tag4, tag5: tag5, good: l.good, bad: r.bad}
})
With this query I got the following error message:
500 Internal Server Error
{
"code": "internal error",
"message": "runtime error @21:2-31:7: full: invalid message"
}
If I use generated data with arrays as in the example the previous join works perfectly.
import "join"
import "array"
left = array.from(rows: [
{tag1: "t111", tag2: "t111", tag3: "t111", tag4: "t111", tag5: "t111", good: 10},
{tag1: "t111", tag2: "t222", tag3: "t111", tag4: "t111", tag5: "t111", good: 11},
{tag1: "t111", tag2: "t111", tag3: "t333", tag4: "t111", tag5: "t111", good: 12},
{tag1: "t111", tag2: "t111", tag3: "t111", tag4: "t111", tag5: "t111", good: 13},
{tag1: "t111", tag2: "t111", tag3: "t111", tag4: "t444", tag5: "t111", good: 14},
])
right = array.from(rows: [
{tag1: "t111", tag2: "t111", tag3: "t111", tag4: "t111", tag5: "t111", bad: 10},
{tag1: "t111", tag2: "t222", tag3: "t111", tag4: "t111", tag5: "t111", bad: 11},
{tag1: "t111", tag2: "t111", tag3: "t111", tag4: "t111", tag5: "t555", bad: 12},
{tag1: "t111", tag2: "t333", tag3: "t111", tag4: "t111", tag5: "t111", bad: 13},
{tag1: "t666", tag2: "t111", tag3: "t111", tag4: "t111", tag5: "t111", bad: 14},
])
join.full(left: left, right: right,
on: (l, r) => l.tag1 == r.tag1 and l.tag2 == r.tag2 and l.tag3 == r.tag3 and l.tag4 == r.tag4 and l.tag5 == r.tag5,
as: (l, r) => {
tag1 = if exists l.tag1 then l.tag1 else r.tag1
tag2 = if exists l.tag2 then l.tag2 else r.tag2
tag3 = if exists l.tag3 then l.tag3 else r.tag3
tag4 = if exists l.tag4 then l.tag4 else r.tag4
tag5 = if exists l.tag5 then l.tag5 else r.tag5
return {tag1: tag1, tag2: tag2, tag3: tag3, tag4: tag4, tag5: tag5, good: l.good, bad: r.bad}
})
Returns this table as a result:
But if i run just my good (or bad) query it returns a table as well.
good = from(bucket: "cp")
|> range(start: 2020-01-01T01:00:00Z)
|> filter(fn: (r) => r["_measurement"] == "cycle" and r["_field"] == "result" and r["_value"] == true)
|> group(columns: ["tag1", "tag2", "tag3", "tag4", "tag5"])
|> rename(columns: { _value: "good"})
|> keep(columns: ["tag1", "tag2", "tag3", "tag4", "good", "tag5"])
|> count(column: "good")
My queries (good and bad) working perfectly individually, but I can’t join them. My join is working perfectly individually as well with the generated data.
I noticed that in the generated data the default “table” column only contains 0 values and in my queries it is an ascending number (0 - 12). This is the only difference I can find between the 2.
The same problem is happening when I try to use the join.left(), join.right() and join.tables() functions as well.
The only one is working with my queries is the simple join() function but this is an inner join and I need a full outer join.
Can you help me with this problem please?
Thank you in advance!
