Why your query language should be explicit • Hacker News Board

Re: Why your query language should be explicit

#41

Post by kragen » Fri, Jun 19, 2015, 3:05 AM UTC

pacala wrote: post #38

cwyers wrote: post #21

> In this query, we get all the users with the name 'jorge' are queried and then ordered in descending order by age. > SELECT * FROM users WHERE name = 'jorge' ORDER BY age; > If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up. No I wouldn't. I can look right…

Every so often the optimizer does it wrong. Instead of reading query plans [new language] and adding a layer of hints [yet another language], how about having a language that allows you to specify the precise order when you eventually care, and optimizes anyway by default. As opposed to three languages layered over each other in a fairly ad-hoc way. val q = Q.from(table).select(foo).filter(foo > 3) val q = Q.from(tab…

This is a great idea. It's usually known as the relational algebra.

Re: Why your query language should be explicit

#42

Post by kragen » Fri, Jun 19, 2015, 3:07 AM UTC

DrScump wrote: post #30

"In this query, we get (sic) all the users with the name 'jorge' are queried and then ordered in descending order by age." Am I missing something, or should that say "ASCending" ?

Descending is probably a better default.

Re: Why your query language should be explicit

#43

Post by MSM » Fri, Jun 19, 2015, 3:08 AM UTC

jamesrom wrote: post #37

A lot of people are saying 'just trust the query planner'. The best of both worlds would be the ability to explicitly define how to get what you want just as easily as you can define what you want. That should be the goal.

SQL gives you all of the tools you need to hang yourself if you're bent on it. FORCE ORDER will force joins to happen in the order specified, index / join hints will force the optimizer to do things the way you'd like.

The reason these things aren't seen often is because they are a terrible code smell. An index that works today may not be the best bet tomorrow. The beauty of the optimizer is it takes the (estimated) statistics available to it on execution to build a performant plan.

The author is basically taking a huge no-no (forcing specific index use / order of operations) and somehow trying to sell that as a positive.

Re: Why your query language should be explicit

#44

Post by icebraining » Fri, Jun 19, 2015, 7:03 AM UTC

geophile wrote: post #31

icebraining wrote: post #13

Earlier quoted context omitted.

When you do need to be very careful about writing a query, the ORM adds a layer of complexity -- not only do you need to control the SQL, but you now need to ensure that your ORM can produce that SQL A good ORM will let you run manually written SQL queries and map the results to objects. For example in Django: https://docs.djangoproject.com/en/1.8/topics/db/sql/

Right, so an ORM makes the easy things easier. The hard things are either harder or you need the escape hatch. I've both created an ORM (long ago, back in Java 1.0 days) and used a few. My conclusion is that the real pain in interacting with a database isn't writing SQL, it's managing the API (e.g. JDBC). Managing PreparedStatements, managing ResultSets, handling exceptions, getting rid of warnings, mapping to object…

I guess it depends on your needs. On the platform I work on, the user can configure a set of security restrictions on tables (e.g. users of group 1 can only read records of table X which have public = TRUE; users of group 2 can only update records of table Y which have user_id = [their own user id]), and the ORM automatically inserts those restrictions into any query made to those tables.

Doing this with manually written queries would be, if nothing else, a PITA. Then again, it's a specific need; I suppose most applications have static, not dynamic rules.

Re: Why your query language should be explicit

#45

Post by ksherlock » Fri, Jun 19, 2015, 1:15 PM UTC

This seems like a massive step backwards. And it's not for the benefit of the user (the programmer, she needs to do more work). It's for the benefit of the RethinkDB programmer (query planners are hard work!). Add an index in SQL? Existing queries work and can make use of it. Add an index in RethinkDB? Now go rewrite all your code if you want to take advantage of it. That's an improvement? (And do you think the average javascript programmer will do a better job at it than 30 years of database research?)

If you're trying to spin shit into gold, maybe you should try a rethink preprocessor. Just write normal SQL in your code and the pre-processor verifies the tables and columns, checks for indices, and writes the best "explicit" query for you.

Re: Why your query language should be explicit

#46

Post by geophile » Fri, Jun 19, 2015, 3:11 PM UTC

icebraining wrote: post #44

geophile wrote: post #31

Earlier quoted context omitted.

Right, so an ORM makes the easy things easier. The hard things are either harder or you need the escape hatch. I've both created an ORM (long ago, back in Java 1.0 days) and used a few. My conclusion is that the real pain in interacting with a database isn't writing SQL, it's managing the API (e.g. JDBC). Managing PreparedStatements, managing ResultSets, handling exceptions, getting rid of warnings, mapping to object…

I guess it depends on your needs. On the platform I work on, the user can configure a set of security restrictions on tables (e.g. users of group 1 can only read records of table X which have public = TRUE; users of group 2 can only update records of table Y which have user_id = [their own user id]), and the ORM automatically inserts those restrictions into any query made to those tables. Doing this with manually wri…

It sounds like a VIEW would handle this restriction also. And then you should be able to use any ORM or lower level of abstraction.

Re: Why your query language should be explicit

#47

Post by kylepdavis » Fri, Jun 19, 2015, 10:02 PM UTC

I think explicit languages can make things more clear however disagree with the notion that implicit behaviors are necessarily a bad thing.

I've found that the hybrid approach in the MongoDB aggregation framework works really well.

It optimizes things around the first $match to create an optimized initial read (the selectivity of your initial stages is really important). Once you're past the initial read the rest of the pipeline is fully imperative.

This makes things really nice when debugging complex aggregation pipelines. For example, you can simply omit the rest of your pipeline at any point to debug (with a $limit), see what you're dealing with, fix them, and move on to the next one.