[#MDEV-29373] sql_mode=ORACLE: ROWNUM is accepted in subquery, but ignored in subquery in WHERE clause

[MDEV-29373] sql_mode=ORACLE: ROWNUM is accepted in subquery, but ignored in subquery in WHERE clause Created: 2022-08-24 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.6.9
Fix Version/s:	10.6

Type:

Bug

Priority:

Major

Reporter:

Alexander Keremidarski

Assignee:

Michael Widenius

Resolution:

Unresolved

Votes:

Labels:

None

Description

CREATE TABLE t1 (a INT);

CREATE TABLE t2 (b INT);

INSERT INTO t1 VALUES(1),(2),(3);

INSERT INTO t2 VALUES(1),(2),(3);

SET SQL_MODE=ORACLE;

SELECT a FROM t1 WHERE a IN (SELECT b FROM t2 WHERE ROWNUM < 3);

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.000 sec)

The result is wrong because:

SELECT b FROM t2 WHERE ROWNUM < 3;

+------+

| b    |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.001 sec)

It works properly with derived table both with intended cartesian product and with ON clause:

SELECT a, b FROM t1 JOIN (SELECT b FROM t2 WHERE ROWNUM < 3);

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

| a    | b    |

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

|    1 |    1 |

|    1 |    2 |

|    2 |    1 |

|    2 |    2 |

|    3 |    1 |

|    3 |    2 |

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

6 rows in set (0.001 sec)

SELECT a, b FROM t1 JOIN (SELECT b FROM t2 WHERE ROWNUM < 3) ON a = b;

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

| a    | b    |

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

|    1 |    1 |

|    2 |    2 |

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

2 rows in set (0.001 sec)

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