[MDEV-6942] SET SESSION statement combined with SET STATEMENT has no effect if it's executed for the same variable Created: 2014-10-24  Updated: 2016-01-22  Resolved: 2016-01-22

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-5231 Per query variables from Percona Serv... Closed
relates to MDEV-6923 Testing for SET STATEMENT .. FOR (MDE... Closed
Sprint: 10.1.11

 Description    

MariaDB [test]> select @@sort_buffer_size;
 
+--------------------+
 
| @@sort_buffer_size |
 
+--------------------+
 
|            2097152 |
 
+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> set statement sort_buffer_size = 100000 for set session sort_buffer_size = 200000;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> select @@sort_buffer_size;
 
+--------------------+
 
| @@sort_buffer_size |
 
+--------------------+
 
|            2097152 |
 
+--------------------+
 
1 row in set (0.00 sec)

If it's not expected to work, there should be an error or a warning.

Percona has a more generic flavor of the problem, where it doesn't work for any combination of variables (https://bugs.launchpad.net/percona-server/+bug/1341438), while in our case it happens when the same variable appears in SET STATEMENT and SET SESSION.



 Comments   
Comment by Oleksandr Byelkin [ 2014-10-26 ]

Why it does not work?

SET SETSTATEMENT store old value, SET sets new one and SET STATEMENT restore old one.

i.e. SET STATEMENT equal to:

SET @save=@@variable;
<STATEMENT>;
SET SESSION variable=@save;

Comment by Elena Stepanova [ 2014-10-26 ]

It's an edge case when it comes to the definition of SET STATEMENT, but the common sense suggests that in this situation it isn't supposed to restore the saved value, since it was changed explicitly in <STATEMENT>. It doesn't look meaningful in this simplest form, but imagine that <STATEMENT> is in fact a complicated code, e.g. a stored procedure, and the value changes somewhere deep inside. It would be very unexpected that the whole procedure works smoothly, but the value does not get changed at the end.
That's why it's more reasonable to produce an error saying that a value cannot be changed in <STATEMENT>, although there might be side-effects that should be thought through.

Re-opening so you could re-consider. Please think about it from the user's perspective, not from the implementation side.

Comment by Oleksandr Byelkin [ 2015-03-11 ]

IMHO it should be just documented.

Comment by Oleksandr Byelkin [ 2016-01-22 ]

Documentation fixed: https://mariadb.com/kb/en/mariadb/set-statement/

Generated at Thu Feb 08 07:15:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.