maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Igor,

Please do "reply all" when replying.

On 04/12/2018 01:28 AM, Igor Babaev wrote:
> Alexander,
> 
> If you have the same behaviour you have to use
> 
> SELECT * FROM t1 UNION (SELECT * FROM t2 FOR UPDATE).

Thanks. This worked fine for me.

> 
> Otherwise it's considered as
> 
> (SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE).

What does this syntax mean 10.3-MDEV-11953?

Which records should be locked? From t1, from t2, or from both?

It seems that it locks nothing.
If so, shouldn't this be disallowed?

This query (without parentheses) also locks nothing:

SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;

> 
> MySQL manual says that 'FOR UPDATE' must follow  ORDER BY,
> 
> but it does not tell you how to interpret FOR  UPDATE in unions.

The old behavior was to interpret UNION parts separately.
This locked only t2 in 10.3:

SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;

In 10.3-MDEV-11953 it locks nothing. Upgrade looks dangerous.


Can we do this way:

1. In 10.4: disallow FOR UPDATE applied to the entire UNION.

2. In 10.3: Add a warning which tells that this feature
   is deprecated and suggests to use parentheses
   around union parts when a non-parenthesized union term
   is used with FOR UPDATE ?



> 
> Anyway you can't use anymore:
> SELECT c FROM t1 ORDER BY a
> UNION
> SELECT c FROM t2 ORDER BY b
> ORDER BY c.
> 
> You have to put it so:
> (SELECT c FROM t1 ORDER BY a)
> UNION
> (SELECT c FROM t2 ORDER BY b)
> ORDER BY c.
> 
> (see the standard).

I like this change.

> 
> It means that the ORDER BY clause followed a union relates to the union.
> The same is true for 'FOR UPDATE' because it follows optional ORDER BY/
> LIMIT clauses.
> 
> 
> Regards,
> Igor.
> 
> 
> On 04/11/2018 09:10 AM, Alexander Barkov wrote:
>> Hi Sanja, and Igor,
>>
>>
>> (resending with Igor on CC)
>>
>>
>> I noticed a FOR UPDATE behavior change in 10.3-MDEV-11953
>>
>> In 10.3 I run this script in a client session:
>>
>> CREATE OR REPLACE TABLE t1 (a INT NOT NULL PRIMARY KEY);
>> CREATE OR REPLACE TABLE t2 (a INT NOT NULL PRIMARY KEY);
>> INSERT INTO t1 VALUES (1);
>> INSERT INTO t2 VALUES (2);
>> BEGIN;
>> SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
>>
>> Notice no COMMIT yet! It returns this result:
>> +---+
>> | a |
>> +---+
>> | 1 |
>> | 2 |
>> +---+
>>
>> Now I open a new console, start a new client session and run this script:
>>
>> BEGIN;
>> SELECT * FROM t2 FOR UPDATE;
>>
>> The second session gets locked, as expected.
>>
>> Now I return to the first console and run "COMMIT;".
>> The second console gets unlocked and returns
>> +---+
>> | a |
>> +---+
>> | 2 |
>> +---+
>>
>>
>> Now if I do the same in 10.3-MDEV-11953, the second session does not get
>> locked, it returns the result immediately, even before I typed "COMMIT;"
>> in the first session.
>>
>>
>> Note, if in 10.3-MDEV-11953 I start a transaction with a simpler query
>> (without UNION) in the first session:
>>
>> BEGIN;
>> SELECT * FROM t2 FOR UPDATE;
>>
>> and run the same script in the second session
>>
>> BEGIN;
>> SELECT * FROM t2 FOR UPDATE;
>>
>> then the second session gets locked as expected.
>>
>>
>> Is this behavior change expected?
>>
>>
>> Thanks!
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-developers
>> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-developers
>> More help   : https://help.launchpad.net/ListHelp
>>