3 ms·

> Real parameterized queries exist and can be had today /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing

> Real parameterized queries exist and can be had today /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing something almost all vendors already had in some form or another, for example, I'm pretty sure even the first release of ODBC in the late 1980s also had parameters. Disclaimer: My pontifications below derive from my life's experiences starting with Access/JET Red as a sprog, to my current professional work with MS SQL Server, Postgres (and MySQL, I'll admit - but I'll say MariaDB instead) built over the past 17 years - but I have absolutely zero experience with Oracle and Db2, so I honestly don't know what cool language features they have (and they must be way ahead of the ISO spec, otherwise why else would people pay so much for it?... Hmm, then again, Oracle still doesn't have a bit/bool column type, does it?). Anyway: However, the expressiveness of parameters hasn't changed much since the original design of ODBCS - at least as far as I'm aware: Query parameters are still mere scalar value placeholders instead of hardcoding literals inside queries and statements: with limited exceptions like T-SQL's ceremony-laden table-valued-parameters and Postgre's array types, it's still not possible to parameterize database object identifiers or even have a true variadic `WHERE IN ( ... )` predicate without resorting to Dynamic-SQL, nor can we use parameters to conditionally enable or disable query predicate clauses: yet these are all essential features for any kind of ORM or language-integrated-query system built today (the canonical example being Linq/EF in .NET and TypeORM or Prisma for TypeScript/JS. Why isn't anyone meaningfully advocating for a "SQL/2"? I know backwards-compatibility is 100% essential, but that's the easy part (because relational calculus and relational are isomorphisms, hence why semantic-preserving query translation between different SQL engines is a solved problem), but lack-of backwards-compatibility is usually the reason most good-ideas die in committee - so how come Google was successful in pushing HTTP/2 and even HTTP/3, but we're still using 1980s-level SMTP and SQL? Are all of the major RDBMS vendors so afraid of change that they're willing to forgoe winning-over millions of new customers if they can ship a usable, flexible, expressive, and safe query (and query-building) language? I'm just blabbering at this point. Forgive me, but I need something to distract me from the outside world right now.

jhgb 5y ago
> /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing something almost all vendors already had in some form or another, for example, I'm pretty sure even the first release of ODBC in the late 1980s also had parameters. Interbase must have had them in 1980s since Interbase (and now Firebird) compiles ESQL queries into static BLR code. Since there's no way to compile a new query at runtime (other than by outright switching to using Interbase/Firebird's DSQL C API for a non-compile-time query), all these queries have to be parameterized to be of any use.
- DaiPlusPlus 5y ago
  I was curious about BLR so I had a quick look but there isn’t much around other than what’s on Firebird’s website. Their description of BLR makes it sound like an eagerly-evaluated equivalent of SQL Server’s Execution Plans, but are compiled on CREATE instead of on first-use, and are comprised of machine-native (i.e. raw x86/x64 + disk read syscalls) operators arranged together - or maybe slightly higher-level? It’s unclear how schema-binding works. I’m curious what advantages there are to that approach anymore: databases are invariably IO-bound, not CPU bound, and things like well-maintained indexes and statistics objects are far more important when it comes to DB perf than the ISA of the query engine. I’d wager an ultramodern engine running in WASM or even interpreted Java will run faster on the same hardware than JET, I’d think. On a related note, why are we still stuck with 4K/8K-sized pages?
namibj 5y ago
You can combine literals into an SQL query, and doing so isn't even hard to do correctly. You'd still use parameters to feed user input, of course.
- DaiPlusPlus 5y ago
  SQL Server has had _autoparameterization_ opf queries for a while now: when you have multiple queries of the same general shape but with varying literal values, it automatically converts the literals to parameters (rather than vice-versa), and it's generally beneficial for performance because existing query-plans can be reused (instead of having to create a new plan for each literal value encountered): https://www.mssqltips.com/sqlservertip/2935/sql-server-simple-and-forced-parameterization/ https://www.mssqltips.com/sqlservertip/2935/sql-server-simpl...
  - namibj 5y ago
    I didn't mean it that way. I mean you can combine SQL fragments, think multiple `ORDER BY` clauses.

jhgb 5y ago

> /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing something almost all vendors already had in some form or another, for example, I'm pretty sure even the first release of ODBC in the late 1980s also had parameters. Interbase must have had them in 1980s since Interbase (and now Firebird) compiles ESQL queries into static BLR code. Since there's no way to compile a new query at runtime (other than by outright switching to using Interbase/Firebird's DSQL C API for a non-compile-time query), all these queries have to be parameterized to be of any use.

DaiPlusPlus 5y ago
I was curious about BLR so I had a quick look but there isn’t much around other than what’s on Firebird’s website. Their description of BLR makes it sound like an eagerly-evaluated equivalent of SQL Server’s Execution Plans, but are compiled on CREATE instead of on first-use, and are comprised of machine-native (i.e. raw x86/x64 + disk read syscalls) operators arranged together - or maybe slightly higher-level? It’s unclear how schema-binding works. I’m curious what advantages there are to that approach anymore: databases are invariably IO-bound, not CPU bound, and things like well-maintained indexes and statistics objects are far more important when it comes to DB perf than the ISA of the query engine. I’d wager an ultramodern engine running in WASM or even interpreted Java will run faster on the same hardware than JET, I’d think. On a related note, why are we still stuck with 4K/8K-sized pages?

namibj 5y ago

You can combine literals into an SQL query, and doing so isn't even hard to do correctly. You'd still use parameters to feed user input, of course.

DaiPlusPlus 5y ago
SQL Server has had _autoparameterization_ opf queries for a while now: when you have multiple queries of the same general shape but with varying literal values, it automatically converts the literals to parameters (rather than vice-versa), and it's generally beneficial for performance because existing query-plans can be reused (instead of having to create a new plan for each literal value encountered): https://www.mssqltips.com/sqlservertip/2935/sql-server-simple-and-forced-parameterization/ https://www.mssqltips.com/sqlservertip/2935/sql-server-simpl...
- namibj 5y ago
  I didn't mean it that way. I mean you can combine SQL fragments, think multiple `ORDER BY` clauses.