Orientdb: SELECT returns very different results for equivalent queries

[codelimner]

Expected behavior and actual behavior

SELECT queries expected to return equivalent results instead produce very different results.

Steps to reproduce the problem

1) Start orientdb console and connect to GratefulDeadConcerts database. (v2.2.1-SNAPSHOT from develop).

In the next two queries (which I believe to be equivalent), I am trying to list all singers that have sung a song written by "Bob_Dylan".

2) Query 1:

orientdb {db=GratefulDeadConcerts}> SELECT OUT("sung_by").name as singer FROM V WHERE OUT("written_by").name="Bob_Dylan"

+----+-------------+
|# |singer |
+----+-------------+
|0 |[Weir] |
|1 |[Weir] |
|2 |[Bob_Dylan] |
|3 |[Bob_Dylan] |
|4 |[Bob_Dylan] |
|5 |[Bob_Dylan] |
|6 |[Bob_Dylan] |
|7 |[Bob_Dylan] |
|8 |[Garcia] |
|9 |[Garcia] |
|10 |[Bob_Dylan] |
|11 |[Bob_Dylan] |
|12 |[Bob_Dylan] |
|13 |[Garcia] |
|14 |[Lesh] |
|15 |[Bob_Dylan] |
|16 |[Garcia] |
|17 |[All] |
|18 |[Weir_Garcia]|
|19 |[Weir] |
+----+-------------+
LIMIT EXCEEDED: resultset contains more items not displayed (limit=20)

3) Query 2:

orientdb {db=GratefulDeadConcerts}> SELECT name as singer FROM V WHERE IN("sung_by").OUT("written_by").name="Bob_Dylan"

+----+-----------+
|# |singer |
+----+-----------+
|0 |Weir_Garcia|
|1 |Neil_Young |
+----+-----------+

2 item(s) found. Query executed in 0.068 sec(s).

Important Questions

Runninng Mode

• [ ] Embedded, using PLOCAL access mode
• [ ] Embedded, using MEMORY access mode

• [X ] Remote

  Misc

• [ ] I have a distributed setup with multiple servers. How many?

• [ ] I'm using the Enterprise Edition

  OrientDB Version

• [ ] v2.0.x - Please specify last number:

• [ ] v2.1.x - Please specify last number:

• [ X] v2.2.x - Please specify last number:
  OrientDB console v.2.2.1-SNAPSHOT (build develop@r3f4b9c490e67ffe2c0c2030930517509c647a334; 2016-06-07 14:02:16+1000) www.orientdb.com

  Operating System

• [ ] Linux

• [ ] MacOSX
• [ ] Windows
• [ X] Other Unix (FreeBSD 10.3)

• [ ] Other, name?

  Java Version

• [ ] 6

• [ ] 7
• [ X] 8 (OpenJDK 8)

[luigidellaquila]

Hi @codelimner

You should not use = operator against expressions that can return multiple values (in case more than one value is returned, the = won't match), you should use CONTAINS instead.

Please try the following and let me know:

SELECT OUT("sung_by").name as singer FROM V WHERE OUT("written_by").name CONTAINS "Bob_Dylan"

SELECT name as singer FROM V WHERE IN("sung_by").OUT("written_by").name CONTAINS "Bob_Dylan"

Thanks

Luigi

[wolf4ood]

@codelimner

BTW from 2.2 you could use pattern matching like this

MATCH 
    { class: V, as: bob, where: (name ="Bob_Dylan") } 
    <-written_by- {as : song} -sung_by-> {as : artist}
RETURN bob.name,song.name,artist.name

See here for docs
http://orientdb.com/docs/2.2/SQL-Match.html

[codelimner]

@luigidellaquila Yes, I had to use CONTAINS, and with that change the query produces results as expected.

@maggiolo00 The MATCH statement is very useful. Just a minor comment on the documentation at http://orientdb.com/docs/2.2/SQL-Match.html The second MATCH query under the section [Use Cases -> Deep Traversal] appears incomplete.

Thanks!

[luigidellaquila]

Hi @codelimner

Thank you for the pointer, it seems to be a problem with GitBook, the original wiki page works fine... https://github.com/orientechnologies/orientdb-docs/blob/master/SQL-Match.md#deep-traversal

I'm trying to fix it now

Thanks

Luigi