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

algebra.join undefined when using multiple arithmetic operations in SQL where-clause #3048

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

Comments

@monetdb-team
Copy link

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

Date: 2012-03-06 16:26:20 +0100
From: Freddy <<freddy_priyatna>>
To: SQL devs <>
Version: 11.13.3 (Oct2012)
CC: @njnes, @drstmane

Last updated: 2013-01-22 09:29:06 +0100

Comment 17041

Date: 2012-03-06 16:26:20 +0100
From: Freddy <<freddy_priyatna>>

User-Agent: Mozilla/5.0 (X11; Linux i686) AppleWebKit/535.11 (KHTML, like Gecko) Ubuntu/11.10 Chromium/17.0.963.56 Chrome/17.0.963.56 Safari/535.11
Build Identifier: MonetDB 5 server v11.7.5 "Dec2011" (32-bit, 32-bit oids)

It seems that MonetDB can't process both of the 2 last lines of the query. But if I comment either one of the last 2 lines, it's ok.

Reproducible: Always

Steps to Reproduce:

SELECT distinct p.nr, p.label
FROM product p, product po,
(Select distinct pfp1.product FROM productfeatureproduct pfp1,
(SELECT "productFeature" FROM productfeatureproduct WHERE product=2) pfp2
WHERE pfp2."productFeature"=pfp1."productFeature") pfp
WHERE p.nr=pfp.product AND po.nr=2 AND p.nr <> po.nr
AND p."propertyNum1" < (po."propertyNum1"+120) AND p."propertyNum1" > (po."propertyNum1"-120)
AND p."propertyNum2" < (po."propertyNum2"+170) AND p."propertyNum2" > (po."propertyNum2"-170)

Actual Results:

Error: TypeException:user.s945_1[155]:'algebra.join' undefined in: _267:bat[:oid,:int] := algebra.join(_266:bat[:oid,:void], _115:bat[:oid,:int])
39000!program contains errors
SQLState: null
ErrorCode: 0

Comment 17044

Date: 2012-03-06 17:15:59 +0100
From: @drstmane

Could you possibly provide us with all info to reproduce this, i.e., the required DDL (create table, etc.) statements?

Preferably not the whole 14 MB SQL DDL plus data (insert statements) available from http://dl.dropbox.com/u/531378/bsbm/bsbm250k-monetdb-ddl.zip, but the minimal set of pure DDL (create table) statements required for this bug?

Thanks!

Comment 17047

Date: 2012-03-06 17:30:41 +0100
From: Freddy <<freddy_priyatna>>

The create table statements can be downloaded from : http://dl.dropbox.com/u/531378/bsbm/bsbm250k-monetdb-ddl%28create-table%29.zip

Comment 17128

Date: 2012-04-05 15:06:23 +0200
From: @sjoerdmullender

Changeset 6e01ec1907d2 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=6e01ec1907d2

Changeset description:

Added test for bug #3048.  Output to be provided.

Comment 17150

Date: 2012-04-25 15:00:10 +0200
From: @njnes

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=64ed1c560295

Changeset description:

fixed bugs 3047 and 3059
3047 failed because of a bug in the handling of "IN" with correlation and
values on the left side.
3059 failed because the to many column expressions were removed in case of
set operators

Added out put for bug #3048, which runs fine without the m5 joinpath optimizer

Comment 17186

Date: 2012-04-27 14:21:53 +0200
From: @njnes

alternative solution is to removed the virtual id function signatures (like bug #3073)

*** This bug has been marked as a duplicate of bug #3073 ***

Comment 17462

Date: 2012-07-14 08:54:38 +0200
From: @drstmane

re-opened and assigned to Jul2012 as test has been failing for long with Apr2012, Jul2012 & default, with

QUERY = SELECT distinct p.nr, p.label
FROM product p, product po,
(Select distinct pfp1.product FROM productfeatureproduct pfp1,
(SELECT "productFeature" FROM productfeatureproduct WHERE product=2) pfp2
WHERE pfp2."productFeature"=pfp1."productFeature") pfp
WHERE p.nr=pfp.product AND po.nr=2 AND p.nr <> po.nr
AND p."propertyNum1" < (po."propertyNum1"+120) AND p."propertyNum1" >
(po."propertyNum1"-120)
AND p."propertyNum2" < (po."propertyNum2"+170) AND p."propertyNum2" >
(po."propertyNum2"-170);
ERROR = !TypeException:user.s0_1[96]:'algebra.join' undefined in: _198:bat[:oid,:int] := algebra.join(_190:bat[:oid,:void], _191:bat[:oid,:int])
!program contains errors

cf.,

http://monetdb.cwi.nl/testweb/web/testgrid.php?serial=44501:b490b640b431&module=sql&targets=&order=platform,arch,compiler

http://monetdb.cwi.nl/testweb/tests/44501:b490b640b431/GNU-Fedora-x86_64-propcheck-assert/sql/mTests/test/BugTracker-2012/multiple-arithmetic-operations.Bug-3048.err.diff.html

http://monetdb.cwi.nl/testweb/web/testgrid.php?serial=44603:a04c99a2b437&module=sql&targets=&order=platform,arch,compiler

http://monetdb.cwi.nl/testweb/tests/44603:a04c99a2b437/GNU-Fedora-x86_64-propcheck-assert/sql/mTests/test/BugTracker-2012/multiple-arithmetic-operations.Bug-3048.err.diff.html

Comment 17673

Date: 2012-08-24 14:55:53 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 17793

Date: 2012-10-11 10:56:51 +0200
From: @grobian

This bug still exists on Oct2012 branch on all platforms.
http://monetdb.cwi.nl/testweb/web/testgrid.php?serial=45660:2d08000a18dd&module=sql&tstlimit=test/BugTracker-2012

A fix would be nice.

Comment 17958

Date: 2012-11-20 13:26:41 +0100
From: @grobian

another occurrence, which seems to be the same problem:

RD 1353412923544: read final block: 171 bytes
RX 1353412923544: !TypeException:user.s983_1[393]:'algebra.join' undefined
in: _804:bat[:oid,:lng] := algebra.join(_783:bat[:oid,:void],
_798:bat[:oid,:lng])
!39000!program contains errors

Comment 18016

Date: 2012-11-26 19:23:23 +0100
From: @njnes

fixed, ie added the missing signature. Only on oct2012 as on default this is no longer possible (headless).

Comment 18361

Date: 2013-01-22 09:29:06 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.

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