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

INNER JOIN with Subquery produces mal errors #3034

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

INNER JOIN with Subquery produces mal errors #3034

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

Comments

@monetdb-team
Copy link

Date: 2012-02-21 11:36:30 +0100
From: George Garbis <>
To: SQL devs <>
Version: -- development
CC: charnik, ggarbis, @drstmane

Last updated: 2012-02-29 10:34:23 +0100

Comment 16964

Date: 2012-02-21 11:36:30 +0100
From: George Garbis <>

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:10.0.2) Gecko/20100101 Firefox/10.0.2
Build Identifier: 03600f3e6c1b tip

A query with INNER JOIN between a table and a subquery produces mal errors. These errors concern datatypes of subquery columns.

Reproducible: Always

Steps to Reproduce:

  1. Create tables and insert values
    CREATE TABLE table1 (a INTEGER);
    CREATE TABLE table2 (a INTEGER, b INTEGER);
    INSERT INTO table1 VALUES(1);
    INSERT INTO table1 VALUES(2);
    INSERT INTO table2 VALUES(2, 2);

  2. Pose the problematic query
    SELECT * FROM (SELECT a, 2 AS b FROM table1) t1 INNER JOIN table2 t2 ON (t1.b = t2.b);

Actual Results:

 TypeException:user.s5_1[44]:'bat.mirror' undefined in: _62:any := bat.mirror(_61:int)
 TypeException:user.s5_1[46]:'algebra.join' undefined in: _64:any := algebra.join(_62:any, _63:bat[:oid,:oid])
 TypeException:user.s5_1[47]:'algebra.markH' undefined in: _65:any := algebra.markH(_64:any, _66:oid)
 TypeException:user.s5_1[48]:'algebra.markT' undefined in: _67:any := algebra.markT(_64:any, _66:oid)
 TypeException:user.s5_1[49]:'bat.reverse' undefined in: _68:any := bat.reverse(_67:any)
 TypeException:user.s5_1[50]:'algebra.join' undefined in: _69:any := algebra.join(_68:any, _61:int)
 TypeException:user.s5_1[51]:'algebra.join' undefined in: _70:any := algebra.join(_65:any, _59:bat[:oid,:int])
 TypeException:user.s5_1[55]:'algebra.join' undefined in: _77:any := algebra.join(_76:bat[:void,:oid], _65:any)
 TypeException:user.s5_1[56]:'bat.reverse' undefined in: _78:any := bat.reverse(_77:any)
 TypeException:user.s5_1[57]:'algebra.markT' undefined in: _79:any := algebra.markT(_78:any, _43:oid)
 TypeException:user.s5_1[58]:'bat.reverse' undefined in: _80:any := bat.reverse(_79:any)
 TypeException:user.s5_1[59]:'algebra.leftjoin' undefined in: _81:any := algebra.leftjoin(_80:any, _42:bat[:oid,:int])
 TypeException:user.s5_1[64]:'algebra.project' undefined in: _86:any := algebra.project(_81:any, _83:int)
 TypeException:user.s5_1[65]:'algebra.join' undefined in: _87:any := algebra.join(_86:any, _85:bat[:int,:oid])
 TypeException:user.s5_1[66]:'bat.reverse' undefined in: _88:any := bat.reverse(_87:any)
 TypeException:user.s5_1[67]:'algebra.markT' undefined in: _89:any := algebra.markT(_88:any, _21:oid)
 TypeException:user.s5_1[68]:'bat.reverse' undefined in: _90:any := bat.reverse(_89:any)
 TypeException:user.s5_1[69]:'algebra.leftjoin' undefined in: _91:any := algebra.leftjoin(_90:any, _20:bat[:oid,:int])
 TypeException:user.s5_1[76]:'algebra.markT' undefined in: _103:any := algebra.markT(_87:any, _101:oid)
 TypeException:user.s5_1[77]:'bat.reverse' undefined in: _104:any := bat.reverse(_103:any)
 TypeException:user.s5_1[78]:'algebra.leftjoin' undefined in: _105:any := algebra.leftjoin(_104:any, _81:any)
 TypeException:user.s5_1[79]:'algebra.project' undefined in: _106:any := algebra.project(_105:any, _96:bte)
 TypeException:user.s5_1[86]:'algebra.leftjoin' undefined in: _113:any := algebra.leftjoin(_80:any, _59:bat[:oid,:int])
 TypeException:user.s5_1[87]:'algebra.leftjoin' undefined in: _114:any := algebra.leftjoin(_104:any, _113:any)
 39000!program contains errors

Expected Results:

+------+------+------+----+
| a | b | a | b |
+======+======+======+====+
| 2 | 2 | 2 | 2 |
| 1 | 2 | 2 | 2 |
+------+------+------+----+

If a user creates a table with the results of the aforementioned query and pose the same query with the new table instead of the subquery, then query executes flawlessly and expected results are returned.

CREATE TABLE table3 (a INTEGER, b INTEGER);
INSERT INTO table3(a,b) (SELECT a, 2 AS b FROM table1);
SELECT * FROM (SELECT a, b FROM table3) t1 INNER JOIN table2 t2 ON (t1.b = t2.b);

Comment 16974

Date: 2012-02-22 23:53:06 +0100
From: @drstmane

IMHO the problem is not the inner join, but an incorrect treatment of the constant "2 AS b" column of the sub query.

In any case, it is a bug.

Comment 16980

Date: 2012-02-23 14:44:25 +0100
From: @njnes

Changeset 86d94f21e92c 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=86d94f21e92c

Changeset description:

fixed bugs 3034, 2964 2946 and 3017

The rel2bin_join didn't handle a bunch of corner cases
@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 Nov 9, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working major SQL
Projects
None yet
Development

No branches or pull requests

2 participants