Row value expressions are something very powerful in SQL. They have been around since the early days of standard SQL, e.g. in SQL 1992, even if not all databases implement them correctly, still today. As always, the NULL predicate is one that is a bit tricky to understand, also in the context of row value … Continue reading Row value expressions and the NULL predicate
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
Big Fail. How to recognise bad software
There's some subtlety in how "bad software" can be recognised from error messages as these... ;-)
ElSql, a new external SQL DSL for Java
Stephen Colebourne who is frequently commenting on the lambda-dev and other Java 8 mailing lists, has recently published an idea he has been having for a while: ElSql, a new external SQL DSL for Java. An example SQL statement is given on the blog posts or on GitHub: @NAME(SelectBlogs) @PAGING(:paging_offset,:paging_fetch) SELECT @INCLUDE(CommonFields) FROM blogs WHERE … Continue reading ElSql, a new external SQL DSL for Java
The IS DISTINCT FROM predicate
The SQL-1999 standard specifies a useful IS DISTINCT FROM predicate, that behaves slightly different from the regular not equal comparison predicate. Here is its simple definition: 8.13 <distinct predicate> Format <distinct predicate> ::= <row value expression 3> IS DISTINCT FROM <row value expression 4> <row value expression 3> ::= <row value expression> <row value expression … Continue reading The IS DISTINCT FROM predicate
How to get Oracle execution plans with Starts, E-Rows, A-Rows and A-Time columns
This can probably be found elsewhere as well, but here's a short wrap-up how to get the most out of your execution plans, quickly 1. Be sure the actual rows and time statistics are collected. You can do this with -- login as user sys alter system set statistics_level = all; 2. Execute your bad … Continue reading How to get Oracle execution plans with Starts, E-Rows, A-Rows and A-Time columns
Alvor: Static SQL analysis in Strings passed to JDBC
I have recently discovered this nice Eclipse plugin here: https://code.google.com/p/alvor/ It evaluates String, StringBuilder, StringBuffer, CharSequence and many other types passed to JDBC method for subsequent execution. It doesn't do a bad job at this, even if it is in beta mode. The rate of false positives that I have experienced is around 20% for regular SQL statements, and … Continue reading Alvor: Static SQL analysis in Strings passed to JDBC
Serious SQL: A “convex hull” of “correlated tables”
Now THIS is an interesting, and challenging question on the jOOQ user group: https://groups.google.com/d/topic/jooq-user/6TBBLYt9eR8/discussion Say you have a big database with lots of tables and foreign key references. Now you would like to know all tables that are somehow inter-connected by their respective foreign key relationship "paths". You could call this a "convex hull" around … Continue reading Serious SQL: A “convex hull” of “correlated tables”
You never stop learning about Oracle features
Oracle's name is no coincidence. It is truly an oracle, telling you mystical, secret things about your database. It may take great expertise and skill to optimally extract that knowledge from this monster. While it implements vast parts of the SQL:2008 standard, it ships with a lot of features no other database has, but which … Continue reading You never stop learning about Oracle features
SLICK, integrating SQL into Scala
Now it's official - even if version numbers are still preceded by a "zero" major release: SLICK has been publicly announced by Typesafe: http://blog.typesafe.com/introducing-slick http://finance.yahoo.com/news/typesafe-announces-scala-language-integrated-100000262.html http://java.dzone.com/articles/slicker-scala-stack-qa-martin SLICK stands for Scala Language-Integrated Connection Kit, which is more or less the Scala equivalent for LINQ-to-SQL. Note that I say LINQ-to-SQL, not LINQ in general, as Scala already has … Continue reading SLICK, integrating SQL into Scala
MySQL Bad Idea #384
MySQL is a database of compromise. Compromise between running a production-ready relational database and being popular with all sorts of hackers - mostly the ones that don't really like SQL. And because they don't really like SQL, they choose MySQL, as MySQL is very forgiving. It is just as forgiving as their favourite language PHP, … Continue reading MySQL Bad Idea #384