FOR … END FOR

Let’s start by combining one new MariaDB feature — VALUES (5) which is the standard equivalent of the old non-standard “SELECT 5” — with another new feature in MariaDB 10.3.5, the FOR … END FOR statement.

mariadb>BEGIN NOT ATOMIC
> DECLARE v INT DEFAULT 0;
> FOR a IN (VALUES (7)) DO SET v = v + 1; END FOR;
> SELECT v;
>END;
Error 2013 (HY000) Lost connection to MySQL server during query

Oops. However, the MariaDB folks now know about this, it’s bug MDEV-15940″ and they know about the other bugs that I’ll mention in this post, so there’s no problem provided you wait for the next version.

FOR … END FOR works prettily so I decided to describe it. The official documentation isn’t out yet so this could change.

FOR x IN (subquery) DO … END FOR

Example:

BEGIN NOT ATOMIC
  DECLARE v INT DEFAULT 0;
  FOR a IN (SELECT 5) DO SET v = v + 1; END FOR;
  SELECT v;
END;

The SELECT will return ‘1’ because the SET statement happened once, because SELECT 5 returns 1 row. We must put (SELECT 5) inside parentheses because there must be a way to find where it ends — the word DO is not reserved and therefore is useless as a marker for the parser.

FOR x IN cursor DO … END FOR

Example:

CREATE TABLE t (s1 INT);
INSERT INTO t VALUES (1),(2),(3);
BEGIN NOT ATOMIC
  DECLARE v2, v3 INT DEFAULT 0;
  BEGIN
    DECLARE cn CURSOR FOR SELECT * FROM t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v3 = 1;
    FOR cn_record IN cn
    DO
      SET v2 = v2 + cn_record.s1;
    END FOR;
  END;
  SELECT v2, v3;
END;

FOR cn_record IN cn means “loop with cursor cn, which was declared earlier”. And cn_record according to MariaDB is a row variable that can be used within the loop, so cn_record.s1 is the value of s1 within a given row of table t.

Given that table t has 3 rows containing (1),(2),(3), and given that the obvious intent is that v2 will end up with the sum, you might think the SELECT will return v2 = 6, eh? Well, you’re thinking right, it does. This is a way to go through a cursor, with each iteration doing an automatic FETCH.

And, since there’s a CONTINUE HANDLER that says we’ll set v3 = 1 when there are no more rows to fetch, you might think the SELECT will return v3 = 1, eh? Well, you’re thinking reasonably (I think), but it doesn’t. The handler isn’t brought into play, the NOT FOUND condition is automatically cleared when the loop ends.

Summary: this kind of FOR does automatic cursor-open and cursor-fetch, but not automatic cursor-close. To me that looked like a bug, the way I interpret the standard document, “CLOSE cn;” is supposed to happen. And MariaDB agrees, it’s bug MDEV-15941.

FOR x IN low-number .. high-number DO … END FOR

Example:

SET @v = 0;
CREATE PROCEDURE p()
BEGIN
  FOR a IN 1 .. 3 DO SET @v = @v + a; END FOR;
  SELECT @v;
END;

MariaDB 10.3 comes with a boatload of Oracle/PostgreSQL syntax, often known as “PL/SQL” (their deviation from the standard “SQL/PSM” syntax which MySQL/MariaDB have followed in the past). But all the PL/SQL stuff is supplied if and only if you say

SET @@sql_mode='oracle';

The FOR … END FOR statement is an exception, it works in the default mode too. That’s deliberate. And it’s equivalent to

SET a = 1; WHILE a <= 3 DO ... SET a = a + 1; END WHILE;

So the SELECT in the above example displays '6'.

The ".." is a new operator, vaguely similar to the "range operators" in languages like Ruby and Perl. But SQL already has a range operator, it's called BETWEEN. And now MariaDB won't be able to accept the SQL Server syntax where ".." is for qualifiers ("a..b" means "schema a + default table + column b").

I encountered a problem during the test, with:
FOR a IN (SELECT 1) .. 3 DO SET @v = @v + a; END FOR;
Technically this should be legal because “low-number” can be any expression, including a subquery. In this case, though, subqueries mean something else, so it is not legal. This is now bug MDEV-15944″.

I enthusiastically recommend: don’t use this, use WHILE.

Differences from the standard

My first example looked like this:

  FOR a IN (SELECT 5) DO SET v = v + 1; END FOR;

In standard SQL it would have looked like this:

  FOR (VALUES (5)) DO SET v = v + 1; END FOR;

… The standard doesn’t require mentioning “a” when there is no need to refer to “a” inside the loop. The fact that the standard would use “VALUES (5)” is, as we’ve seen, something that MariaDB will soon support too.

My second example looked like this:

    FOR cn_record IN cn
    DO
      SET v2 = v2 + cn_record.s1;
    END FOR;

In standard SQL it would have looked like this:

    FOR cn_record AS cn
    DO
      SET v2 = v2 + s1;
    END FOR;

So it’s a difference between “IN” and “AS”, and between “cn_record.s1” and “s1”. However, I could have said “cn_record.s1” in standard SQL too, it’s optional when there’s no ambiguity.

There are serious matters that underlie that innocent-looking difference with “cn_record.”, about which I now digress.

Shadowing, a Digression

In the following, should “SELECT v FROM t;” display 1, or 2, or 3?

BEGIN NOT ATOMIC
  a: DECLARE v INT DEFAULT 1;
  BEGIN
    b: DECLARE v INT DEFAULT 2;
    CREATE TABLE t (v INT);
    INSERT INTO t VALUES (3);
    SELECT v FROM t;
  END;
END;

Answer: in MariaDB it’s 2. Inner scope beats outer scope, and variable declaration beats table definition. This is an old MySQL flaw that MariaDB has inherited (Bug #5967 Stored procedure declared variable used instead of column). The workaround, if you actually want “column v of table t”, is to use a qualifier, as in “SELECT t.v FROM t;”.

But what if you wanted to refer to the first declared variable? That would be easy too, in standard SQL you would qualify with the label, as in “SELECT a.v FROM t;”. Unfortunately — another old MySQL flaw that MariaDB has inherited — you can’t use [label.]variable_name for qualification.

As a result of these two flaws, we could have trouble in our FOR … END FOR loop if we used unqualified names like “s1”. Therefore in MariaDB you have to say “cn_record.s1” if you want the FOR variable, that is, qualification is compulsory. That’s a solution.

But the solution depends on a third flaw.

In standard SQL in this FOR loop “cn_record” becomes a label, and that’s why “cn_record.s1” would be legal — cn_record is a label qualifier. That’s not the case with Oracle/MariaDB, where “cn_record” is a variable with type = ROW — cn_record is a row-variable qualifier. The effect is the same for the particular example that I used, but that’s just luck.

The flaws do not mean that the implementor did something wrong for FOR … END FOR, rather it means that the blame lies in MySQL’s history. It would be grossly wrong to blame the standard, which has no flaws.

Differences from the Standard, continued

My third example looked like this:

  FOR a IN 1 .. 3 DO SET @v = @v + a; END FOR;

In standard SQL it would have looked like this:

  [empty prairie with crickets chirping in the darkness]

For more about the original plan for adding FOR … END FOR to MySQL in a standard way, go to “WL#3309: Stored Procedures: FOR statement” and click “High-level architecture”.

ocelotgui

Of course we’ve changed Ocelot’s GUI client for MySQL/MariaDB so that it recognizes most of the new syntax in MariaDB 10.3. We haven’t changed the debugger, though, so it won’t handle statements containing FOR … END FOR.

, April 22, 2018. Category: MariaDB.

About pgulutzan

Co-author of four computer books. Software Architect at MySQL/Sun/Oracle from 2003-2011, and at HP for a little while after that. Currently with Ocelot Computer Services Inc. in Edmonton Canada.