basic queries give unexpected results

[mkgn]

Pardon me if below queries are wrong (I use surrealist and it doesn't complain). So, I am trying different queries to see how surrealdb works before I decide to choose it for my project which expects to have high reads. So I am quite interested in Record Id links and Graph relate options.

This is what I did;

add some test data with RELATE

FOR $i IN [1,2,3,4,5] {
    RELATE (CREATE ONLY user:[string::concat("user-", $i), time::now()])->owns->(CREATE ONLY test:[rand::string(5), time::now()]);
};

check data
SELECT * FROM owns,test,user; //all ok

working queries (returns the record having user-5 in id)

1. SELECT * from user:['user-5', NONE]..;
2. SELECT * from user where id[0]='user-5';

non of the below queries are working which I feel should work

1) SELECT * from user:['user-5','2024-05-15T03:07:02.554Z'] //time is from an already inserted record returns []
2) SELECT * from user:['user-5',<datetime>'2024-05-15T03:07:02.554Z'] /same as above returns []
3) select * from user:['user-5', type::datetime('2024-05-15T03:07:02.554Z')]; same as above returns []
4) SELECT * from user where id[1]='2024-05-15T03:07:02.554Z'; //same as above returns []
5) SELECT * from user where id[1]=type::datetime('2024-05-15T03:07:02.554Z') //same as above returns []
6) SELECT * from user where id[1]=<datetime>'2024-05-15T03:07:02.554Z' //same as above returns []

basically, if you have a datetime in a complex record id, you cannot search using it in any of the above methods.

Now, let's recreate above with a small change as below;

FOR $i IN [1,2,3,4,5] {
    RELATE (CREATE ONLY user:[string::concat("user-", $i), string::concat("last-", $i)])->owns->(CREATE ONLY test:[rand::string(5), time::now()]);
}; 

In above, user table has no datetime part to the complex id. it's just a [string,string]. Now if I run below query

select id from user:['user-1','last-1'] it works.

This behaviour is there regardless of the complex record id type (array or object).

Another thing I noticed is when you do a RELATE query for a non existent record it doesn't complain it just inserts it (related to #3921 ). In the mentioned issue, the original comment was to throw an error if you try to RELATE non-existent data. But then it has changed to a feature to auto create the missing data, which I think is dangerous in certain use cases. In my scenario when doing a RELATE i must make sure that both exists in the db. Of course I can do that by code, however by default the db should enforce it may be unless you introduce something like;

RELATE [table]:[id]->owns->[table]:[id] **RELAX**  <-- new keyword?

[mkgn]

ok.. so I checked this with a friend of mine and the first part of the issues was sorted. Difference was that he tried it using VS Code Thunder Client and I was using surrealist over websockets.

To test these queries what I did was after inserting them using for...loop I did a

select * from user and in the results, surrealist rounds the datetime to a shorter version;

2024-05-15T10:24:28.903167600Z becomes 2024-05-15T10:24:28.903Z in surrealist query result output

So, in above queries, i use the shortened version to search and of course no results.

So, surreallist should not meddle with the datetime and it should not round it. I am going to post this to surrealist.