[MDEV-21639] DEFAULT(col) evaluates to a bad value in WHERE clause Created: 2020-02-03  Updated: 2021-12-29  Resolved: 2021-12-29

Status: Closed
Project: MariaDB Server
Component/s: Server, Virtual Columns
Affects Version/s: 10.2
Fix Version/s: 10.2.42

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-21637 Assertion `!is_valid_datetime() || fr... Closed
Relates
relates to MDEV-24958 Server crashes in my_strtod / Value_s... Closed

 Description   

I run this script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a BIGINT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
 
INSERT INTO t1 VALUES (10000);
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END = 10 AS ce
 
FROM t1;

It returns the following correct result:

+-------+------------+------+------+
 
| a     | DEFAULT(a) | c    | ce   |
 
+-------+------------+------+------+
 
| 10000 |         10 |   10 |    1 |
 
+-------+------------+------+------+

Looks good so far. The value of the CASE expression (result column c) is correctly evaluates to 10, and the comparison expression (result column ce) correctly evaluates to true.

Now I put the comparison expression into the WHERE clause:

SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;


Empty set (0.001 sec)

Looks wrong.

Now I put a breakpoint in Field_longlong::val_int() and rerun the last SQL query with the WHERE condition.
val_int() is called two times:

  • I skip the first call which is evaluation of WHEN a
  • I print the following values during the second call, which is evaluation of THEN DEFAULT(a):

(gdb) print ptr
 
$6 = (uchar *) 0x62b000001a99 "\245\245\245\245\245\245\245\245", '\217' <repeats 15 times>
 
(gdb) fin
 
Run till exit from #0  Field_longlong::val_int (this=0x62b0000019c8)
 
    at /home/bar/maria-git/server.10.2.asan/sql/field.cc:4514
 
Item_field::val_int_result (this=0x62b000000d88)
 
    at /home/bar/maria-git/server.10.2.asan/sql/item.cc:2898
 
2898	  return result_field->val_int();
 
Value returned is $7 = -6510615555426900571

Notice:

  • Field_longlong::ptr points to a non-initialized buffer
  • val_int() returns a non-relevant value instead of 10

 Comments   
Comment by Aleksey Midenkov [ 2021-10-01 ]

Not reproducible in 10.2 (also tried some old version until 2020-02-03), 10.3.

10.3 debugging shows that val_int() returns 10.

Comment by Alexander Barkov [ 2021-12-29 ]

Still repeatable in the current 10.2 as of fad1d15326651a92895c799829ff66edc37fc20f

Also repeatable with the following scripts testing other data types - they all return empty sets.

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
 
INSERT INTO t1 VALUES (10000);
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END = 10 AS ce
 
FROM t1;
 
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DECIMAL(10,0) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
 
INSERT INTO t1 VALUES (10000);
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END = 10 AS ce
 
FROM t1;
 
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(32) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
 
INSERT INTO t1 VALUES (10000);
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END = '10' AS ce
 
FROM t1;
 
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10';


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),DATE'2001-01-01')));
 
INSERT INTO t1 VALUES ('2000-01-01');
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END = '2001-01-01' AS ce
 
FROM t1;
 
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01';


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a TIME NOT NULL DEFAULT (IF(false,TIME(UNIX_TIMESTAMP()%10),TIME'10:20:30')));
 
INSERT INTO t1 VALUES ('10:00:00');
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END = '10:20:30' AS ce
 
FROM t1;
 
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30';


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),TIMESTAMP'2001-01-01 10:20:30')));
 
INSERT INTO t1 VALUES ('2000-01-01 10:00:00');
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END = '2001-01-01 10:20:30' AS ce
 
FROM t1;
 
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30';


DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a INT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),FALSE)));
 
INSERT INTO t1 VALUES (10);
 
SELECT
 
  a,
 
  DEFAULT(a),
 
  CASE WHEN a THEN DEFAULT(a) END AS c,
 
  CASE WHEN a THEN DEFAULT(a) END IS FALSE AS ce
 
FROM t1;
 
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE;

Generated at Thu Feb 08 09:08:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.