4 ms·

You might need to explain yourself a little more because I'm confused by your examples: > "- lack of statically typing guarantees (for example, no guarantee th

• marcosdumay 10y ago
  > I would have described SQL as statically typed because columns are given a type when created. The biggest problem is that SQL has no well defined "compile time". For all practical purposes, it's always an interpreted language. I can't check my code against the production database without running it. > You don't need to specify the table name for each column if you're only querying one table. I do think the GP was about doing stuff like that: SELECT * FROM function_that_returns_table();
  • yawaramin 10y ago
    You can check your code without running it on databases which support it, e.g. Postgres https://www.postgresql.org/docs/current/static/sql-prepare.html https://www.postgresql.org/docs/current/static/sql-prepare.h... db=# prepare t as select 1 from a; ERROR: relation "a" does not exist at character 28 STATEMENT: prepare t as select 1 from a; ERROR: relation "a" does not exist LINE 1: prepare t as select 1 from a; ^ Time: 0.684 ms
  • laumars 10y ago
    > The biggest problem is that SQL has no well defined "compile time". For all practical purposes, it's always an interpreted language. I can't check my code against the production database without running it. I get what you're saying but I've seen that as a pretty common problem outside of SQL as well. Firstly, and sorry for the nitpicking, but it will explain my point, I don't think the "interpreted language" is the right description here. Nearly all modern "interpreted" languages are still technically compiled, albeit it's bytecode that's then executed by the language runtime. Usually when people talk about interpreted languages they're talking about JIT compiled (like Perl, Python, PHP, Ruby and Javascript, to name a few) but technically one could argue that Java and other, similar AOT compiled languages are also interpreted. Even some AOT compilers that produce a Windows PE's (.EXE files) and Linux ELF (for example) will be interpreted (either that or JIT compiled to machine code when executed). Anyhow, the reason I raise that point is because there are a lot of parallels between SQL and other JIT compiled languages. With JIT compiled languages, they are obviously still compiled - even if it is just to bytecode. But in many cases the compiler will perform many of the same sanity checks that an AOT compiler would. Likewise SQL is compiled albeit by the RDBMS to something that's optimised for execution on that platform and it's during that compilation that table names and types are checked. And much like a JIT compiled language, you have to make a few assumptions about the running environment when you write the code .eg the same and correct Perl cpan or Javascript npm modules are installed on dev, UAT and live environments; or with databases you have to make assumptions that your dev database schemas mirror your live database. The problem RDBMS suffer from is that some of the solutions for the JIT compiled environment problem (eg docker) don't work so well for databases because you're dealing with data which can be sensitive and is often in flux, rather than code which should be pretty much unchanged across the deployment pipeline (whatever that may be). The data is the key problem here. If you wrote a C++ routine to handle data encoded in JSON files then you'd still be at the mercy of executing your program to check it can parse the data correctly (eg the field names are correct and the data exists). Likewise if you wrote the same routine in Haskell. Or if you replaced JSON with XML, YAML or CSV files. I'm not saying SQL is without its warts though nor that a functional-paradigm reimagining wouldn't be better. But I do think some of the biggest challenges with SQL is data related and thus could very easily rear their proverbial heads in other languages too (particularly if you don't want to trust your language to perform too much background magic for fear of risking the integrity of the data you're retrieving).
• js8 10y ago
  I think marcosdumay already explained it pretty well. > I would have described SQL as statically typed because columns are given a type when created. Both statically and dynamically is a misnomer because it's akin to something more in-between - like a dynamic language which can create, say, an empty list and declare it can only contain integers. Just quickly though - I believe static typing helps productivity, because you don't have wait for some edge case in your program (in this case infrequently invoked query, since SQL is often embedded in other language) to fail. There are other benefits, like Quickcheck. I think better term (which would explain the distinction between schema creation and query compilation) would be static typechecking of queries against the schema. > The issue arises with multiple tables In general, SQL is quite adhoc in what it lets you do, and anything more complicated you need to resolve with code generation (for example running same query over different tables). It's very hard to reuse SQL code. That's ugly, and functional languages offer a better solution - high-order functions. I think what I really want is to deal with schema (and other things, such as result set) as first-class types. I think the language should be functional, but also total (every function will finish, no recursion allowed), to make it easy for compilers (query engines).
  • laumars 10y ago
    It really depends on which RDBMS we are talking about. sqlite is definitely not statically typed. Values in cells can be compared against integers or strings using any preferred operators. However others relational databases such as Oracle support are strict about data types and even support inlining functions inside SQL (technically it's PL/SQL rather than ANSI SQL) for code reuse exactly like the feature enhancement you were describing. Most enterprise RDBMSs I wouldn't say we're dynamic; you define your table at the start of your code, Inc storage types (like you would declare global variables in a strictly typed imperative programming language) and you get compile time errors when executing the SQL if the datatypes are mismatched, just like you would in most modern strictly typed languages. The problem people typically raise about SQL is really more a matter of tooling. eg there isn't much in the way of Visual Studio or IntelliJ for SQL. The best I've used is MySQL Workbench but even that is buggy and often crash prone. However most developers generally write code outside of an SQL IDE because they're either inlining their SQL inside another language (eg via paramatised SQL functions in Go, PHP, Java or whatever) or a DB guy so skilled enough to write their SQL outside of an IDE (much like how some functional or imperative language developers use text editors rather than IDEs).