8

I thought by now I'd know all features of the Stack Exchange Data Explorer but apparently I don't. This is what I do:

  1. Click the Compose Query button on the home page.
  2. Paste SELECT MAX(CreationDate) FROM Posts into the editor and click Run Query

enter image description here

  1. Click the 'permalink' option. This will take you to a read-only version of the query, which is no surprise, but SEDE also automagically added some documentation to my query:

enter image description here

This is the added text:

-- Approximate date of last Data Explorer update
-- based on the most recent post available to Data Explorer
-- 
-- adapted from this query:
-- http://data.stackexchange.com/superuser/s/1002/top-100-most-frequent-voters

Sadly, the linked query isn't really relevant. I can reproduce it in a private window, so it's not something tied to my account.

6

tl;dr: no magic here, but a bug somewhere

When storing the query in the database, we check if such a query already exists based on the hash of the provided SQL, and just link it to the query set that you're working in if one does.

At the time the query is submitted, it's converted to a parsed query which gives us, among other things, the hash value used in the process above + saved with the query, if a new query has to be created. There was a bug that resulted in the comment not being included in the hash, so the "existing query" query returns this query (…query).

This bug existed back when the original query was written, where the hash was calculated based on the value of Sql. This was taken from the input SQL with lead comments removed, since the leading comments originally were used to define the query title and description.

I fixed the problem when I later refactored some of the responsible code, although I don't know off hand if I did so consciously. Now, putting any other kind of comment (thereby changing the hash) will cause a new query to be created, but since we never recalculated the hashes for queries already in the database, it's possible to trigger it with new, SQL-only queries.

5
  • 1
    Horrible things are done to the query for the purposes of execution, but the Sql value this particular hash is calculated from is supposed to be exactly what was entered, so 🤷‍♂️
    – Tim Stone
    Jun 6 at 20:53
  • The main problem is that it takes the ExecutionHash for the lookup in the Cached Query Results which is calculated after the SQL is "parsed". The parsing step removes SQL comments and for the example queries that does return the same SQL string and luckily? hash.
    – rene
    Jun 7 at 7:02
  • 1
    @rene That's expected, if the SQL is equivalent it's okay to return the same cached results
    – Tim Stone
    Jun 7 at 19:32
  • True, so ... this is status-bydesign then? It is a somewhat ... unexpected result in the sql text.
    – rene
    Jun 7 at 19:43
  • 2
    @rene The cached results are just the query output(s), the issue here is with the query itself where we're considering the query with comment and query without comment the same, even though their hash (separate from the execution hash) should be different.
    – Tim Stone
    Jun 7 at 19:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .