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

running analyze on a schema which contains a stream table stops with an error #6817

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

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2020-02-13 13:26:58 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.35.9 (Nov2019-SP1)
CC: @PedroTadim

Last updated: 2020-02-21 12:36:58 +0100

Comment 27540

Date: 2020-02-13 13:26:58 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

It is possible to run analyze on all tables existing in a schema or a specific table or even column.
However when a schema contains a stream table, it produces an error for the stream table (Table 'strt' is not persistent SQLState: 42S02) and stops analysing any other table in the schema.
This makes analyzing all tables in a schema, not possible anymore.
If a stream table is not persisted, analyze should skip it (like done with views).

Reproducible: Always

Steps to Reproduce:

delete from statistics;
select count(*) from statistics;

CREATE STREAM TABLE "sys"."strt" (
"id" INTEGER NOT NULL,
"nm" VARCHAR(123) NOT NULL,
CONSTRAINT "strt_id_pkey" PRIMARY KEY ("id")
);

select * from "sys"."strt";

analyze "sys"."strt";
-- Error: Table 'strt' is not persistent SQLState: 42S02
select (count(*) > 0) as has_rows from statistics;

analyze sys;
-- Error: Table 'strt' is not persistent SQLState: 42S02
select (count(*) > 0) as has_rows from statistics;

drop table "sys"."strt";
-- now run analyze without the stream table
analyze sys;
select (count(*) > 0) as has_rows from statistics;
-- true (181)
delete from statistics;

Actual Results:

sql>delete from statistics;
0 affected rows
sql>select count() from statistics;
+------+
| L2 |
+======+
| 0 |
+------+
1 tuple
sql>
sql>CREATE STREAM TABLE "sys"."strt" (
more> "id" INTEGER NOT NULL,
more> "nm" VARCHAR(123) NOT NULL,
more> CONSTRAINT "strt_id_pkey" PRIMARY KEY ("id")
more>);
operation successful
sql>
sql>select * from "sys"."strt";
+----+----+
| id | nm |
+====+====+
+----+----+
0 tuples
sql>
sql>analyze "sys"."strt";
Table 'strt' is not persistent
sql>-- Error: Table 'strt' is not persistent SQLState: 42S02
sql>select (count(
) > 0) as has_rows from statistics;
+----------+
| has_rows |
+==========+
| false |
+----------+
1 tuple
sql>
sql>analyze sys;
Table 'strt' is not persistent
sql>-- Error: Table 'strt' is not persistent SQLState: 42S02
sql>select (count() > 0) as has_rows from statistics;
+----------+
| has_rows |
+==========+
| false |
+----------+
1 tuple
sql>
sql>drop table "sys"."strt";
operation successful
sql>-- now run analyze without the stream table
sql>analyze sys;
sql>select (count(
) > 0) as has_rows from statistics;
+----------+
| has_rows |
+==========+
| true |
+----------+
1 tuple
sql>-- true (181)
sql>delete from statistics;
181 affected rows
sql>

Expected Results:

sql>delete from statistics;
0 affected rows
sql>select count() from statistics;
+------+
| L2 |
+======+
| 0 |
+------+
1 tuple
sql>
sql>CREATE STREAM TABLE "sys"."strt" (
more> "id" INTEGER NOT NULL,
more> "nm" VARCHAR(123) NOT NULL,
more> CONSTRAINT "strt_id_pkey" PRIMARY KEY ("id")
more>);
operation successful
sql>
sql>select * from "sys"."strt";
+----+----+
| id | nm |
+====+====+
+----+----+
0 tuples
sql>
sql>analyze "sys"."strt";
sql>select (count(
) > 0) as has_rows from statistics;
+----------+
| has_rows |
+==========+
| false |
+----------+
1 tuple
sql>
sql>analyze sys;
sql>select (count() > 0) as has_rows from statistics;
+----------+
| has_rows |
+==========+
| true |
+----------+
1 tuple
sql>
sql>drop table "sys"."strt";
operation successful
sql>-- now run analyze without the stream table
sql>analyze sys;
sql>select (count(
) > 0) as has_rows from statistics;
+----------+
| has_rows |
+==========+
| true |
+----------+
1 tuple
sql>-- true (181)
sql>delete from statistics;
181 affected rows
sql>

Comment 27541

Date: 2020-02-13 13:59:26 +0100
From: MonetDB Mercurial Repository <>

Changeset 7832d33851e5 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

add test for bug #6817

Comment 27543

Date: 2020-02-13 14:24:37 +0100
From: MonetDB Mercurial Repository <>

Changeset dda55ac48ee9 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Fix for bug #6817
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant