[MDEV-36295] INTERVAL function returns inconsistent results when used in a WHERE clause - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 11.8.0, 10.5, 10.6, 10.11, 11.4, 11.8
Fix Version/s: 10.11, 11.4, 11.8
Component/s: Optimizer, Server
Labels:
- wrong_result
Environment:
git rev-parse HEAD
11a6c1b30a12c448ddfe05e1b818a6a228e90e43

Description

Description: The behavior of the INTERVAL function is inconsistent when used in a SELECT query. The first query, which uses the INTERVAL function and selects columns, returns correct results. However, when the same INTERVAL function is used in the WHERE clause to filter the results, the query returns an empty set. These results should be consistent, but they are not.
Steps to Reproduce:

DROP TABLE IF EXISTS `t0`;

CREATE TABLE `t0` (

  `c1` double,

  `c2` double

);

INSERT INTO `t0` VALUES (23,-16);

query1:

select distinct

  subq_0.c_0 as c_0,

  subq_0.c_1 as c_1,

  subq_0.c_2 as c_2,

  INTERVAL(subq_0.c_0,

            subq_0.c_1,

            subq_0.c_2) as c_7,

   (INTERVAL(subq_0.c_0,

            subq_0.c_1,

            subq_0.c_2)

             > 0) as w_1

from

  (select distinct

        ref_0.c1 as c_0,

        ref_0.c2 as c_1,

        (ref_0.c2) / (ref_0.c1) as c_2

      from

        t0 as ref_0

      ) as subq_0

where  (INTERVAL(subq_0.c_0,

            subq_0.c_1,

            subq_0.c_2))

output:

+------+------+---------------------+-----+-----+

| c_0  | c_1  | c_2                 | c_7 | w_1 |

+------+------+---------------------+-----+-----+

|   23 |  -16 | -0.6956521739130435 |   2 |   1 |

+------+------+---------------------+-----+-----+

1 row in set (0.00 sec)

query2:

select distinct

  subq_0.c_0 as c_0,

  subq_0.c_1 as c_1,

  subq_0.c_2 as c_2,

  INTERVAL(subq_0.c_0,

            subq_0.c_1,

            subq_0.c_2) as c_7,

   (INTERVAL(subq_0.c_0,

            subq_0.c_1,

            subq_0.c_2)

             > 0) as w_1

from

  (select distinct

        ref_0.c1 as c_0,

        ref_0.c2 as c_1,

        (ref_0.c2) / (ref_0.c1) as c_2

      from

        t0 as ref_0

      ) as subq_0

where  (INTERVAL(subq_0.c_0,

            subq_0.c_1,

            subq_0.c_2)>0)

output:

Empty set (0.00 sec)

Expected Behavior:
Both queries should return the same result
Actual Behavior:
but the second query returns an empty set, while the first query returns the expected row.

Attachments

Activity

Alice Sherepa added a comment - 2025-03-17 17:06

Thanks! I repeated as described on 10.5-11.8, Myism/InnoDB. Without DISTINCT clause - returnes results:

MariaDB [test]> CREATE TABLE t0 ( c1 double, c2 double);

Query OK, 0 rows affected (0,034 sec)

MariaDB [test]> INSERT INTO t0 VALUES (23, -16);

Query OK, 1 row affected (0,006 sec)

MariaDB [test]> SELECT  1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0  ) dt WHERE interval(dt.c1, dt.c2, dt.c) ;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0,002 sec)

MariaDB [test]> SELECT  1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0  ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ;

Empty set (0,001 sec)

MariaDB [test]> explain extended SELECT  1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0  ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ;

+------+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |

+------+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 2    |   100.00 | Using where                  |

|    2 | DERIVED     | t0         | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using where; Using temporary |

+------+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

2 rows in set, 1 warning (0,003 sec)

Note (Code 1003): /* select#1 */ select 1 AS `1` from (/* select#2 */ select distinct `test`.`t0`.`c1` AS `c1`,`test`.`t0`.`c2` AS `c2`,`test`.`t0`.`c2` / `test`.`t0`.`c1` AS `c` from `test`.`t0` where interval(`test`.`t0`.`c1`,`test`.`t0`.`c2`,`test`.`t0`.`c2` / `test`.`t0`.`c1`) > 0) `dt` where interval(`dt`.`c1`,`dt`.`c2`,`dt`.`c`) > 0

MariaDB [test]> SELECT  1 FROM (SELECT c1, c2, c2 / c1 AS c FROM t0  ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0,003 sec)

MariaDB [test]> explain extended SELECT  1 FROM (SELECT c1, c2, c2 / c1 AS c FROM t0  ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ;

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

|    1 | SIMPLE      | t0    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using where |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0,003 sec)

Note (Code 1003): select 1 AS `1` from `test`.`t0` where interval(`test`.`t0`.`c1`,`test`.`t0`.`c2`,`test`.`t0`.`c2` / `test`.`t0`.`c1`) > 0

MariaDB [test]> select interval(dt.c1, dt.c2, dt.c) FROM (SELECT c1, c2, c2 / c1 AS c FROM t0  ) dt;

+------------------------------+

| interval(dt.c1, dt.c2, dt.c) |

+------------------------------+

|                            2 |

+------------------------------+

1 row in set (0,003 sec)

MariaDB [test]> DROP TABLE IF EXISTS t0;

Query OK, 0 rows affected (0,033 sec)

MariaDB [test]> CREATE TABLE t0 ( c1 double, c2 double);

Query OK, 0 rows affected (0,032 sec)

MariaDB [test]> INSERT INTO t0 VALUES (23, -16);

Query OK, 1 row affected (0,006 sec)

MariaDB [test]> SELECT  1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0  ) dt WHERE interval(dt.c1, dt.c2, dt.c) ;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0,001 sec)

MariaDB [test]> SELECT  1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0  ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ;

Empty set (0,001 sec)

Alice Sherepa added a comment - 2025-03-17 17:06 Thanks! I repeated as described on 10.5-11.8, Myism/InnoDB. Without DISTINCT clause - returnes results: MariaDB [test]> CREATE TABLE t0 ( c1 double, c2 double); Query OK, 0 rows affected (0,034 sec) MariaDB [test]> INSERT INTO t0 VALUES (23, -16); Query OK, 1 row affected (0,006 sec) MariaDB [test]> SELECT 1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0 ) dt WHERE interval(dt.c1, dt.c2, dt.c) ; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0,002 sec) MariaDB [test]> SELECT 1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0 ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ; Empty set (0,001 sec) MariaDB [test]> explain extended SELECT 1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0 ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ; +------+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DERIVED | t0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 2 rows in set, 1 warning (0,003 sec) Note (Code 1003): /* select#1 */ select 1 AS `1` from (/* select#2 */ select distinct `test`.`t0`.`c1` AS `c1`,`test`.`t0`.`c2` AS `c2`,`test`.`t0`.`c2` / `test`.`t0`.`c1` AS `c` from `test`.`t0` where interval(`test`.`t0`.`c1`,`test`.`t0`.`c2`,`test`.`t0`.`c2` / `test`.`t0`.`c1`) > 0) `dt` where interval(`dt`.`c1`,`dt`.`c2`,`dt`.`c`) > 0 MariaDB [test]> SELECT 1 FROM (SELECT c1, c2, c2 / c1 AS c FROM t0 ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0,003 sec) MariaDB [test]> explain extended SELECT 1 FROM (SELECT c1, c2, c2 / c1 AS c FROM t0 ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0,003 sec) Note (Code 1003): select 1 AS `1` from `test`.`t0` where interval(`test`.`t0`.`c1`,`test`.`t0`.`c2`,`test`.`t0`.`c2` / `test`.`t0`.`c1`) > 0 MariaDB [test]> select interval(dt.c1, dt.c2, dt.c) FROM (SELECT c1, c2, c2 / c1 AS c FROM t0 ) dt; +------------------------------+ | interval(dt.c1, dt.c2, dt.c) | +------------------------------+ | 2 | +------------------------------+ 1 row in set (0,003 sec) MariaDB [test]> DROP TABLE IF EXISTS t0; Query OK, 0 rows affected (0,033 sec) MariaDB [test]> CREATE TABLE t0 ( c1 double, c2 double); Query OK, 0 rows affected (0,032 sec) MariaDB [test]> INSERT INTO t0 VALUES (23, -16); Query OK, 1 row affected (0,006 sec) MariaDB [test]> SELECT 1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0 ) dt WHERE interval(dt.c1, dt.c2, dt.c) ; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0,001 sec) MariaDB [test]> SELECT 1 FROM (SELECT DISTINCT c1, c2, c2 / c1 AS c FROM t0 ) dt WHERE interval(dt.c1, dt.c2, dt.c)>0 ; Empty set (0,001 sec)

People

Assignee:: Sergei Petrunia

Reporter:: chengzhiqiang

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2025-03-14 06:58

Updated:: 2025-03-17 17:07

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server