Showing entries 1 to 10 of 29
10 Older Entries »
Displaying posts with tag: syntax (reset)
MySQL 8.0: GROUPING function

Starting with MySQL 8.0.1, the server supports the SQL GROUPING function.  The GROUPING function is used to distinguish between a NULL representing the set of all values in a super-aggregate row  (produced by a ROLLUP operation) from a NULL in a regular row.…

MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows

MySQL 8.0.1 introduces two new features which allow you to better manage situations where you have tables with hot row contention.  This issue frequently presents itself in scenarios such as worker threads all accessing the same tables trying to find new work, and ecommerce websites trying to keep accurate inventory counts.…

MySQL 8.0: Refactoring and Improving the Parser

In 8.0 we have introduced another bunch of parser refactoring worklogs:

  • WL#8067 (me): “Refactoring of the CREATE TABLE statement” with its subtasks:
    • WL#8434: Refactoring of partitioning-related stuff.
    • WL#8435: Cleanup and refactoring column definition stuff.
    • WL#8433: Separate DD commands from regular SQL queries in the parser grammar.

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:

  • Oracle GoldenGate 12.1.2.1.1  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]
Should I Put That Table Alias or Not?

Infrequent SQL developers often get confused about when to put parentheses and/or aliases on derived tables. There has been this recent Reddit discussion about the subject, where user Elmhurstlol was wondering why they needed to provide an alias to the derived table (the subselect with the UNION) in the following query:

SELECT AVG(price) AS AVG_PRICE
FROM (
  SELECT price from product a JOIN pc b
  ON a.model=b.model AND maker='A'
  UNION ALL
  SELECT price from product a JOIN laptop c
  ON a.model=c.model and maker='A'
) hello

The question really was about why the "hello" table alias was necessary, because often it seems not to be required.

Here’s what the SQL standard states

If in doubt, it is often useful to consider the SQL standard about the rationale behind some …

[Read more]
Merging tables with INSERT...ON DUPLICATE KEY UPDATE

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?

Hints

Consider the following queries as hints to the above questions:

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

Answers

  • 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]
Showing entries 1 to 10 of 29
10 Older Entries »