| Bug #45370 | IF() values are evaluated differently in a regular SQL vs Prepared Statements | ||
|---|---|---|---|
| Submitted: | 8 Jun 2009 12:13 | Modified: | 28 Mar 2013 16:11 |
| Reporter: | Leandro Morgado | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 5.0.56, 5.1.35 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Jun 2009 12:15]
Leandro Morgado
Here are some more tests/workaround that might help:
1) Things work fine is @var1 is not declared as a string:
==============================================================================
master [localhost] {msandbox} ((none)) > set @var1=0.038687 ;Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} ((none)) > select @var1 , if( @var1 = 0 , 1 , @var1 ) as sqlif ;Field 1: `@var1`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 83
Max_length: 8
Decimals: 30
Flags: BINARY
Field 2: `sqlif`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 83
Max_length: 8
Decimals: 30
Flags: BINARY
+----------+----------+
| @var1 | sqlif |
+----------+----------+
| 0.038687 | 0.038687 |
+----------+----------+
1 row in set (0.00 sec)
master [localhost] {msandbox} ((none)) > prepare fail_stmt from "select ? , if( ? = 0 , 1 , ? ) as ps_if_fail" ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
master [localhost] {msandbox} ((none)) > execute fail_stmt using @var1 , @var1 , @var1 ;
Field 1: `?`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 9
Max_length: 8
Decimals: 6
Flags: BINARY
Field 2: `ps_if_fail`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 9
Max_length: 8
Decimals: 6
Flags: BINARY
+----------+------------+
| ? | ps_if_fail |
+----------+------------+
| 0.038687 | 0.038687 |
+----------+------------+
1 row in set (0.00 sec)
==============================================================================
2) Things works if we compare against 0.00 in the IF(). Note that 0.0 does not work.
==============================================================================
master [localhost] {msandbox} ((none)) > set @var1='0.038687' ;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} ((none)) > select @var1 , if( @var1 = 0 , 1 , @var1 ) as sqlif ;
Field 1: `@var1`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 8192
Max_length: 8
Decimals: 31
Flags:
Field 2: `sqlif`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 8192
Max_length: 8
Decimals: 31
Flags:
+----------+----------+
| @var1 | sqlif |
+----------+----------+
| 0.038687 | 0.038687 |
+----------+----------+
1 row in set (0.00 sec)
master [localhost] {msandbox} ((none)) > prepare fail_stmt from "select ? , if( ? = 0.0 , 1 , ? ) as ps_if_fail" ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
master [localhost] {msandbox} ((none)) > execute fail_stmt using @var1 , @var1 , @var1 ;
Field 1: `?`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 8
Max_length: 8
Decimals: 0
Flags: NOT_NULL
Field 2: `ps_if_fail`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 8
Max_length: 1
Decimals: 0
Flags: NOT_NULL BINARY
+----------+------------+
| ? | ps_if_fail |
+----------+------------+
| 0.038687 | 1 |
+----------+------------+
1 row in set (0.00 sec)
master [localhost] {msandbox} ((none)) > prepare fail_stmt from "select ? , if( ? = 0.00 , 1 , ? ) as ps_if_fail" ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
master [localhost] {msandbox} ((none)) > execute fail_stmt using @var1 , @var1 , @var1 ;
Field 1: `?`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 8
Max_length: 8
Decimals: 0
Flags: NOT_NULL
Field 2: `ps_if_fail`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 8
Max_length: 8
Decimals: 0
Flags: NOT_NULL BINARY
+----------+------------+
| ? | ps_if_fail |
+----------+------------+
| 0.038687 | 0.038687 |
+----------+------------+
1 row in set (0.00 sec)
master [localhost] {msandbox} ((none)) >
==============================================================================
[8 Jun 2009 12:16]
Leandro Morgado
Last test
3) If we cast the ? parameter in the IF() to a CHAR the comparison fails as expected.
==============================================================================
master [localhost] {msandbox} ((none)) > set @var1='0.038687' ;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} ((none)) > select @var1 , if( @var1 = 0 , 1 , @var1 ) as sqlif ;
Field 1: `@var1`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 8192
Max_length: 8
Decimals: 31
Flags:
Field 2: `sqlif`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 8192
Max_length: 8
Decimals: 31
Flags:
+----------+----------+
| @var1 | sqlif |
+----------+----------+
| 0.038687 | 0.038687 |
+----------+----------+
1 row in set (0.00 sec)
master [localhost] {msandbox} ((none)) > prepare fail_stmt from "select ? , if( CAST(? AS SIGNED) = 0 , 1 , ? ) as ps_if_fail" ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
master [localhost] {msandbox} ((none)) > execute fail_stmt using @var1 , @var1 , @var1 ;Field 1: `?`
Catalog: `def`
Database: ``
Table: ``
[8 Jun 2009 15:49]
John Dzilvelis
Another variation on a work-around is to cast as decimal with the desired precision. prepare pass_stmt from "select if( cast(? as decimal(10,6)) = 0 , 1 , cast( ? as decimal(15,6)) ) as ps_if_pass" ; execute pass_stmt using @var1 , @var1 ;
[28 Mar 2013 16:11]
Paul DuBois
Noted in 5.1.70, 5.5.32, 5.6.12, 5.7.2 changelogs. IF() function evaluations could produce different results when executed in a prepared versus nonprepared statement.
Description: When passing a user @var to IF(), we will get different results if the IF() is inside a prepared statement. It looks almost as the data types are different when using prepared statements. Multiple things seem to affect the data types on @var1 and how it gets compared in the IF() function. Please see the tests/workarounds attachment below for an idea on how the data type changes. How to repeat: /sandboxes/rsandbox_5_0_56$ ./m -T Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.56-enterprise-gpl-log MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. master [localhost] {msandbox} ((none)) > set @var1='0.038687' ; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} ((none)) > select @var1 , if( @var1 = 0 , 1 , @var1 ) as sqlif ; Field 1: `@var1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 8192 Max_length: 8 Decimals: 31 Flags: Field 2: `sqlif` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 8192 Max_length: 8 Decimals: 31 Flags: +----------+----------+ | @var1 | sqlif | +----------+----------+ | 0.038687 | 0.038687 | +----------+----------+ 1 row in set (0.01 sec) -- *** The result above is the expected result. Notice how the IF statement if false and it return @var1. No data type conversion is done on @var1 *** master [localhost] {msandbox} ((none)) > prepare fail_stmt from "select ? , if( ? = 0 , 1 , ? ) as ps_if_fail" ; Query OK, 0 rows affected (0.00 sec) Statement prepared master [localhost] {msandbox} ((none)) > execute fail_stmt using @var1 , @var1 , @var1 ; Field 1: `?` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 8 Max_length: 8 Decimals: 0 Flags: NOT_NULL Field 2: `ps_if_fail` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 8 Max_length: 1 Decimals: 0 Flags: NOT_NULL BINARY +----------+------------+ | ? | ps_if_fail | +----------+------------+ | 0.038687 | 1 | +----------+------------+ 1 row in set (0.00 sec) -- *** The result above is NOT the expected result. Notice how the IF statement if true even though @var1 != 0. Data type conversion *is* done on @var1*** Suggested fix: Not sure exactly what the prepared statement is doing vs the SELECT but I'd expect both to return the same result, evaluating the IF() the same way.