Prepared Query Bug

[Giraldo Rosales]

Sending a prepared query with a hash value. ie:
INSERT INTO Users set id=:userid

The object value sent is a JSON object:
{"userID":6ce3ef04cb2cb750d4ce0d0f9648066f"}

Whenever the hash begins with a number, the value is converted to a number. If the hash begins with a letter, ie. "a6ce..." Then it stores as a string correctly. Sometimes it even stores as a date.

The value should only be converted into a number if not wrapped in quotes and contains only numbers. Or if a field is set to numeric. Am I right?

[Giraldo Rosales]

I believe OrientDB is setting it as a BigDecimal (https://github.com/orientechnologies/orientdb/wiki/Network-Binary-Protocol#wiki-record-format under numbers).

The regex in OrientDB used to recognize the "c" after the "6" is making it look like a number is the value. And in my case it is a hash.

OrientDB should look at the whole value instead of a number and the first character after.

Hopefully it is an easy fix. We are using the latest version from git.

Thanks!!

[Luca Garulli]

Hi,
Why the value isn't between quotes?

Sent from Mobile device

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Giraldo Rosales]

The value is wrapped with quotes. It is with a SQL INSERT command. Haven't tested with UPDATE.

Try it with the exact value I sent. I was able to recreate each time.

When I insert the hash, only "6" is actually inserted, not the full hash.

I believe it is the formatting of the BigDecimal. Since this example starts off with "6c...". It only inserts "6".

You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/KYwZrXaL_LE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

[Giraldo Rosales]

Typo in my initial email. The actual object sent is:

{"userID":"6ce3ef04cb2cb750d4ce0d0f9648066f"}

I'm trying to find the number formatting in the source code. I think if it is temporarily turned off, it will fix the insert.

As a permanent fix, if the number formatting is fixed it should recognize a value of "6c" is different than "6ce3ef04cb2cb750d4ce0d0f9648066f"

Now that I think of it, the numbers shouldn't be formatted unless a schema is specified and a number field is set.

What if I wanted to insert a string of, "6c"? OrientDB will insert it as "6" even though I specified the field as string. Or what if I don't even specify a string field property, a schema-less case? I wouldn't want OrientDB to format "6c" as a number if I actually wanted it as a string value (for whatever reason).

Thanks Luca!

[Luca Garulli]

So,

can you provide me a query to execute against GratefulDeadConcerts database that demonstrate the problem? I tried:

CREATE CLASS Users

INSERT INTO Users set id="6ce3ef04cb2cb750d4ce0d0f9648066f"

Then:

select from Users and this is the (correct) result

@rid	@version	@class	id
#14:0	1	Users	6ce3ef04cb2cb750d4ce0d0f9648066f

Lvc@

[Giraldo Rosales]

Sure. 

It works perfectly when placing the value in the SQL directly. Where it inserts it incorrectly is when used with a prepared query. So in the GratefulDeadConcerts database, try:

INSERT INTO V SET id=:id

Then add the params:

{"id":"6ce3ef04cb2cb750d4ce0d0f9648066f"}

Then get the resulting record:

SELECT * FROM V WHERE id ="6"

You'll get back the incorrect record. Which is why I'm assuming it has something to do with the parsing of the prepared query.

[bck...@gmail.com]

Giraldo Rosales wrote:

Sure. 

It works perfectly when placing the value in the SQL directly. Where it inserts it incorrectly is when used with a prepared query. So in the GratefulDeadConcerts database, try:

INSERT INTO V SET id=:id

Then add the params:

{"id":"6ce3ef04cb2cb750d4ce0d0f9648066f"}

Then get the resulting record:

SELECT * FROM V WHERE id ="6"

You'll get back the incorrect record. Which is why I'm assuming it has something to do with the parsing of the prepared query.

When I try with the java driver in 1.6.4, it works:

    ODatabaseDocument db = ODatabaseDocumentPool.global().acquire("remote:localhost/test", "admin", "admin");

    Map<String,Object> parms = new HashMap<>();
    parms.put("hash", "6ce3ef04cb2cb750d4ce0d0f9648066f");

    Object result = db.command(new OCommandSQL("insert into test set hash=:hash")).execute(parms);
    System.out.println(result);

And the test class is created with "create class test".

regards,
Finn

[Giraldo Rosales]

Weird. I tried using version 1.6.3 and the latest and both had the same results, both inserting an incorrect value of "6" instead of "6ce3ef04cb2cb750d4ce0d0f9648066f". Is there a way to test this out via a REST call?

We are sending the following to the binary call, REQUEST_COMMAND:

- operation (bytes) = 41

- session id (int) 

- mode (bytes) = s

- class name (string) = com.orientechnologies.orient.core.sql.OCommandSQL

- sql query (string) = INSERT INTO V SET id=:id

- params present (boolean) - true

- params (string) = params:{"id":"6ce3ef04cb2cb750d4ce0d0f9648066f"}

- composite key params present (boolean) - false

Is there anything wrong with what is being sent? Not sure how it works in the Java API but not via a socket call in NodeJS. 

[Giraldo Rosales]

I can get it working if I try:

params:{"id":"\"6ce3ef04cb2cb750d4ce0d0f9648066f\""}

[Giraldo Rosales]

Seems like this must only be done with md5 hashes. If a RID or date is send like this, it will cause an error.

This causes an error to insert into a date field:

params:{"date":"\"2013-01-30 19:24\""}

This causes an error to insert into a LINK field (assuming #9:5 is a valid record):

params:{"link":"\"#9:5\""}

The following string field works:

params:{"text":"\"Her name was \"Sarah\".\""}

params:{"text":"Her name was \"Sarah\"."}

(both field values are inserted as: Her name was "Sarah")

The following string field works for a md5 hash:

params:{"hash":"\"6ce3ef04cb2cb750d4ce0d0f9648066f\""}

(field value is inserted as: 6ce3ef04cb2cb750d4ce0d0f9648066f)

The following string field works but inserted incorrectly:

params:{"hash":"6ce3ef04cb2cb750d4ce0d0f9648066f"}

(field value is inserted as: 6)

Not sure why there is an inconsistency. Finn has demonstrated the Java API is working. Not sure if the strict type casting is helping there or not but sending the params for a prepared query via NodeJS socket and buffers seem to cause some inconsistencies. Am able to do a regex to catch the md5 scenario and add additional quotes. But not sure if other scenarios exist.

I just want to confirm this is an issue with NodeJS and not with the OrientDB binary. We are using the Network Binary Protocol. Not sure if there is another way to check this out.

Thanks!

[bck...@gmail.com]

Giraldo Rosales wrote:

I can get it working if I try:

params:{"id":"\"6ce3ef04cb2cb750d4ce0d0f9648066f\""}

A bit strange, but the code at

https://github.com/orientechnologies/orientdb/blob/25d95a6d10ab90cb492440fc0c4fe67d49eec053/core/src/main/java/com/orientechnologies/orient/core/command/OCommandRequestTextAbstract.java#L107

does some special handling of String params. First adding quotes in simpleValueToStream and then putting the qouted value into a Document that is serialized with the normal Document rules from

https://github.com/orientechnologies/orientdb/wiki/Network-Binary-Protocol#wiki-Record_format

I agree that is surprising.

regards,
Finn

[Giraldo Rosales]

Thanks Finn, for giving us a good place to start! Will have to play around with the code to see if there is a solution.