SQL GROUP BY [1.3.0]

[Mark Bigler]

I did some tests with OrientDB 1.3.0 and found a problem with the GROUP-BY command.

Given the demo-database included in the release 1.3.0 distribution...

orientdb> select count(*) from Address

---+---------+--------------------

  #| RID     |count

---+---------+--------------------

  0|         |166

---+---------+--------------------

... 166 Addresses are found. Now when group them by type...

orientdb> select type, count(*) from Address GROUP BY type

---+---------+--------------------+--------------------

  #| RID     |type                |count

---+---------+--------------------+--------------------

  0|    #-2:1|Residence           |104

  1|  #-2:101|work                |50

  2|  #-2:151|residence           |1

  3|  #-2:156|Headquarter         |10

---+---------+--------------------+--------------------

... only 165 Addresses are returned. 1 Address is missing.

It looks like "empty" / "null" fields are ignored in the query above - but not when group by multiple fields...

orientdb> select type, street, count(*) from Address GROUP BY type, street

---+---------+--------------------+--------------------+--------------------

  #| RID     |type                |street              |count

---+---------+--------------------+--------------------+--------------------

  0|    #-2:1|Residence           |Piazza Navona, 1    |101

  1|  #-2:101|work                |Plaza central       |50

  2|  #-2:151|residence           |Rio de Castilla     |1

  3|  #-2:153|Residence           |Piazza di Spagna, 111|1

  4|  #-2:154|Residence           |unknown             |2

  5|  #-2:156|Headquarter         |WA 98073-9717       |10

  6|  #-2:166|null                |Godewaersvelde      |1

---+---------+--------------------+--------------------+--------------------

Should we reopen Issues 1152 or create a new one?

Greetings
Mark

[Luca Garulli]

Hi,

this could be reasonable in case of multiple fields. What does return the standard SQL in such case?

Lvc@

Mark

--
 
 
 

[Mark Bigler]

Ofc it should be returned in case of multiple fields, but also in case of a single field. My expectation for the query...

orientdb> select type, count(*) from Address GROUP BY type

... was ...

---+---------+--------------------+--------------------

  #| RID     |type                |count

---+---------+--------------------+--------------------

  0|    #-2:1|Residence           |104

  1|  #-2:101|work                |50

  2|  #-2:151|residence           |1

  3|  #-2:156|Headquarter         |10

  4|      ...|null                |1

---+---------+--------------------+--------------------

... but actually the null value is not returned.


Mark

[Luca Garulli]

Hi Mark,

you're right that would be the expected result. Please could you open a new issue?

Lvc@

On 22 December 2012 17:25, Mark Bigler <mark....@gmail.com> wrote:

not

[Mark Bigler]

Issue 1230