[MDEV-21076] NOT NULL and UNIQUE constraints cause SUM() to yield an incorrect result Created: 2019-11-18  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 5.5, 10.1, 10.4.11, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Manuel Rigger Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 19.04

Issue Links:
Relates

 Description   

Consider the following statements:

CREATE TABLE t0(c0 INT NOT NULL, c1 CHAR UNIQUE);
 
INSERT INTO t0 VALUES (0, 1);
 
INSERT INTO t0 VALUES (0, '');
 
SELECT SUM(a.t) FROM (SELECT (c1 RLIKE c1) = (c0 IS NULL) as t FROM t0) as a; -- expected: 1, actual: 0

I would expect SUM() to yield 1, because the expression evaluates to TRUE for one row:

SELECT (c1 RLIKE c1) = (c0 IS NULL) FROM t0; -- 1, 0



 Comments   
Comment by Elena Stepanova [ 2019-11-25 ]

It seems more likely the expression evaluates to TRUE incorrectly.

If we run (c1 RLIKE c1) separately, it returns TRUE to both, which seems quite natural:

MariaDB [test]> SELECT (c1 RLIKE c1) FROM t0;
 
+---------------+
 
| (c1 RLIKE c1) |
 
+---------------+
 
|             1 |
 
|             1 |
 
+---------------+
 
2 rows in set (0.001 sec)

And naturally c0 IS NULL returns FALSE for both rows:

MariaDB [test]> SELECT (c0 IS NULL) FROM t0;
 
+--------------+
 
| (c0 IS NULL) |
 
+--------------+
 
|            0 |
 
|            0 |
 
+--------------+
 
2 rows in set (0.000 sec)

But when they are run together, strange things happen:

MariaDB [test]> SELECT (c1 RLIKE c1), (c0 IS NULL) FROM t0;
 
+---------------+--------------+
 
| (c1 RLIKE c1) | (c0 IS NULL) |
 
+---------------+--------------+
 
|             1 |            0 |
 
|             0 |            0 |
 
+---------------+--------------+
 
2 rows in set (0.000 sec)

Reproducible on 5.5-10.5. MySQL behaves differently comparing to MariaDB and between its own versions.

Comment by Alexander Barkov [ 2019-11-26 ]

The problem is also demonstrated in this script:

CREATE OR REPLACE TABLE t1(c0 INT NOT NULL, c1 CHAR UNIQUE);
 
INSERT INTO t1 VALUES (0, 1);
 
INSERT INTO t1 VALUES (0, '');

This query returns a correct result:

SELECT c1, (c1 RLIKE c1) FROM t1;


+------+---------------+
 
| c1   | (c1 RLIKE c1) |
 
+------+---------------+
 
|      |             1 |
 
| 1    |             1 |
 
+------+---------------+

Now if a add an extra column, the expression (c1 RLIKE c1) starts to return a wrong result for one row:

SELECT c1, (c1 RLIKE c1), (c0 is null) FROM t1;


+------+---------------+--------------+
 
| c1   | (c1 RLIKE c1) | (c0 is null) |
 
+------+---------------+--------------+
 
| 1    |             1 |            0 |
 
|      |             0 |            0 |
 
+------+---------------+--------------+

If I now add the ORDER BY clause:

SELECT c1, (c1 RLIKE c1), (c0 is null) FROM t1 ORDER BY c1;

it returns correct results again:

+------+---------------+--------------+
 
| c1   | (c1 RLIKE c1) | (c0 is null) |
 
+------+---------------+--------------+
 
|      |             1 |            0 |
 
| 1    |             1 |            0 |
 
+------+---------------+--------------+

The problem seem to reside in this code:

  if (is_compiled())
 
  {
 
    if (!stringcmp(pattern, &m_prev_pattern))
 
      return false;
 
    cleanup();
 
    m_prev_pattern.copy(*pattern);
 
  }

It erroneously performs return false instead of recompiling.

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