Odd behaviour when SQL group by on MarkLogic TDE

0
votes

When I run the following SQL query on a TDE defined in Marklogic 9.0.5 I get no results, even though the TDE is filled with data and the particular row (number) I am query'ing is not null for any row.

SELECT number, count(*) FROM object
GROUP BY number

[["number","count(*)"],[null,0]]

When I run the Group By query on id I do get results:

SELECT id, count(*) FROM object
GROUP BY id

[["id","count(*)"],["00e0172adcd406240eebe0d673eeac0b",1],["0276e6d7093440e0998ae9eca13e1d55",1, etc.]

Also when I include the id in the original query as dummy field I get results as expected:

SELECT number, count(*), id FROM object
GROUP BY number

["number","count(*)","id"],["1215837",29,"071b9a599acff0dd7479734e8eb0de31"],["1230182",28,"00e0172adcd406240eebe0d673eeac0b"],["945146",29,"ebfe1da3847d5b0ac14d479436d03e86"]]

Why do I get no results for the first query? I would expect a similar result as the last query except without the id's.

1
sqlmarklogicmarklogic-9
You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. - jarlh
@T. Philippi Did you try? - Menelaos

1 Answers

0
votes

I had a look at the documentation for marklogic. I see that it isn't 100% an SQL rational database, but more interesting is that their examples with count() always show count(1).

see: https://www.marklogic.com/blog/sql-group-by/

Example:

get a basic gender count like ‘male: 22, female: 31’, in an RDBMS we do this:

select gender, count(1) from person group by gender

It's a long show but give it a try:

SELECT number, count(1) FROM object
GROUP BY number