Hacker News Reader

Comment by CuriouslyC | Hacker News Reader

Those are some nice features. Unfortunately, your DBMS doesn't do some basic things like return consistent results for a simple SQL query with a group by and having clause. I admit this might be a configuration issue on my company's end, but if so, that is a terrible configuration option and should be hidden away, opt in only, with a huge wall of warnings so people don't actually enable it except in extreme circumstances.

View on HN

I've seen you mention these inconsistent results twice here in this this thread, but have worked at MemSQL for 5 years and never heard of such an issue. Have you reached out to see if maybe your query / data is not what you expect? I've seen inconsistent results only once, and it was because the default date formats across RDMBSs were different (and was not anticipated).

How would the query/data not be what I expect if If I'm writing the query myself, and looking directly at the sql table definition to create it?

Beyond those considerations, why would the same exact same query (executed several times in rapid succession from the console) produce vastly different results? Also, I should clarify, rewriting the query from "select ... from xyz group by ... having ..." to "select ... from (select * from xyz where ...) group by ..." made the inconsistency goes away, without changing the filtering clause. That does not inspire confidence.

Can you post the full schema and query? Are you sure you are not projecting columns that are not part of the group by expression?

I really appreciate the offer. I got the go-ahead to share this information, where should I direct it?

Here, or https://www.memsql.com/forum/, or memsql-public.slack.com.

To close the loop on this one. We looked at the query and strictly speaking we should be rejecting it b/c HAVING clause is referencing a column that's NOT in group by and NOT an aggregate expression. The query shape is:

  select count(*), a from T group by a having b > 0

In this case b is not allowed to be part of having by ANSI standard.

We let it run b/c some customers migrate from MySQL and MySQL allows this query. You can set MemSQL to be strict about it by setting this variable:

  set session sql_mode = only_full_group_by;

Thanks for taking a look at it. Your position is perfectly reasonable, but given the fact that (at least in my case) the results I got back were subtly wrong, and there's a good chance someone wouldn't notice, it might be a good idea to default this off if it isn't already, with a really stern warning in the config.

Reply on news.ycombinator.com