N1Q1 Query adds .0 for numbers with length >=7

SQL++
1

When we select a number which is of length 7 digits, couchbase converts it to a Float. In the below case, we get the result as 1000000.0 instead of 1000000.

Code Sample

using (var bucket = _cluster.OpenBucket())
{
const string query = “SELECT 1000000 FROM DEFAULT”;
var result = bucket.Query(query);
console.writeline(result.rows[1]);
}
_cluster.Dispose();

Couchbase-Error.jpg1440×900 270 KB

2

Hi @srbhaski,

I noticed that you are using Query<dynamic>. What if you define a type (with an int or long or whatever you are trying to get)?

public class MyType { public int MyValue {get;set;} }

Then:

var result = bucket.Query<MyType>("select 1000000 as MyValue from `default`")
3

Hi Matthews,

Thanks for your reply. The problem is our query returns a JObject and in that we have a property of this 7 digit value which is getting converted to float.

Also I noted that
var result1 = bucket.Query(“SELECT 9999999 as MyValue FROM default”) - fails
var result1 = bucket.Query(“SELECT 9999999 as MyValue FROM default”) - Works

If I try
var result1 = bucket.Query(“SELECT 9999999 as MyValue FROM default”) - it returns with .0

4

Hi @srbhaski, what is the couchbase version? Do you see similar behavior with web-console/query-tab or cbq too?? We fixed a rounding issues with large ints (>15 digits or so) in 4.5.1. Do you want to try that build, or wait for 4.5.1 release?

5

Hi Prasad,

We are using Couchbase.NetClient Version 2.2.1.0, RunTime Version - v4.0.30319. Couchbase Version: 4.0.0-4051 Community Edition (build-4051)

We do not see this behaviour in Query-tab or cbq. Its only when we connect from .Net, we see this issue.

6

There was a problem with large integers getting rounded because of conversion to floats in the query engine. I fixed this recently, and the fix should be available in 4.5.1.

In the current codebase, the query “SELECT 1000000 FROM default” returns a proper value 1000000, without adding a period.

Incidentally, this query only returns one row per document in the default bucket. If you always want just a single value, “SELECT 1000000” is legal N1QL and works.

curl http://localhost:8093/query/service -d "statement=select 1000000 from default"
{
    "requestID": "af9e0d9a-f548-4061-98ae-ac5f4b23091f",
    "signature": {
        "$1": "number"
    },
    "results": [
        {
            "$1": 1000000
        },
        {
            "$1": 1000000
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.138596ms",
        "executionTime": "1.109752ms",
        "resultCount": 2,
        "resultSize": 74
    }
}

Can you wait for 4.5.1, which will have a fix?

7

Hi Johan,

Thanks for the updates. Waiting for 4.5.1.

8

What is the diff??
var result1 = bucket.Query(“SELECT 9999999 as MyValue FROM default”) - fails
var result1 = bucket.Query(“SELECT 9999999 as MyValue FROM default”) - Works

9

I don’t see any difference. Maybe there is something in the formatting that got eaten up as you were posting?

10

@srbhaski -

Can you use Fiddler or Charles or some other HTTP debugger and get the response from the server and post it?

11

Hi Mathew, Prasad,

My post was marked for spam filter and when they released it, I think it got messed up. Anyhow what I meant was when I make the MyValue property as Int32, it fails, when I make it as Int64 it works and when I make it as dynamic, it works again as it converts it to Int64 automatically.

I have already got the answer from Johan for my issue. This one seems to be fixed in 4.5.1.

Thanks for your help.