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;
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.
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.
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:
Comment 25778
Date: 2017-10-21 13:10:41 +0200
From: @njnes
fixed
The text was updated successfully, but these errors were encountered: