[0.9.3-rc2] Sum is returning 0

[buro1983]

Sum is returning 0 on 0.9.3-rc2 release. I am not able to understand why. Is there any problem with the query?

I am trying to sum up downstream values but I am getting 0

  select Downstream  from Usage where time = '2015-08-23 01:07:08'
  name: Usage
  -----------
  time                    Downstream
  2015-08-23T01:07:08Z    986
  2015-08-23T01:07:08Z    241
  2015-08-23T01:07:08Z    0
  2015-08-23T01:07:08Z    276

  select sum(Downstream)  from Usage where time = '2015-08-23 01:07:08'
  name: Usage
  -----------
  time                    sum
  2015-08-23T01:07:08Z    0

[buro1983]

I am facing same problem with 0.9.2. also. I I have tried with group by clause also. is there anything that I am missing while querying data?

[pauldix]

Can you try that query with a time range? There might be a bug with aggregates against an exact time. Like:

select sum(Downstream)  from Usage where time >= '2015-07-23 01:07:08' and time < '2015-07-23 01:07:09'

[buro1983]

I have tried with time range, now() (with and without group by) but no luck. I was using 0.8.8. I just started migrating to 0.9. Please let me know if it is a bug or query is wrong.

[pauldix]

can you show the exact query and CLI output for the range queries you did? Also, what shows up if you just do the sum without specifying any time?

[buro1983]

  1. select sum(Downstream)  from Usage where time >= '2015-08-23 00:07:08' and time < '2015-08-23 18:07:09' group by LinkName
  name: Usage
  tags: LinkName=Link1
  time                    sum
  ----                    ---
  2015-08-23T00:07:08Z    0

  2. select sum(Downstream)  from Usage where time > '2015-08-23 00:07:08' and time < '2015-08-23 18:07:09' group by LinkName
  name: Usage
  tags: LinkName=Link1
  time                            sum
  ----                            ---
  2015-08-23T00:07:08.000000001Z  0

  3. select sum(Downstream)  from Usage group by LinkName
  name: Usage
  tags: LinkName=Link1
  time                    sum
  ----                    ---
  1970-01-01T00:00:00Z    0

  4.  select sum(Downstream)  from Usage
  name: Usage
  -----------
  time                    sum
  1970-01-01T00:00:00Z    0

There are values for Downstream,

  1. select count(Downstream)  from Usage
  name: Usage
  -----------
  time                    count
  1970-01-01T00:00:00Z    238251

  2. select Downstream  from Usage limit 10
  name: Usage
  -----------
  time                    Downstream
  2015-08-23T09:55:23Z    91
  2015-08-23T09:55:23Z    70
  2015-08-23T09:55:23Z    930
  2015-08-23T09:55:23Z    986
  2015-08-23T09:55:23Z    0
  2015-08-23T09:55:23Z    736
  2015-08-23T09:55:23Z    62
  2015-08-23T09:55:23Z    155
  2015-08-23T09:55:23Z    256
  2015-08-23T09:55:23Z    23157

[buro1983]

I have run these queries on 0.9.2 release

[dgnorton]

I tried several things to reproduce but no luck so far:

• start with 0.9.3rc2, create new db, write 4 points, select sum(Downstream) from Usage
• start with 0.9.2, create new db, write a few points, switch to 0.9.3rc2, write a point, run same query
• start with 0.9.1, create new db, write points, 0.9.2 write point, 0.9.3rc2 write point, run same query

@buro1983 can you reproduce this on a new DB? If not, maybe it's a bug related to moving from one version to the next that I didn't hit in my simple tests. Any chance you could share your DB with us? If so, how large is it?

[buro1983]

raft.db will work for you?

[dgnorton]

@buro1983 are you running a cluster or single node?

We would need the contents of the data directory. In your influxdb config file...

[data]
  dir = "/home/dgnorton/.influxdb/data"

Can you put it on a Google drive (or similar) and email the link to support@influxdb.com and ref this issue or post it here?

[pauldix]

Should probably just grab the whole .influxdb directory. You'll need the WAL too

[buro1983]

I have sent DB details to support@influxdb.com

[beckettsean]

@buro1983 is it possible you have been writing the values as strings and not numbers? The SUM of a string is always zero, and there's no easy way to tell from the output if it's stored as a number or a string.

Can you share a sample write statement?

[buro1983]

  client.Point {
       Measurement: "Usage",
       Tags: map[string]string{
             "LinkName": linkName,
             "ApplicationType": apptype,
             "Subscriber": subName,
       },
       Fields: map[string]interface{}{
            "Upstream": metric.UB,
            "Downstream": metric.DB,
        },
        Tme: ts,
         Precision: "s",
  }

UB and DB is declared as unit32. I have faced is problem on 0.9.3-rc2 then down graded to 0.9.2, I am not sure if it is a issue or problem from my side. But it did not work on both the versions.

[beckettsean]

@buro1983 The UB and DB variables may be numbers in your code, but if they are double-quoted in the actual write submission they will be cast as strings by the database. This would be true for all 0.9 versions of the database.

How are you writing points? A library, direct HTTP API calls? JSON protocol or line protocol?

[buro1983]

I am referring to ExampleClient_Write in https://github.com/influxdb/influxdb/blob/master/client/example_test.go

I am writing everything into client.BatchPoints then using conn.Write() to write into DB. My code is same as mentioned in example.

[buro1983]

Influx log file contains traces like POST /write?consistency=&db=cs&precision=&rp=default HTTP/1.1 204 0 - InfluxDBClient 6680e80a-497d-11e5-8302-000000000000 1.220006ms
So I am not able to figure out the body part of POST message.

[corylanou]

@pauldix I just realized, the dates are identical. That should be impossible right?

 select Downstream  from Usage where time = '2015-08-23 01:07:08'
  name: Usage
  -----------
  time                    Downstream
  2015-08-23T01:07:08Z    986
  2015-08-23T01:07:08Z    241
  2015-08-23T01:07:08Z    0
  2015-08-23T01:07:08Z    276

[corylanou]

nm, realized they are different series by doing a select *

[corylanou]

Ok, found the problem. The data is actually being written as a string. I added a panic that will fire if it isn't a valid type and got this result:

panic: unknown data type string - 0 during MapSum

While this isn't a bug specifically because all the data is a string, we need to do a better job of catching and erring out the queries to end users. I opened an issue to here: #3818

[buro1983]

Wait, I am using Influx client APIs to write into DB. My values are in integer then how it is getting stored as string? If it is expected then what I need to do to make sure that I am inserting integer values into DB instead of string.

    Fields: map[string]interface{}{
        "Upstream": metric.UB,
        "Downstream": metric.DB,
    }

Still it is not clear how interface.(type) is getting evaluated as string, where as actual value is unit32.
If you read value from Fields and do type checking then you will never get string. Please suggest me how do I insert integer/float values into DB using client api.

[corylanou]

Can you show me the definition for the metric struct?

[buro1983]

  type UsageRequest struct {
      EventTimestamp *diam.AVP      `avp:"Event-Timestamp"`
      SessionId      *diam.AVP      `avp:"Session-Id"`
      LinkName       string         `avp:"Link-Name"`
      SubscriberName string         `avp:"Subscriber-Name"`
      UsageMetric    []*UsageMetric `avp:"Usage-Metric"`
  }

  type UsageMetric struct {
     ID int    `avp:"Category-Id"`
     UB uint32 `avp:"Up-Bytes"`
     DB uint32 `avp:"Down-Bytes"`
  }

Whole msg is getting parsed into this structure, after that I am storing into DB.

[corylanou]

Ok, there is a bug in the way we decode a uint32. I referenced the issue above and we should have a fix for it soon. You are not doing anything wrong on your end.