[MDEV-9516] type error when setting session variable Created: 2016-02-03  Updated: 2016-03-28  Resolved: 2016-03-21

Status: Closed
Project: MariaDB Server
Component/s: Admin statements
Affects Version/s: 10.1.9, 10.1
Fix Version/s: 10.1.13

Type: Bug Priority: Major
Reporter: Moritz Vondano Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: regression
Environment:

XAMPP bundle on Windows 8.1


Sprint: 10.1.13

 Description   

The following query fails:

  • SET SESSION wait_timeout = GREATEST(28000, @@wait_timeout)

Error:
#1232 - Incorrect argument type to variable 'wait_timeout`

There should be no type conversion issue as those queries work:

  • SET SESSION wait_timeout = 28000;
  • SET SESSION wait_timeout = @@wait_timeout;
  • SELECT GREATEST(28000, @@wait_timeout);

please see this SO question for detail:
http://stackoverflow.com/questions/35187378/mariadb-type-error-when-setting-session-variable



 Comments   
Comment by Elena Stepanova [ 2016-02-06 ]

Thanks for the report.

The problem was introduced by this commit:

commit 09b87d6293b4b41321ba98366d5d7ade9ad681d3
Author: Alexander Barkov <bar@mariadb.org>
Date:   Wed Sep 30 10:05:16 2015 +0400
 
    MDEV-8871 Wrong result for CREATE TABLE .. SELECT LEAST(unsigned_column,unsigned_column)

Comment by Alexander Barkov [ 2016-03-16 ]

Note, the same problem is repeatable in 10.0 with other hybrid type functions:

SET SESSION wait_timeout = COALESCE(28000, @@wait_timeout);
SET SESSION wait_timeout = IFNULL(28000, @@wait_timeout);
SET SESSION wait_timeout = CASE WHEN TRUE THEN 28000 ELSE @@wait_timeout END;

So the patch for MDEV-8871 only made LEAST detect its data type consistently with COALESCE, IFNULL, CASE.
The problem was not really "introduced" by MDEV-8871.
It exists for a long time and should be fixed for all hybrid functions at once.

Comment by Alexander Barkov [ 2016-03-16 ]

Possible fixes:
1. Fix hybrid functions to fallback to INT or BIGINT if the result data type was detected as DECIMAL(N,0) with a small N.
Note, fallback should work only if all arguments are of INT-alike types actually (and should keep DECIMAL if any of the arguments was DECIMAL).
2. Fix int-type variables
a. Accept assignment of a DECIMAL(N,0) expression with a small N.

  • Allow integer type variables to accept expressions of the DECIMAL(N, 0) type, providing that N fits into the underlying INT data type
  • Fix data type of @@wait_timeout to return INT(11), instead of BIGINT(21), otherwise the "N fits" requirement will be FALSE.

b. Another option is just to accept all DECIMAL(N, 0), even if N is big enough, and produce a warning on overflow.

c. Another option is to accept all DECIMAL(N,M), even if M>0, and produce a warning on fraction truncation.

Comment by Alexander Barkov [ 2016-03-16 ]

During a discussion, Serg and Bar decided to accept DECIMAL(N,0), no matter if N is big enough, and produce an error if the value does not fit.

Comment by Sergei Golubchik [ 2016-03-21 ]

ok to push

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