Hacker News Reader

Comment by wruza | Hacker News Reader

A bit shameless to ask a question under top HN record, but I always wondered why do not SQL engines give access to their SQL AST to generate queries. Of course, one can combine a query from strings with placeholders, and it takes not so much comparing to actual querying process, but it feels so hacky. Why not provide something like:

    Query *q = db_select(db);
    db_add_from(q, "persons");
    db_left_join(q, "companies", db_expr(DB_EQ, "per_id", "com_id"));

    Query *q = db_create_table(db);
    db_add_column(q, "id", DB_TYPE_INT | DB_COL_NOT_NULL | DB_COL_PRIMARY);
    db_set_table_name(q, "persons");

... and so on? That way we could combine and generate queries for specific engines without that ugly string joining/parsing.

View on HN

> A bit shameless to ask a question under top HN record, but I always wondered why do not SQL engines give access to their SQL AST to generate queries.

A number of reasons from the PostgreSQL POV (work on it):

- our internal parser representation isn't stable and frequently changes from major release to major release, occasionally even in minor releases.

- we'd need a lot of additional verbose error checking code to make sure only sensible AST can be submitted

- there's two forms of parser AST "raw" and "analyzed". The former is produced without access to the database, the latter has done validity checks, type lookups, etc. The former is what you could validly produce remotely - but it's also more cumbersome to deal with.

I think there's arguments for supporting a useful different representation than SQL, but I think doing this with the already existing AST isn't going to fly. And a new proper language / representation would be a lot of work...

Edit: formatting

> And a new proper language / representation would be a lot of work...

I don't think a new language would be needed, and most of the work has already been done in projects like LINQ. You'd just need to expose a stable AST front-end, which would be a lot easier than a whole new parser. I mean, it's just relational algebra right? ;-)

> You'd just need to expose a stable AST front-end, which would be a lot easier than a whole new parser.

"Just". You still need a parser for that AST if it comes from the client, you need to convert that into the actual internal representation with validation and everything.

> I mean, it's just relational algebra right? ;-)

It's really not. A lot of SQL can't conveniently expressed in relational algebra (could luck with recursive CTEs) and a lot of other queries aren't as efficiently representable.

> "Just". You still need a parser for that AST if it comes from the client, you need to convert that into the actual internal representation with validation and everything.

Yes, just: http://lambda-the-ultimate.org/node/5375

> A lot of SQL can't conveniently expressed in relational algebra (could luck with recursive CTEs) and a lot of other queries aren't as efficiently representable.

Clearly some language is being used to reason about these things, and evidently, this language is reflected in some SQL-like surface syntax. It would be no more a burden to provide an extensible AST than an extensible SQL-like language. In fact, it'd be considerably simpler since tokenizing, parsing and verification would all be simpler.

In MySQL's new protobuf-based protocol you can express expressions as Protobuf messages https://dev.mysql.com/doc/internals/en/x-protocol-messages-m...

This isn't usable for all sorts of all statements as, among other things, it's hard to maintain a stable API for all aspects. But might be useful for common cases. If there's more you need I'd be interested in a feature request! (I'm working in MySQL's clients team)

That's awesome! Do you expect significant latency improvements to come from this?

There shouldn't be much of a difference in performance, there are some areas on the server we can improve in the longer run and hope is that clients can write requests more efficiently ... in a typical situation the parsing of the query hopefully isn't the bottleneck, but the actual execution or processing of the result in the application ;-)

This is essentially what an ORM does, e.g. SQLAlchemy [1]. Except instead of providing a slightly nicer API to generate query strings, an ORM tends to be more tied to the host language and use its object system to abstract out most of the SQL complexity and make declaring SQL tables/queries look like normal operations on the data themselves instead of the database. Django's ORM [2] for example autogenerates joins for you under certain query conditions, which is super handy.

[1] http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

[2] https://docs.djangoproject.com/en/1.11/topics/db/queries/

I'm aware of SQLAlchemy Core/ORM (and ORMs in general), my question is more like why do we have to concatenate strings in these project's internals instead of using some API to pass query structures without intermediate composing/decomposing. For small queries that ORMs often do, it feels like needless and annoying overhead.

For now, even '?' placeholder escaping is actually manually-printf'ed under the driver's hood, afaik, so in the end we send plain text to the server. SQLite probably avoids that via sqlite3_bind() call, which allows to pass SQLITE3_STATIC string args if you want, but that's the benefit of in-process engine.

You need some way of serializing your data structures to a wire format, and SQL has worked ok so far.

Besides a potential of a performance gain in parsing at the server side, I'm not quite sure there's much gain you would get - there might not be that much difference in serializing those data structures to "plain text" vs any other format you come up with.

See the part about support-costs in a different link.

But even more, I think it's "in the nature of programming" that for a given system you have some parts are regularly structured and externally visible but other parts that seem like they ought to be visible and changable from outside but which aren't and can't easily be made so.

This might be because they use intermediate variable or it might be because they have changed the implementation over time or for a variety of reasons.

And this ultimately comes down to the difference between the way intuitively you'd think X feature should be implemented and real, much-more-messy way X feature is actually implemented - the difference internals and an API.

Having internals and externals be equally easy to work with was the dream of the object orientation but sadly that has remained a fantasy and unfortunately looks like it will remain so.

I highly doubt that placeholders are interpolated into the SQL statement. Doing so will defeat the purpose of having them in the first place. Rather, they are tranferred out-of-band to the SQL "engine", as it were, and are used as straight up variables.

I checked it for PostgreSQL right now, and it seems that libpq can send them OOB via PQexecParams() and networked integer buffers. But that's not the case with OpenResty's pgmoon driver, which doesn't have such an interface. E.g. Lapis framework built on top of that simply escape_literal()-izes all arguments and interpolates right into query text. So, YMMW with specific (or generic) drivers.

Placeholders are there for those who tries to concatenate queries by hand; having them properly escaped in protocol should not defeat client-side security purpose.

You can never properly escape user input, so this escape_literal() is a ticking timebomb.

How come it's impossible to properly escape user input? I get that it's hard if the format is complex, but I don't see what makes it categorically impossible, especially for a simple format like an SQL string. You just double every single quote and then surround the whole thing with single quotes.

> ...every single quote...

And then you come across \u0027 and you're screwed.

Do you know any decent server that interprets 6-char \uxxxx sequences as part of its top-level query syntax? I mean, how do you safely pass '\u0027' literal to it anyway? If you can't, then its literals and escaping are broken by design, so it is good to know from the start of using it.

Edit: removed double backslash from literal to not confuse it with host language's escaping.

If you use a broken RDBMS, maybe. The standard is quite clear, and that certainly doesn't work in PortgreSQL.

Depends. Server-side prepare works the way you described while client-side prepare interpolates placeholder values. Both are widely used.

Having the interpolation done in one central place and coded by someone experienced sounds as good as OOB to me.

With that central place being?

Lapis db:query("? ?", a, b)

Edit: this call is most low-level db interface in lapis apps.

I know a lot (a lot) of non-technical SQL users. SQL is a widespread abstraction from the underlying databases. It's very powerful.

What you describe is useful from a programmer's point of view, but maybe the wrong abstraction. You could write such a thing which actually generates SQL from an API like you've defined. You could write an alternate ODBC engine.

But there is a lot of overhead for database authours to actually provide an API like you describe. It's far simpler to provide something SQL-compliant with an ODBC or JDBC driver and now you can plug in almost anywhere.

I have used a database engine that exposes primitives to build filters and similar programs to what you describe, and selectively push them to the server. It's not a very deep abstraction.

Part of the reason is support. Long ago Britton Lee[1] in fact provided an AST API mainly for tool vendors. They stopped doing that when one of their customers called them asking for help with the "GUIDE BY" clause. Nobody at BL knew what a "GUIDE BY" was since it was something implemented by the frontend, but that didn't matter to the customer.

https://en.wikipedia.org/wiki/Britton_Lee%2C_Inc.

Product Manager for MySQL here. Yes, I agree with you that the reason is most likely support.

Consider SQL; it's declarative and highly expressive. Your users can craft queries that you did not consider, and you have to make sure they work consistently between versions. It's a very hard problem.

Creating more public APIs? It's a hard sell that vendors are sensitive to. Reducing the surface area makes it easier to add future improvements.

KDB lets you do this.

    q)-3!parse"select last id from t"
    "(?;`t;();0b;(,`id)!,(last;`id))"

Indeed you can use this to modify a user-entered query easily:

    p:parse"select last id from t";
    @[p;2;'[1 rotate;,];enlist(=;`user;enlist u)]

This is worse than a useless comment. It is downright harmful to getting people to use KDB. Maybe you should explain it to people?

For those reading:

parse is a function that takes a string and returns the parse tree and -3! (gotta love APL) is execute that takes a tree and turns it back into a string.

The parsed syntax it spits out allows you to compose pieces of queries or modify them (there are easier ways such as queries return tables that can always be in inputs to other queries).

Maybe the author would like to add more context?

> This is worse than a useless comment.

Oh no, please don't attack others like this in comments here. Even if you clearly mean well (which I'd say is the case here), it can only cause harm.

geocar's comments are consistently excellent (including for explaining the niceties of kdb and array languages to the community) and having fewer of them would make this place worse. Not everything needs to be spelled out, and besides, sometimes there just isn't time to do so. It can be a lot more work!

As a long time K evangelist (before KDB 2 even) I understand that but this post just played into all the bad stereotypes of KDB+ being jibberish and line noise. Even for somebody veered K it took me a minute to figure out -3!. He isn't a@kx.com posting to the K mailing list.

I did vote the comment up before responding to him as KDB, but I'm not sure why really.

Well, "companies" is a string, isnt't it? Ultimately, there will be strings down there.

Working with pure AST is a huge pain. One can trivially construct something that can not realistically be represented in an SQL dialect of choice.

LINQ is the (IMO) one of the best ways to do query composition, and with the nameof() operator you can really get away without a single hardcoded string in your application-level code.

>LINQ

Ehhh...something as simple as a left-outer join is near ungrokkable for me in LINQ. Cross apply / lateral join on a table-valued function? Forget about it.

Not to mention - due to the way LINQ works a lot of errors that seem like they should be caught at compile time don't get caught until run time (e.g. you wrote something in a where clause that can't be translated to SQL).

LINQ is like flying a plane. It will take you where you need to go extremely fast, but will also crash you into the ground if you don't know what you're doing.. Personally I get immense value from it daily, but I know when not to use it as well.

I didn't know about these shortcomings (I don't use LINQ much). Could you give examples for each one?

Here's a left outer join (taken from https://stackoverflow.com/questions/3404975/left-outer-join-...)

    from c in categories
    join p in products on c.Category equals p.Category into ps
    from p in ps.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

You're doing something called a 'group join' (second line) 'into' a new identifier (ps) above. I'm guessing it stuffs each matching result in 'p' into a list, which sorta makes sense. The second part mystifies me though - somehow 'from p in ps.DefaultifEmpty()' flattens the list back out into a denormalized result set while simultaneously preserving entries in 'c' with no matches.

What does 'p' point to now? Why is it seemingly created twice? Is 'ps' still available? For a simple left join this is way, WAY too convoluted IMO.

The second thing I'm talking about you can read about by finding out when EF throws a 'NotSupportedException' at runtime. (e.g. https://stackoverflow.com/questions/25337974/entity-framewor...). Basically not everything you can do in LINQ can be translated to SQL. But it won't tell you at compile time. You have to wait till run time, which is garbage IMO. The example I linked is trivial but it's easy to miss this kind of thing when you're optimizing ORM use (and trying to move as much computation to the DB as possible).

Dynamicaly adding filters or parts of the query, be able to have different parties to construct query, do transformations, replacing objects,... that would be great. And having it in well known, standard format and supported in languages, and have execution plans the similar way, that would be the dream... That would really simplify the life.

I started to write something a bit like this a while ago for SQLite and C++, https://github.com/Gloopio/SQLighter we also have a more complete/full featured version that we use internally, although I was not sure if there are enough people that would want it - for it to be turned into an open source project.

IIRC, stored procedures let you do something like this. If you compile your query as a stored proc, there's usually an API using which you can pass values to it as parameters in their "native" form, i.e. without having to convert them to strings first.

Yes please! A couple years ago I wrote a sql-like layer to federate queries across a couple different databases, with the intent of unifying the data we had spread out over postgres, cassandra and S3. The best decision I made was not writing a real query parser and just using s-expressions, which is pretty much what you're asking for. It was great.

Generating an AST client side, then serializing it, can take a substantial amount of CPU usage especially in languages like JavaScript or Python. If you do include the ability to make an AST, it would be good to have a facility for parameterized queries so that you only have to build and serialize the AST once.

Reply on news.ycombinator.com