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

Error in optimizer garbageCollector on merge tables select #6756

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

Error in optimizer garbageCollector on merge tables select #6756

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

Comments

@monetdb-team
Copy link

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

Date: 2019-09-09 12:18:57 +0200
From: jpastuszek
To: SQL devs <>
Version: 11.33.11 (Apr2019-SP1)
CC: @PedroTadim

Last updated: 2019-11-28 10:00:02 +0100

Comment 27270

Date: 2019-09-09 12:18:57 +0200
From: jpastuszek

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:70.0) Gecko/20100101 Firefox/70.0
Build Identifier:

With two tables merged and selecting more than 4 columns with LIMIT expression I get "Error in optimizer garbageCollector".
Note that this is a regression in SP1 as this query was working fine in Apr2019.

Reproducible: Always

Steps to Reproduce:

  1. Create tables like this:

CREATE SCHEMA logs;

CREATE MERGE TABLE logs.test_message (
logentry_no BIGINT,
logentry_id STRING,
processed_timestamp TIMESTAMP,
timestamp TIMESTAMP,
logsource STRING,
logsource_environment STRING,
logsource_service STRING,
logsource_location STRING,
logsource_subsystem STRING,
program STRING,
program_type STRING,
program_name STRING,
program_log STRING,
program_source STRING,
program_thread STRING,
log_level STRING,
tags JSON,
syslog_severity STRING,
syslog_facility STRING,
syslog_tag STRING,
message STRING,
structured_data JSON
)

CREATE TABLE logs.test_message_20190909 (
logentry_no BIGINT GENERATED ALWAYS AS
IDENTITY (
START WITH 2019090900000000000 INCREMENT BY 1
MINVALUE 2019090900000000000 MAXVALUE 2019090999999999999
CACHE 50 CYCLE
),
logentry_id STRING,
processed_timestamp TIMESTAMP,
timestamp TIMESTAMP,
logsource STRING,
logsource_environment STRING,
logsource_service STRING,
logsource_location STRING,
logsource_subsystem STRING,
program STRING,
program_type STRING,
program_name STRING,
program_log STRING,
program_source STRING,
program_thread STRING,
log_level STRING,
tags JSON,
syslog_severity STRING,
syslog_facility STRING,
syslog_tag STRING,
message STRING,
structured_data JSON
)

CREATE TABLE logs.test_message_20190910 (
logentry_no BIGINT GENERATED ALWAYS AS
IDENTITY (
START WITH 2019090900000000000 INCREMENT BY 1
MINVALUE 2019090900000000000 MAXVALUE 2019090999999999999
CACHE 50 CYCLE
),
logentry_id STRING,
processed_timestamp TIMESTAMP,
timestamp TIMESTAMP,
logsource STRING,
logsource_environment STRING,
logsource_service STRING,
logsource_location STRING,
logsource_subsystem STRING,
program STRING,
program_type STRING,
program_name STRING,
program_log STRING,
program_source STRING,
program_thread STRING,
log_level STRING,
tags JSON,
syslog_severity STRING,
syslog_facility STRING,
syslog_tag STRING,
message STRING,
structured_data JSON
)

ALTER TABLE logs.test_message ADD TABLE logs.test_message_20190909;
ALTER TABLE logs.test_message ADD TABLE logs.test_message_20190910;

  1. Run this query

SELECT timestamp AS timestamp , logentry_no AS logentry_no, logsource AS logsource, program AS program
FROM logs.test_message
WHERE processed_timestamp >= '2019-09-09 01:23:58.949' AND processed_timestamp <= '2019-09-09 17:38:58.949'
ORDER BY timestamp
LIMIT 2000

  1. Run this query

SELECT timestamp AS timestamp , logentry_no AS logentry_no, logsource AS logsource --, program AS program
FROM logs.test_message
WHERE processed_timestamp >= '2019-09-09 01:23:58.949' AND processed_timestamp <= '2019-09-09 17:38:58.949'
ORDER BY timestamp
LIMIT 2000

Actual Results:

First query fails with "SQL Error [42000]: Error in optimizer garbageCollector".

Expected Results:

Both queries to give empty result set.

I have two systems running Apr2019-SP1 and both fail this query.
Similar query to this but with 'AND NOT' WHERE condition works.
Adding 'AND NOT program_type = 'foo'' also makes the issue to go away.

Comment 27271

Date: 2019-09-09 15:44:48 +0200
From: MonetDB Mercurial Repository <>

Changeset 6c30e2e3c8e5 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=6c30e2e3c8e5

Changeset description:

Added test for Bug #6756

Comment 27272

Date: 2019-09-09 17:20:48 +0200
From: MonetDB Mercurial Repository <>

Changeset 0c293f27828b 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=0c293f27828b

Changeset description:

Fix for bug #6756, i.e. test for existence of an ordering column in the projection list, adding only if it does not exist yet

Comment 27273

Date: 2019-09-10 11:53:19 +0200
From: jpastuszek

I have patched my MonetDB servers and looks like the issue is resolved.
Thank you for your prompt response!

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