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

Jump to bottom

Faulty plan generated. Query returns more rows than expected or existing in the view sys.tables. #6440

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Faulty plan generated. Query returns more rows than expected or existing in the view sys.tables. #6440

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working major SQL
Milestone
Ancient Release

Comments

@monetdb-team
Copy link

Date: 2017-10-19 17:16:53 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @njnes

Last updated: 2017-10-26 14:01:39 +0200

Comment 25767

Date: 2017-10-19 17:16:53 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:56.0) Gecko/20100101 Firefox/56.0
Build Identifier:

Query:
select * from sys.tables
where id in (select c.table_id from sys.columns c where c."type" = 'clob');
returns many duplicate rows. This is incorrect.
Probably the plan generator is generating a faulty plan. See below.

Note: I found this on the version MonetDB v11.27.8 (unreleased), Jul2017 branch compiled on 19 okt 2017. I haven't checked it against release Jul2017-SP2 or Jul2017-SP1.

Reproducible: Always

Steps to Reproduce:

Run queries:

select /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
from sys.tables
order by schema_id, name, id;
-- lists 60 rows, is ok

select /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
from sys.tables
where schema_id in (select id from sys.schemas where name in ('sys', 'tmp'))
order by schema_id, name, id;
-- lists 55 rows, is ok

select /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
from sys.tables
where id in (select table_id from sys.columns where "type" = 'clob')
order by schema_id, name, id;
-- lists 71 rows, more than exists in sys.tables, is NOT ok, lots of duplicate rows

select /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
from sys.tables
where schema_id in (select id from sys.schemas where name in ('sys', 'tmp'))
and id in (select table_id from sys.columns where "type" = 'clob')
order by schema_id, name, id;
-- lists 44 rows, is NOT ok, lots of duplicate rows

select distinct /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
from sys.tables
where schema_id in (select id from sys.schemas where name in ('sys', 'tmp'))
and id in (select table_id from sys.columns where "type" = 'clob')
order by schema_id, name, id;
-- lists 16 rows, is ok

-- it does not improve when more aliases are used
select /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
from sys.tables t
where t.id in (select c.table_id from sys.columns c where c."type" = 'clob')
and t.schema_id in (select s.id from sys.schemas s where s.name in ('sys', 'tmp'))
order by t.schema_id, t.name, t.id;
-- lists 44 rows, is NOT ok, lots of duplicate rows

plan
select /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
from sys.tables
where id in (select table_id from sys.columns where "type" = 'clob')
order by schema_id, name, id;

Actual Results:

bash-4.4$ mclient -p41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.27.8 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>\q
bash-4.4$ ./start_squirrel.sh
bash-4.4$
bash-4.4$
bash-4.4$ mclient -p41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.27.8 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>select /* id, / name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables
more> order by schema_id, name, id;
+--------------------+-----------+------+--------+---------------+--------+-----------+
| name | schema_id | type | system | commit_action | access | temporary |
+====================+===========+======+========+===============+========+===========+
| _columns | 2000 | 10 | true | 0 | 0 | 0 |
| _tables | 2000 | 10 | true | 0 | 0 | 0 |
| args | 2000 | 10 | true | 0 | 0 | 0 |
| auths | 2000 | 10 | true | 0 | 0 | 0 |
| columns | 2000 | 11 | true | 0 | 0 | 0 |
| db_user_info | 2000 | 10 | true | 0 | 0 | 0 |
| dependencies | 2000 | 10 | true | 0 | 0 | 0 |
| dependency_types | 2000 | 10 | true | 0 | 1 | 0 |
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| function_languages | 2000 | 10 | true | 0 | 1 | 0 |
| function_types | 2000 | 10 | true | 0 | 1 | 0 |
| functions | 2000 | 10 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| idxs | 2000 | 10 | true | 0 | 0 | 0 |
| index_types | 2000 | 10 | true | 0 | 1 | 0 |
| key_types | 2000 | 10 | true | 0 | 1 | 0 |
| keys | 2000 | 10 | true | 0 | 0 | 0 |
| keywords | 2000 | 10 | true | 0 | 1 | 0 |
| netcdf_attrs | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_dims | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_files | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_vardim | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_vars | 2000 | 10 | true | 0 | 0 | 0 |
| objects | 2000 | 10 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| privilege_codes | 2000 | 10 | true | 0 | 1 | 0 |
| privileges | 2000 | 10 | true | 0 | 0 | 0 |
| querylog_calls | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| schemas | 2000 | 10 | true | 0 | 0 | 0 |
| sequences | 2000 | 10 | true | 0 | 0 | 0 |
| sessions | 2000 | 11 | true | 0 | 0 | 0 |
| spatial_ref_sys | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| systemfunctions | 2000 | 10 | true | 0 | 0 | 0 |
| table_types | 2000 | 10 | true | 0 | 1 | 0 |
| tables | 2000 | 11 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tracelog | 2000 | 11 | true | 0 | 0 | 0 |
| triggers | 2000 | 10 | true | 0 | 0 | 0 |
| types | 2000 | 10 | true | 0 | 0 | 0 |
| user_role | 2000 | 10 | true | 0 | 0 | 0 |
| users | 2000 | 11 | true | 0 | 0 | 0 |
| _columns | 2097 | 10 | true | 2 | 0 | 0 |
| _tables | 2097 | 10 | true | 2 | 0 | 0 |
| idxs | 2097 | 10 | true | 2 | 0 | 0 |
| keys | 2097 | 10 | true | 2 | 0 | 0 |
| objects | 2097 | 10 | true | 2 | 0 | 0 |
| triggers | 2097 | 10 | true | 2 | 0 | 0 |
| export | 8043 | 10 | true | 0 | 0 | 0 |
| files | 8043 | 10 | true | 0 | 0 | 0 |
| pg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| sq | 8043 | 10 | true | 0 | 0 | 0 |
+--------------------+-----------+------+--------+---------------+--------+-----------+
60 tuples (5.861ms)
sql>-- lists 60 rows, is ok
sql>
sql>select / id, / name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables
more> where schema_id in (select id from sys.schemas where name in ('sys', 'tmp'))
more> order by schema_id, name, id;
+--------------------+-----------+------+--------+---------------+--------+-----------+
| name | schema_id | type | system | commit_action | access | temporary |
+====================+===========+======+========+===============+========+===========+
| _columns | 2000 | 10 | true | 0 | 0 | 0 |
| _tables | 2000 | 10 | true | 0 | 0 | 0 |
| args | 2000 | 10 | true | 0 | 0 | 0 |
| auths | 2000 | 10 | true | 0 | 0 | 0 |
| columns | 2000 | 11 | true | 0 | 0 | 0 |
| db_user_info | 2000 | 10 | true | 0 | 0 | 0 |
| dependencies | 2000 | 10 | true | 0 | 0 | 0 |
| dependency_types | 2000 | 10 | true | 0 | 1 | 0 |
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| function_languages | 2000 | 10 | true | 0 | 1 | 0 |
| function_types | 2000 | 10 | true | 0 | 1 | 0 |
| functions | 2000 | 10 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| idxs | 2000 | 10 | true | 0 | 0 | 0 |
| index_types | 2000 | 10 | true | 0 | 1 | 0 |
| key_types | 2000 | 10 | true | 0 | 1 | 0 |
| keys | 2000 | 10 | true | 0 | 0 | 0 |
| keywords | 2000 | 10 | true | 0 | 1 | 0 |
| netcdf_attrs | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_dims | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_files | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_vardim | 2000 | 10 | true | 0 | 0 | 0 |
| netcdf_vars | 2000 | 10 | true | 0 | 0 | 0 |
| objects | 2000 | 10 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| privilege_codes | 2000 | 10 | true | 0 | 1 | 0 |
| privileges | 2000 | 10 | true | 0 | 0 | 0 |
| querylog_calls | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| schemas | 2000 | 10 | true | 0 | 0 | 0 |
| sequences | 2000 | 10 | true | 0 | 0 | 0 |
| sessions | 2000 | 11 | true | 0 | 0 | 0 |
| spatial_ref_sys | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| systemfunctions | 2000 | 10 | true | 0 | 0 | 0 |
| table_types | 2000 | 10 | true | 0 | 1 | 0 |
| tables | 2000 | 11 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tracelog | 2000 | 11 | true | 0 | 0 | 0 |
| triggers | 2000 | 10 | true | 0 | 0 | 0 |
| types | 2000 | 10 | true | 0 | 0 | 0 |
| user_role | 2000 | 10 | true | 0 | 0 | 0 |
| users | 2000 | 11 | true | 0 | 0 | 0 |
| _columns | 2097 | 10 | true | 2 | 0 | 0 |
| _tables | 2097 | 10 | true | 2 | 0 | 0 |
| idxs | 2097 | 10 | true | 2 | 0 | 0 |
| keys | 2097 | 10 | true | 2 | 0 | 0 |
| objects | 2097 | 10 | true | 2 | 0 | 0 |
| triggers | 2097 | 10 | true | 2 | 0 | 0 |
+--------------------+-----------+------+--------+---------------+--------+-----------+
55 tuples (9.546ms)
sql>-- lists 55 rows, is ok
sql>
sql>select / id, / name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables
more> where id in (select table_id from sys.columns where "type" = 'clob')
more> order by schema_id, name, id;
+-------------------+-----------+------+--------+---------------+--------+-----------+
| name | schema_id | type | system | commit_action | access | temporary |
+===================+===========+======+========+===============+========+===========+
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| netcdf_attrs | 2000 | 10 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_calls | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| sessions | 2000 | 11 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tracelog | 2000 | 11 | true | 0 | 0 | 0 |
| export | 8043 | 10 | true | 0 | 0 | 0 |
| export | 8043 | 10 | true | 0 | 0 | 0 |
| export | 8043 | 10 | true | 0 | 0 | 0 |
| export | 8043 | 10 | true | 0 | 0 | 0 |
| export | 8043 | 10 | true | 0 | 0 | 0 |
| export | 8043 | 10 | true | 0 | 0 | 0 |
| files | 8043 | 10 | true | 0 | 0 | 0 |
| files | 8043 | 10 | true | 0 | 0 | 0 |
| pg | 8043 | 10 | true | 0 | 0 | 0 |
| pg | 8043 | 10 | true | 0 | 0 | 0 |
| pg | 8043 | 10 | true | 0 | 0 | 0 |
| pg | 8043 | 10 | true | 0 | 0 | 0 |
| pg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| rg | 8043 | 10 | true | 0 | 0 | 0 |
| sq | 8043 | 10 | true | 0 | 0 | 0 |
| sq | 8043 | 10 | true | 0 | 0 | 0 |
| sq | 8043 | 10 | true | 0 | 0 | 0 |
| sq | 8043 | 10 | true | 0 | 0 | 0 |
+-------------------+-----------+------+--------+---------------+--------+-----------+
71 tuples (6.250ms)
sql>-- lists 71 rows, more than exists in sys.tables, is NOT ok, lots of duplicate rows
sql>
sql>select / id, / name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables
more> where schema_id in (select id from sys.schemas where name in ('sys', 'tmp'))
more> and id in (select table_id from sys.columns where "type" = 'clob')
more> order by schema_id, name, id;
+-------------------+-----------+------+--------+---------------+--------+-----------+
| name | schema_id | type | system | commit_action | access | temporary |
+===================+===========+======+========+===============+========+===========+
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| netcdf_attrs | 2000 | 10 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_calls | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| sessions | 2000 | 11 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tracelog | 2000 | 11 | true | 0 | 0 | 0 |
+-------------------+-----------+------+--------+---------------+--------+-----------+
44 tuples (8.211ms)
sql>-- lists 44 rows, is NOT ok, lots of duplicate rows
sql>
sql>select distinct / id, / name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables
more> where schema_id in (select id from sys.schemas where name in ('sys', 'tmp'))
more> and id in (select table_id from sys.columns where "type" = 'clob')
more> order by schema_id, name, id;
+-------------------+-----------+------+--------+---------------+--------+-----------+
| name | schema_id | type | system | commit_action | access | temporary |
+===================+===========+======+========+===============+========+===========+
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| netcdf_attrs | 2000 | 10 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_calls | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| sessions | 2000 | 11 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tracelog | 2000 | 11 | true | 0 | 0 | 0 |
+-------------------+-----------+------+--------+---------------+--------+-----------+
16 tuples (14.232ms)
sql>-- lists 16 rows, is ok
sql>
sql>-- it does not improve when more aliases are used
sql>select / id, / name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables t
more> where t.id in (select c.table_id from sys.columns c where c."type" = 'clob')
more> and t.schema_id in (select s.id from sys.schemas s where s.name in ('sys', 'tmp'))
more> order by t.schema_id, t.name, t.id;
+-------------------+-----------+------+--------+---------------+--------+-----------+
| name | schema_id | type | system | commit_action | access | temporary |
+===================+===========+======+========+===============+========+===========+
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| environment | 2000 | 11 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| geometry_columns | 2000 | 11 | true | 0 | 0 | 0 |
| netcdf_attrs | 2000 | 10 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| optimizers | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_calls | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_catalog | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| querylog_history | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| queue | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| rejects | 2000 | 11 | true | 0 | 0 | 0 |
| sessions | 2000 | 11 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| statistics | 2000 | 10 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storage | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodel | 2000 | 11 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| storagemodelinput | 2000 | 10 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tablestoragemodel | 2000 | 11 | true | 0 | 0 | 0 |
| tracelog | 2000 | 11 | true | 0 | 0 | 0 |
+-------------------+-----------+------+--------+---------------+--------+-----------+
44 tuples (11.785ms)
sql>-- lists 44 rows, is NOT ok, lots of duplicate rows
sql>
sql>plan
more>select / id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables
more> where id in (select table_id from sys.columns where "type" = 'clob')
more> order by schema_id, name, id;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rel |
+==================================================================================================================================================================================================================+
| project ( |
| | project ( |
| | | join ( |
| | | | union ( |
| | | | | project ( |
| | | | | | project ( |
| | | | | | | select ( |
| | | | | | | | table(sys._tables) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", "_tables"."type", "_tables"."system", "_tables"."commit_action", "_tables"."access" ] COUNT |
| | | | | | | ) [ "_tables"."type" != smallint "2" ] |
| | | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", sys.isnull("_tables"."system") as "L101"."L101", sys.isnull(sys.=("_tables"."commit_action", smallint "0")) as "L102". |
: "L102", sys.ifthenelse("L102"."L102", boolean "false", sys.=("_tables"."commit_action", smallint "0")) as "L103"."L103", sys.ifthenelse("L101"."L101", boolean "false", "_tables"."system") as "L104"."L104", sm :
: allint[sys.ifthenelse("L104"."L104", sys.sql_add(int["_tables"."type"], int "10"), sys.ifthenelse("L103"."L103", int["_tables"."type"], sys.sql_add(int["_tables"."type"], int "20")))] as "L7"."type", "_tables :
: "."system", "_tables"."commit_action", "_tables"."access", sys.isnull(sys.and(sys.not("_tables"."system"), sys.>("_tables"."commit_action", smallint "0"))) as "L105"."L105", sys.ifthenelse("L105"."L105", bool :
: ean "false", sys.and(sys.not("_tables"."system"), sys.>("_tables"."commit_action", smallint "0"))) as "L106"."L106", sys.ifthenelse("L106"."L106", tinyint "1", tinyint "0") as "L14"."temporary" ] :
| | | | | ) [ "_tables"."id" as "tables"."id", "_tables"."name" as "tables"."name", "_tables"."schema_id" as "tables"."schema_id", "_tables"."query" as "tables"."query", "L7"."type" as "tables"."type", "_tables |
: "."system" as "tables"."system", "_tables"."commit_action" as "tables"."commit_action", "_tables"."access" as "tables"."access", "L14"."temporary" as "tables"."temporary" ], :
| | | | | project ( |
| | | | | | project ( |
| | | | | | | project ( |
| | | | | | | | table(tmp._tables) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", "_tables"."type", "_tables"."system", "_tables"."commit_action", "_tables"."access" ] COUNT |
| | | | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", smallint[sys.sql_add(int["_tables"."type"], int "30")] as "L23"."type", "_tables"."system", "_tables"."commit_action |
: ", "_tables"."access", tinyint "1" as "L30"."temporary" ] :
| | | | | | ) [ "_tables"."id" as "L32"."id", "_tables"."name" as "L32"."name", "_tables"."schema_id" as "L32"."schema_id", "_tables"."query" as "L32"."query", "L23"."type" as "L32"."type", "_tables"."system" a |
: s "L32"."system", "_tables"."commit_action" as "L32"."commit_action", "_tables"."access" as "L32"."access", tinyint["L30"."temporary"] as "L32"."temporary" ] :
| | | | | ) [ "L32"."id" as "tables"."id", "L32"."name" as "tables"."name", "L32"."schema_id" as "tables"."schema_id", "L32"."query" as "tables"."query", "L32"."type" as "tables"."type", "L32"."system" as "tabl |
: es"."system", "L32"."commit_action" as "tables"."commit_action", "L32"."access" as "tables"."access", "L32"."temporary" as "tables"."temporary" ] :
| | | | ) [ "tables"."id", "tables"."name", "tables"."schema_id", "tables"."query", "tables"."type", "tables"."system", "tables"."commit_action", "tables"."access", "tables"."temporary" ], |
| | | | union ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table(sys._columns) [ "_columns"."type" as "p"."type", "_columns"."table_id" as "p"."table_id" ] COUNT |
| | | | | | ) [ "p"."type" = varchar(1024) "clob" ] |
| | | | | ) [ "p"."table_id" as "L67"."L67" ], |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table(tmp._columns) [ "_columns"."type" as "t"."type", "_columns"."table_id" as "t"."table_id" ] COUNT |
| | | | | | ) [ "t"."type" = varchar(1024) "clob" ] |
| | | | | ) [ "t"."table_id" as "L67"."L67" ] |
| | | | ) [ "L67"."L67" ] |
| | | ) [ "tables"."id" = "L67"."L67" ] |
| | ) [ "tables"."id", "tables"."name", "tables"."schema_id", "tables"."type", "tables"."system", "tables"."commit_action", "tables"."access", "tables"."temporary" ] |
| ) [ "tables"."name", "tables"."schema_id", "tables"."type", "tables"."system", "tables"."commit_action", "tables"."access", "tables"."temporary" ] [ "tables"."schema_id" ASC, "tables"."name" ASC, "tables"."id |
: " ASC ] :
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
34 tuples (2.919ms)
sql>

Expected Results:

Should not lists duplicate rows in the result when adding where-clause condition:
id in (select table_id from sys.columns where "type" = 'clob')
Probably the plan generator is generating a faulty plan.
See output of plan of the query with the faulty result.

Comment 25771

Date: 2017-10-19 23:17:07 +0200
From: Martin van Dinther <<martin.van.dinther>>

Tested it also on Jul2017-SP2 (20171011.msi test built) and the problem does not occur in that version. The plan of Jul2017-SP2 is different (line 3 uses a semijoin instead of a join) when compared to the plan of the 20171029 version.

The plan output on Jul2017-SP2 is:
sql>plan
more>select /* id, */ name, schema_id, /query,/ type, system, commit_action, access, temporary
more> from sys.tables
more> where id in (select table_id from sys.columns where "type" = 'clob')
more> order by schema_id, name, id;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| rel |
+=========================================================================================================================================+
| project ( |
| | project ( |
| | | semijoin ( |
| | | | union ( |
| | | | | project ( |
| | | | | | project ( |
| | | | | | | select ( |
| | | | | | | | table(sys._tables) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", "_tables"."type", "_tabl |
: es"."system", "_tables"."commit_action", "_tables"."access" ] COUNT :
| | | | | | | ) [ "_tables"."type" != smallint "2" ] |
| | | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", sys.isnull("_tables"."system") as "L102"."L10 |
: 2", sys.isnull(sys.=("_tables"."commit_action", smallint "0")) as "L103"."L103", sys.ifthenelse("L103"."L103", boolean "false", sys.=(" :
: _tables"."commit_action", smallint "0")) as "L104"."L104", sys.ifthenelse("L102"."L102", boolean "false", "_tables"."system") as "L105" :
: ."L105", smallint[sys.ifthenelse("L105"."L105", sys.sql_add(int["_tables"."type"], int "10"), sys.ifthenelse("L104"."L104", int["_table :
: s"."type"], sys.sql_add(int["_tables"."type"], int "20")))] as "L7"."type", "_tables"."system", "_tables"."commit_action", "_tables"."a :
: ccess", sys.isnull(sys.and(sys.not("_tables"."system"), sys.>("_tables"."commit_action", smallint "0"))) as "L106"."L106", sys.ifthenel :
: se("L106"."L106", boolean "false", sys.and(sys.not("_tables"."system"), sys.>("_tables"."commit_action", smallint "0"))) as "L107"."L10 :
: 7", sys.ifthenelse("L107"."L107", tinyint "1", tinyint "0") as "L14"."temporary" ] :
| | | | | ) [ "_tables"."id" as "tables"."id", "_tables"."name" as "tables"."name", "_tables"."schema_id" as "tables"."schema_id", "_tabl |
: es"."query" as "tables"."query", "L7"."type" as "tables"."type", "_tables"."system" as "tables"."system", "_tables"."commit_action" as :
: "tables"."commit_action", "_tables"."access" as "tables"."access", "L14"."temporary" as "tables"."temporary" ], :
| | | | | project ( |
| | | | | | project ( |
| | | | | | | project ( |
| | | | | | | | table(tmp._tables) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", "_tables"."type", "_tabl |
: es"."system", "_tables"."commit_action", "_tables"."access" ] COUNT :
| | | | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", smallint[sys.sql_add(int["_tables"."type"], |
: int "30")] as "L23"."type", "_tables"."system", "_tables"."commit_action", "_tables"."access", tinyint "1" as "L30"."temporary" ] :
| | | | | | ) [ "_tables"."id" as "L32"."id", "_tables"."name" as "L32"."name", "_tables"."schema_id" as "L32"."schema_id", "_tables"."qu |
: ery" as "L32"."query", "L23"."type" as "L32"."type", "_tables"."system" as "L32"."system", "_tables"."commit_action" as "L32"."commit_a :
: ction", "tables"."access" as "L32"."access", tinyint["L30"."temporary"] as "L32"."temporary" ] :
| | | | | ) [ "L32"."id" as "tables"."id", "L32"."name" as "tables"."name", "L32"."schema_id" as "tables"."schema_id", "L32"."query" as " |
: tables"."query", "L32"."type" as "tables"."type", "L32"."system" as "tables"."system", "L32"."commit_action" as "tables"."commit_action :
: ", "L32"."access" as "tables"."access", "L32"."temporary" as "tables"."temporary" ] :
| | | | ) [ "tables"."id", "tables"."name", "tables"."schema_id", "tables"."query", "tables"."type", "tables"."system", "tables"."commit |
: action", "tables"."access", "tables"."temporary" ], :
| | | | union ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table(sys._columns) [ "_columns"."type" as "p"."type", "_columns"."table_id" as "p"."table_id" ] COUNT |
| | | | | | ) [ "p"."type" = varchar(1024) "clob" ] |
| | | | | ) [ "p"."table_id" as "L67"."L66" ], |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table(tmp._columns) [ "_columns"."type" as "t"."type", "_columns"."table_id" as "t"."table_id" ] COUNT |
| | | | | | ) [ "t"."type" = varchar(1024) "clob" ] |
| | | | | ) [ "t"."table_id" as "L67"."L66" ] |
| | | | ) [ "L67"."L66" ] |
| | | ) [ "tables"."id" = "L67"."L66" ] |
| | ) [ "tables"."id", "tables"."name", "tables"."schema_id", "tables"."type", "tables"."system", "tables"."commit_action", "tables"."acc |
: ess", "tables"."temporary" ] :
| ) [ "tables"."name", "tables"."schema_id", "tables"."type", "tables"."system", "tables"."commit_action", "tables"."access", "tables"."t |
: emporary" ] [ "tables"."schema_id" ASC, "tables"."name" ASC, "tables"."id" ASC ] :
+-----------------------------------------------------------------------------------------------------------------------------------------+
34 tuples (5.598ms)
sql>

Comment 25775

Date: 2017-10-21 11:32:03 +0200
From: MonetDB Mercurial Repository <>

Changeset 901a54cbadd9 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=901a54cbadd9

Changeset description:

Fixes for bug #6440 (ie properly use semi/anti join for in)

Implement drop schema schema_name restrict (see bug #6438)
We keep the current default (cascade) in the release branch and switch
to the SQL correct default in the default branch.

Comment 25778

Date: 2017-10-21 13:10:41 +0200
From: @njnes

fixed
@monetdb-team monetdb-team added bug Something isn't working major SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working major SQL
Projects
None yet
Milestone
Ancient Release
Development

No branches or pull requests
2 participants
@sjoerdmullender @monetdb-team