Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

EXISTS fails in case its subquery returns several values #8676

Closed
EmirVildanov opened this issue May 20, 2023 · 0 comments · Fixed by #8847
Closed

EXISTS fails in case its subquery returns several values #8676

EmirVildanov opened this issue May 20, 2023 · 0 comments · Fixed by #8847
Assignees
Labels
bug Something isn't working sql

Comments

@EmirVildanov
Copy link

Bug description

Executing SELECT with EXISTS condition operator fails in case subquery returns several values.

  • OS: Linux
  • OS Version: Ubuntu 20.04.5 LTS
  • Architecture: amd64

Tarantool version: 2.10.5-18-g080d07d71

Steps to reproduce

  1. Run tarantool instance
  2. Execute box.cfg{}
  3. Execute box.execute([[create table t (id integer primary key);]])
  4. Execute box.execute([[select * from t where exists (select * from (values (1), (2)));]]) <- on empty table t it runs successfully, returning no rows
  5. Execute box.execute([[insert into t values (1);]])
  6. Execute box.execute([[select * from t where exists (select * from (values (1), (2)));]]) <- on non-empty table t it fails with error 'Failed to execute SQL statement: Expression subquery returned more than 1 row'

Actual behavior

SELECT from non-empty table with additional EXISTS (which subquery returns two values) condition fails.

Expected behavior

SELECT from non-empty table with additional EXISTS (which subquery returns two values) condition should return all the rows from the table (the result should be the same as if subquery was returning just one value: executing query with values (1) instead of values (1), (2) produce no errors).

@EmirVildanov EmirVildanov added the bug Something isn't working label May 20, 2023
@Gumix Gumix added the sql label May 22, 2023
ImeevMA added a commit to ImeevMA/tarantool that referenced this issue Jul 5, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
before this patch, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes tarantool#8676

@TarantoolBot document
Title: EXISTS predicate

The EXISTS predicate in SQL is a predicate that returns true if the
given SQL subquery returns at least one row, and false otherwise.

Example:
```
SELECT EXISTS(SELECT * FROM (VALUES(1), (2)));
```
ImeevMA added a commit to ImeevMA/tarantool that referenced this issue Jul 5, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
before this patch, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes tarantool#8676

@TarantoolBot document
Title: EXISTS predicate

The EXISTS predicate in SQL is a predicate that returns true if the
given SQL subquery returns at least one row, and false otherwise.

Example:
```
SELECT EXISTS(SELECT * FROM (VALUES(1), (2)));
```
ImeevMA added a commit to ImeevMA/tarantool that referenced this issue Nov 10, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes tarantool#8676

NO_DOC=bugfix
ImeevMA added a commit to ImeevMA/tarantool that referenced this issue Nov 10, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes tarantool#8676

NO_DOC=bugfix
ImeevMA added a commit to ImeevMA/tarantool that referenced this issue Nov 10, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes tarantool#8676

NO_DOC=bugfix
ImeevMA added a commit to ImeevMA/tarantool that referenced this issue Nov 15, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes tarantool#8676

NO_DOC=bugfix
igormunkin pushed a commit that referenced this issue Nov 27, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes #8676

NO_DOC=bugfix
igormunkin pushed a commit that referenced this issue Nov 27, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes #8676

NO_DOC=bugfix

(cherry picked from commit a5e498d)
ImeevMA added a commit to ImeevMA/tarantool that referenced this issue Nov 28, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes tarantool#8676

NO_DOC=bugfix

(cherry picked from commit a5e498d)
igormunkin pushed a commit that referenced this issue Nov 28, 2023
According to ANSI, EXISTS is a predicate that tests a given subquery and
returns true if it returns more than 0 rows, false otherwise. However,
after 2a720d1, EXISTS worked correctly only if there were exactly 0
or 1 rows, and in all other cases it gave an error. This patch makes
EXITS work properly.

Closes #8676

NO_DOC=bugfix

(cherry picked from commit a5e498d)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working sql
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants