_sql endpoint should return a row when creating a BLOB Table about crate

[chaudum]

Hi @megastef
Thanks for raising the issue.
I quickly tested, but found out that both CREATE TABLE and CREATE BLOB TABLE do return the rows property in the response json.

CREATE TABLE example

httpie POST http://127.0.0.1:4200/_sql stmt='create table foo (id integer, name string)'
HTTP/1.1 200 OK
Content-Length: 49
Content-Type: application/json; charset=UTF-8

{
    "cols": [],
    "duration": 142,
    "rowcount": 1,
    "rows": []
}

CREATE BLOB TABLE example

httpie POST http://127.0.0.1:4200/_sql stmt='create blob table my_blobs'
HTTP/1.1 200 OK
Content-Length: 49
Content-Type: application/json; charset=UTF-8

{
    "cols": [],
    "duration": 221,
    "rowcount": 1,
    "rows": []
}

Please let me know if I misunderstood the issue. What version of Crate to you use? Would be great if you could provide an example where it fails.

Best, Christian

[megastef]

I digged again into "CREATE BLOB TABLE":

We send this to the _sql endpoint:

{"stmt":"CREATE BLOB TABLE blobtest clustered into ? shards with (number_of_replicas=?)","args":[1,3]}

We get back this:

{ error: 
   { message: 'SQLActionException[line 1:43: mismatched input \'?\' expecting INTEGER_VALUE]',
     code: 4000 },
  rows: [] }

It happened in Version 0.40.4. Any advise? It looks the args are interpreted as string and not as number.
By the way: In this case "row count" is not set.

[mfelsche]

hi @megastef,
the actual issue here is that the clustered into clause does not accept a parameter.
Do you need a parameter to provide a value for the number of shards?

[megastef]

Ok, I fixed it by generating a string and not passing parameters in in "args" array. So the node.js driver provides this function including shards and replicas since 1.0.8. Maybe you close this one and its up to you if you open a new issue for it.

Actually it looks now like this:

exports.createBlobTable = function createBlobTable(tableName, replicas, shards, cbf) {
    var statement = "CREATE BLOB TABLE " + tableName + " clustered into " + shards + " shards with (number_of_replicas=" + replicas + ")";
    // [shards, replicas]
    executeSql(statement, [], cbf);
};

[mfussenegger]

Just merged a PR that will allow parameters in the clustered into clause: #1067

It will also be part of the next 0.41 bugfix release.

Is that row count isn't set still a problem or can I close the issue here?

[megastef]

Thanks. You can close it - I guess the inital failure was as we introduced the shards/replicas. We can update the driver in the future to use the args again for create blob table, when we got the possibility to query CRATE version number. @chaudum showed that rowcount is present in case of success.

[mfussenegger]

Ok great so I am closing this.

Btw you can get the crate version if you do a GET request on the root '/' endpoint.