Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Document SQL syntax used #142

Open
mitar opened this issue Oct 28, 2019 · 14 comments
Open

Document SQL syntax used #142

mitar opened this issue Oct 28, 2019 · 14 comments

Comments

@mitar
Copy link

mitar commented Oct 28, 2019

I could not find documentation for which exactly SQL syntax is being used/supported?

@jonhoo
Copy link
Contributor

jonhoo commented Oct 28, 2019

That is correct — there is no such documentation at the moment. Since this is a research prototype, we sort of just support what the implementation supports, and it's not quite that clearly defined :) In general, most straightforward SQL statements should work, including most aggregations, order+limit, projections, filters, and joins where the join clause is a single column equality.

@mitar
Copy link
Author

mitar commented Oct 28, 2019

I think there are two questions here:

  • What is the syntax. Is it standard SQL, SQLite dialect, MySQL dialect, etc.? What are case sensitivity rules? What are escaping rules? How are placeholders defined? ? Or some other character?
  • What is supported, you listed those, and I had easier time to read about those.

Concretely: what can I put into CREATE TABLE statement? I see that there is some special syntax to define local views and public views (queries). Is this defined somewhere?

So documentation here is very limited. Maybe this is another reason why it would be useful to switch to sqlparser-rs. Because then you can just delegate the syntax questions to it.

Then probably MySQL adapter would have to be changed a bit so that queries are rewritten from MySQL to internal SQL language. I think that it is visible that current syntax is as-is to make it easier to make MySQL support. But that is also a bit breaking the abstraction.

BTW, in my Rust client applications, do I have to use SQL syntax? Or could I create tables by calling some Rust methods? Because the main motivation for me for this issue was that it looks like when I am building a Rust client application I still have to use SQL (and which SQL) to interact with Noria. It is not that Rust client application would provide programmatic API and then MySQL adapter would map from SQL to that API. Rust client library is using SQL as well. That was a surprise to me. And then it also made it unclear what is this SQL syntax I should be using. Because in examples is not just SQL but also some additional special cases like QUERY.

@jonhoo
Copy link
Contributor

jonhoo commented Oct 29, 2019

Ah, yes, you're right, there is also no documentation of the high-level details about queries. In general though, it is just standard SQL; that's why we didn't think to do it. There are some extensions (such as the QUERY keyword you point to), but apart from that standard SQL should do just fine. The same thing applies to CREATE TABLE — you should be able to use standard syntax, though of course Noria will only work with the data types it supports internally (I agree that documenting these would be a good idea!).

As for the Rust interface, the SQL you use to give recipes there should be exactly the same as the MySQL shim. The way to think about this is probably that Noria itself uses SQL, and that the shim is just a way to be compatible with the MySQL wire protocol. We could, in theory, provide a lower-level interface for manually constructing Noria dataflow, but that's pretty difficult to do well since the dataflow is subject to a number of somewhat finicky layout rules that we would somehow need to enforce for user-specified dataflow as well. Our multi-query optimization also relies on SQL, so a manually specified graph would not get the advantages of cross-query combination.

@jonhoo
Copy link
Contributor

jonhoo commented Oct 29, 2019

@ms705 I agree with @mitar that we should documented how Noria's SQL syntax translates to Noria's concepts. For example, things like the QUERY keyword and CREATE VIEW operations would be handy to have some text about. Listing out what we support in CREATE TABLE would also be handy for prospective users.

@ms705
Copy link
Member

ms705 commented Oct 31, 2019

Moving to sqlparser-rs would be great! It's unlikely that I will have cycles for this in the near future, but I'm happy to help a PR along if you want to look at this, @mitar.

Independently of this, we'll add some documentation on how the VIEW and QUERY keywords in Noria recipes work.

@mitar
Copy link
Author

mitar commented Oct 31, 2019

BTW, do you care about VIEW and QUERY keywords? Couldn't we map them to some existing SQL commands, like CREATE VIEW and CREATE MATERIALIZED VIEW? Then probably sqlparser-rs could be used as-is.

Moving to sqlparser-rs would probably mean that you would have to transpile some SQL queries in MySQL adapter. Is that something you would care about? Or we just keep continue to pass SQL as-is through, for better or worse?

@ms705
Copy link
Member

ms705 commented Oct 31, 2019

The MySQL adapter also uses our custom parser at the moment; it would need to likewise move to sqlparser-rs.

VIEW and QUERY are needed because standard CREATE VIEW (AIUI, at least in MySQL) does not allow for placeholders (?) in the query.

@mitar
Copy link
Author

mitar commented Oct 31, 2019

OK, but I think it will be easier to add to sqlparser-rs for placeholders inside CREATE VIEW statements instead of adding special additional keywords?

You are not really passing directly SQL queries to MySQL anyway, no? VIEW and QUERY go to Noria, not to MySQL?

@ms705
Copy link
Member

ms705 commented Oct 31, 2019

That's correct, VIEW and QUERY are for Noria recipes only. They're not even part of our custom SQL parser, but parsed separately inside Noria. The MySQL adapter prefixes prepared statements (which can have placeholders) with these keywords when it installs the statements by extending the Noria recipe.

@mitar
Copy link
Author

mitar commented Oct 31, 2019

So then I am not sure why it could not then work if Noria would use internally CREATE VIEW syntax?

@ms705
Copy link
Member

ms705 commented Oct 31, 2019

It could -- if the parser (whichever is used) supported parameters in CREATE VIEW statements.

@mitar
Copy link
Author

mitar commented Oct 31, 2019

OK, we then understand each other. Good. :-)

I will see if and when I will have time to contribute PRs to this and other ideas I have brought up. I would love to, but at the moment am I a bit swamped with other things. Anyway, it is good to know what should be done. Thanks.

@HenkPoley
Copy link

HenkPoley commented Dec 14, 2019

and joins where the join clause is a single column equality.

Meaning JOIN b on a.b_id=b.id AND a.blah=b.something_else will not work?

@jonhoo
Copy link
Contributor

jonhoo commented Dec 16, 2019

@HenkPoley Correct :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants