181–190 of 206 posts
Earlier quoted context omitted.
While this might be a joke, the world has been ripe for a replacement for SQL… for the last 20 years.
I could see some standardised SQL language extension, similar to how TypeScript extended JavaScript, that has a transformation phase into the native SQL dialect within the database access library, or via some middleware.
Earlier quoted context omitted.
Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably g…
At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself . Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "…
INSERT depends on constraints and AUTO_INCREMENT.
Coming from the graph database / semantic web area, I would propose foreign key/ primary key relationships in the DB to be detailed and named in a schema description, and then queries reference those relationships by name to define the needed joins.
Yup totally agree - OP is an interesting proposal and has provoked a lot of quality commentary regarding the tradeoffs of clarity vs conciseness, implicit vs explicit, FK performance, etc. But IMO you’ve raised the important long term consideration - do graph based schemas and query languages obviate the need to model foreign keys explicitly? If this JOIN FOREIGN proposal is an incremental step forward, what’s the ne…
This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…
Earlier quoted context omitted.
It would indeed be difficult to remember, but the proposal also suggest changing the default naming convention for foreign keys, to give them the same name as the referenced table. If using an ORM, I would guess this proposal isn't useful, since then you wouldn't hand-write queries anyway, right? Except when you want to override the queries generated by the ORM? (I'm not an ORM user myself.)
Speaking as someone who has used ORMs in the past and contributes to a LINQ Micro ORM... It might make tooling 'easier', but since backwards compatibility has to be considered the actual value add is questionable IMO. Most ORMs/MicroORMs will have tooling that sniffs out the DB Schema including foreign keys, and if you are using those bits (i.e. 'not hand written') most will do the right thing today. I suppose you co…
What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).
Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.
Earlier quoted context omitted.
Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably g…
At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself . Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "…
Of all the myriad indignities of SQL, this isn't near the top of my list. I also don't like making the names of objects like foreign keys and indexes first class concerns in your queries, that's a whole new layer of cognitive overhead.
Agree. I'd much prefer they worked on the way MySQL determines what indexes to use on a multi-table join, so there could be less emphasis on the need to ANALYZE TABLE and FORCE INDEX when the DB is seemingly being dumb, but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up. Troubleshooting full table scans on large sets of data is a nightmare.
Unfortunately I don't think providing that information is generally possible. In some specific cases there are useful details it could provide, but there would usually be a myriad of other similar details that are irrelevant and if it included all those you'd not see the wood for the trees.
EXPLAIN and query plan outputs in other DBs are “this is what I did” not really “why I did what I did”. To make the query planner bright enough to know what details would be useful to you, would probably pretty much require making it bright enough to do the optimisation job without you¹.
[1] picking better indexes without hints, even creating those that are often needed, etc.
MS's SQL Server tries to do this a bit with index suggestions. These are sometimes handy, but often at best for guidance². I've seen people blindly follow these suggestions to get a %-or-few gain from a small set of queries that could see orders of magnitude improvement with just a little tweaking elsewhere³, slowly amassing collections of indexes for very specific cases, sometimes multiple on the same key columns but each INCLUDEing a different mix of other data, that balloon their storage requirements⁴.
[2] a nudge in the direction of “Mr Dev/DBA, you might want to think about how I'd avoid scanning this large object or performing many many thousands of seeks on this other one”
[3] refactoring non-sargable predicates, index changes on other tables being referred to, getting rid of “SELECT *” particularly when referring to hideous views, ...
[4] and having the knock-on effects of slowing insert/update activity & important admin functions (particularly backups)
What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).
This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…
Constraints are part of the logical model just like the types of columns are. Indexes on the other hand is part of the physical model and should be transparent to the logical model. Database engines tend to couple foreign-key constraints with indexes, since you usually want an index on a foreign-key. But in principle they are separate.
So I don't see any violation of the relational model in this proposal. I do like the general idea of extending SQL with metadata-aware abstractions, although I'm not a fan of this particular syntax.