3 ms·
> But it is just that SQL is the most terse and standard way so express logic. As someone who has written a ton of complex SQL... I couldn't disagree more. Tr
by crazygringo 3y ago
> But it is just that SQL is the most terse and standard way so express logic.
As someone who has written a ton of complex SQL... I couldn't disagree more.
Trying to shoehorn things that can trivially and intuitively be expressed in a couple of for-loops with a couple of variables, into SQL expressions making use of joins and aggregate functions and GROUP BY's and (god forbid) correlated subqueries... having to replicate subqueries because their results in one part of the query can't be re-used in another part... teaching people arcane terminology distinctions like between WHERE and HAVING... not having basic functions for calculating percentiles or even a basic median... certain basic kinds of logic operations that simply can't be done... flipping a coin on whether the query engine will use the index and execute in 10 ms, or decide to follow a different execution plan and take 5 minutes to run...
I have never encountered more bugs in business logic than in dealing with SQL. It obfuscates what should be clear. SQL isn't a solution for avoiding bugs, it's what causes so many of them.
- deadbabe 3y agoAre you using Postgres? It seems like you haven’t discovered CTEs or lateral joins.
- crazygringo 3y agoOh, I've worked with all the major engines. To which we can add: trying to remember which seemingly basic features are supported by which engines, and which aren't (e.g. percentiles are great in Postgres, but you're SOL in MySQL). That there's virtually nothing "standard" about SQL syntax or functionality at all, except for the simplest of queries and the general basic concepts.
- paulddraper 3y agoWell Java and JavaScript differ a lot too. Some similarities, but a lot of differences too.
- magicalhippo 3y agoWe're in the process of migrating our on-premise business application from SQLAnywhere to MSSQL. Imagine the fun we're having...
- eddd-ddde 3y agoI'm really curious, in the practice, what causes a product to decide "we are moving from abcSQL TO xyzSQL" ?
- crazygringo 3y agoIn that direction, it's usually going to be about robustness -- more tooling, less edge cases, a wider developer base, built-in cloud support, and so forth. The more popular a database is, the easier just everything is. Occasionally it's going to be some very specific aspect of performance limits, or a feature like spatial indexes that work in a special way. And occasionally it's going to be about cost or better integration with a particular cloud or other specific piece of tooling.
- magicalhippo 3y agoFor us there's been two major motivations, and another few lesser. First and most prominent is that Sybase was bought by SAP and SAP has pivoted SQLAnywhere from a general RMDBS to focus on mobile applications (ie more embedded). Apart from product development changing focus we've struggled to buy licenses for our customers, so the writing on the wall was clear. The seconds is that we've got some big customers who run their own MSSQL servers, and they've been pushing harder and harder for us to use their servers so they can handle administration and access control directly. A third motivation is that while we've been primarily on-prem, a lot of customers these days don't want that, so we've started offering cloud hosted options. Since we're a Windows shop Azure made sense, and as such the hosted MSSQL also makes sense and is quite convenient. Fourth is that, while MSSQL is quite anemic in terms of SQL language functionality, it does support transactional schema updates, which makes schema upgrades much less error prone. Given that MSSQL syntax support is quite limited and we now have developed tooling and library support for multiple databases, we'll probably also add support for PostgreSQL later.
- stvltvs 3y agoI agree that CTEs are a solution to some of the pain listed above, but CTEs aren't exotic. Lots of the major RDBMSs support them.
- fifilura 3y agoCTEs are an absolute must to get the right "do-this-then-that" approach.
- fifilura 3y agoI am happy for all the disagreeing posts and that HN is a place for it. It also helps me understand my colleagues. It may be how our brains are wired. For me whenever I see a for loop I see bugs. It is also about that you get the concurrency from map/reduce-type jobs for free without having to think about it. But yeah there are a couple of places you need to be careful. Avoiding duplicates and handling of NULL.
- chrsig 3y agoThe types of issues you're enumerating are really part of a learning curve that every language and environment will have. > not having basic functions for calculating percentiles or even a basic median I don't think this is really true anymore; windowing functions are pretty prolific, and I think every major database will have some percentile functions
- crazygringo 3y ago> The types of issues you're enumerating are really part of a learning curve that every language and environment will have. They're not though. That's why excellent coders often despise having to learn and use SQL, and basically just refuse to. SQL is uniquely terrible -- and I say this as someone with a career spanning from Win32 C++ to every major web technology. > every major database will have some percentile functions MySQL still doesn't. MariaDB only added it in 2018, and I've been writing queries for a lot longer than that. (And MySQL only added window functions in 2018 as well.) And remember that, for various reasons, you're often (usually?) stuck using a database engine in production that's several years old, since upgrading a database engine is not something to be taken lightly.
- chrsig 3y ago> They're not though. That's why excellent coders often despise having to learn and use SQL, and basically just refuse to. SQL is uniquely terrible -- and I say this as someone with a career spanning from Win32 C++ to every major web technology. This is basically a strawman + appeal to authority argument. I've no desire to bicker. "Uniquely terrible" is quite subjective, we'll have to agree to disagree on if it applies to sql.
- crazygringo 3y agoExcuse me, but I'm certainly not "bickering" and I resent the insinuation. And you don't seem to understand what a strawman argument is. I'm not going to write an article on this for you, but you can certainly see the sentiment frequently expressed on HN that the rise of NoSQL and of ORM's is a direct result of otherwise capable programmers not wanting to deal with SQL and its conceptual building blocks such as joins. Take that for what you will, but you might suspect there's a reason for it, and that it's not merely the learning curve for "every language".