Showing entries 1 to 10 of 25
10 Older Entries »
Displaying posts with tag: syntax (reset)
Inline JSON Path Expressions in MySQL 5.7

MySQL 5.7.9 has a new feature, that simplifies queries that deal with JSON data and makes more human-readable: inlined JSON path expressions. Now you can do following:

mysql> CREATE TABLE employees (data JSON);
Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}');
Query OK, 1 row affected (0,00 sec)

mysql> SELECT * FROM employees WHERE data->'$.id'= 2;

| data                     |
| {"id": 2, "name": "Joe"} |
1 row in set (0,01 sec)

Let’s take a closer look at the expression in the WHERE clause.…

Log Buffer #428: A Carnival of the Vanities for DBAs

The Log Buffer Edition once again is sparkling with some gems, hand-picked from Oracle, SQL Server and MySQL.


  • Oracle GoldenGate  is now certified with Unity 14.10.  With this certification, customers can use Oracle GoldenGate to deliver data to Teradata Unity which can then automate the distribution of data to multiple Teradata databases.
  • How do I change DNS servers on Exadata storage servers.
  • Flushing Shared Pool Does Not Slow Its Growth.
[Read more]

Had a case recently where I had to merge data from two identically structured tables containing nearly identical data.

"Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one tables (while others can be NULL in the second).

Otherwise no contradicting data: it was not possible for some data to be "3" in one table and "4" in the other.

How do you create a merge of the tables, such that all missing rows are completed, and NULLs replaced by actual values when possible?

pt-table-sync comes to mind: one can do a bidirectional syncing of two tables, and actually stating how to resolve ambiguities (like "greater value wins"). Very powerful! An example would be:

[Read more]
Pop quiz: funny syntax

The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript.

Can you guess the results of the following statements?

Pop quiz 1

SET @x := 7;
SELECT ++@x;

What is the computation result? What will be the value of @x?

Pop quiz 2

SET @ := 4;
SELECT @ + @'' + @``

What is the computation result?

Pop quiz 3

SET @a := 2;
SELECT @a = @'a' = @`a`

Do we get TRUE or FALSE? When?


Consider the following queries as hints to the above questions:

SELECT +++++-@x;
SELECT @ = @'', @ = @``
SELECT (@a = @'a') = @`a`


  • Pop quiz 1

++@x is interpreted as +(+(@x)), which is in turn evaluated as …

[Read more]
Using CURDATE() the wrong way

It has been a while since I wrote on this blog. Basically I had too much on my mind (expanding my department, holidays, etc) to actually write here and I’ll promise to post more regularly from now onwards.

Anyway, as the title already suggests: I found out how you can use CURDATE() in a wrong way. One of the developers in my company asked me to help him out as his code all of a sudden did not work properly anymore. Or even better: it used to process several thousands of rows and all of a sudden it processed none.

I looked at his code snippet and it was quite a long query with a lot of brackets:

SELECT SUM(some_count_col), logdate, loghour FROM logs
WHERE (logdate = CURDATE() AND loghour = HOUR(NOW())
GROUP BY logdate, loghour;

Column wise logdate is of the type DATE and …

[Read more]
MySQL joins: ON vs. USING vs. Theta-style

What is the difference between the following three syntaxes?

SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id)
SELECT * FROM film JOIN film_actor USING (film_id)
SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id

The difference is mostly syntactic sugar, but with a couple interesting notes.

To put names, the first two are called "ANSI-style" while the third is called "Theta-style".

Theta style

On the FROM clause, tables are listed as if with Cartesian products, and the WHERE clause specifies how the join should take place.

This is considered to be the "old" style. It is somewhat confusing to read. Consider the following query:

SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id AND actor_id = 17 …
[Read more]
Syntax of the day: IS TRUE and IS FALSE

What makes for a true statement?

We usually test statements using a WHERE clause:

SELECT * FROM world.City WHERE Population > 1000000

The "Population > 1000000" statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF():

SET @val := 7;
SELECT IF(@val > 2, 'Yes', 'No')


The two are keywords. They also map for the numerals 1 and 0, as follows:

|    1 |     0 |

Like in the C programming language, a nonzero value evaluates to a true value. A zero evaluates to false. A …

[Read more]
Quoting text JavaScript/Python style

Unless your MySQL is configured to use ANSI_QUOTES in sql_mode, you are able to quote your text in one of two forms: using single quotes or double quotes:

UPDATE world.Country SET HeadOfState = 'Willy Wonka' WHERE Code='USA'
UPDATE world.Country SET HeadOfState = "Willy Wonka" WHERE Code="USA"

This makes for JavaScript- or Python-style quoting: you quote by your needs. Say you have a text which includes single quotes:

It is what you read when you don't have to that determines what you will be when you can't help it. - Oscar Wilde

You wish to insert this text to some tables. You could go through the trouble of escaping it:

INSERT INTO quotes (quote, author) VALUES (
  'It is what you read when …
[Read more]
MySQL terminology: processes, threads & connections

There’s some confusion in the MySQL terminology for processes, threads & connections, which I will try to resolve. I can’t explain the full nature of what processes and threads are; please see Wikipedia [1] [2] for that. But here’s some basics with regard to MySQL:

  • MySQL server is a single process application.
  • It is multithreaded.
  • It (usually) acts as a TCP/IP server, accepting connections.
  • Each connection gets a dedicated thread.
  • These threads are sometimes named processes, and sometimes they’re referred to as connections.

The last part is where confusion arises, so let me discuss again the use of threads and connections in MySQL.

MySQL truly is a single process server. It is multi …

[Read more]
SQL: good comments conventions

I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code.

I list four major places where SQL comments are helpful. I’ll use the sakila database. It is originally scarcely commented; I’ll present it now enhanced with comments, to illustrate.

Table definitions

The CREATE TABLE statement allows for a comment, intended to describe the nature of the table:

CREATE TABLE `film_text` (
 `film_id` smallint(6) NOT NULL,
 `title` varchar(255) NOT NULL,
 `description` text,
 PRIMARY KEY (`film_id`),
 FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Reflection of `film`, used for FULLTEXT search.'

It’s too bad the comment’s max length is 60 characters, though. However, it’s a very powerful …

[Read more]
Showing entries 1 to 10 of 25
10 Older Entries »