[MDEV-12818] Inner join output is incorrect (extra row is returned) Created: 2017-05-16  Updated: 2017-06-24  Resolved: 2017-06-24

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.2.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Seong Yoon Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

Windows 10

Attachments: Text File mariadb_ddl_dml.txt    

 Description   

1. ddl/dml is attached.
2. using server - MariaDB 10.2.4 RC
using client - version 1.5.9 (mariadb-java-client-1.5.9.jar)
3. incorrect results (extra row) for the below sql.

SELECT
 
    `T2`.`PNO`, 
    `T1`.`SNO`, 
    `T1`.`PNO`, 
    `T1`.`QTY`
 
FROM
 
    `TPARTS` `T2`, 
    `TPARTS` `T3`, 
    (
 
    SELECT
 
        `SNO`, 
        `PNO`, 
        `QTY`
 
    FROM
 
        `TSUPPLY` 
    WHERE 
        `SNO` = 'S1' AND
 
        `PNO` = 'P1'
 
    ) T1 
WHERE 
    `T2`.`PNO` = `T1`.`PNO` AND
 
    `T2`.`PNO` = `T3`.`PNO`

Expected results

 
PNO SNO PNO QTY
 
P1	S1	P1	200
 
P1	S1	P1	700

MariaDB results

 
PNO SNO PNO QTY
 
P1	S1	P1	200
 
P1	S1	P1	700
 
P1	S5	P1	100



 Comments   
Comment by Elena Stepanova [ 2017-05-18 ]

Could you please attach your cnf file(s), and paste the output of 

EXPLAIN EXTENDED <your problematic query>;
 
SHOW WARNINGS;

Thanks.

Comment by Seong Yoon [ 2017-05-26 ]

I am an end user and am not familiar with those.
Please reproduce the issue in your end using that I attahced ddl/dml and sqls.
Thanks.

Comment by Elena Stepanova [ 2017-05-26 ]

You don't need to be familiar with them, please just copy-paste the following to your client program and then copy-paste the complete output from there to here:

EXPLAIN EXTENDED 
SELECT
 
    `T2`.`PNO`, `T1`.`SNO`, `T1`.`PNO`, `T1`.`QTY`
 
FROM
 
    `TPARTS` `T2`, `TPARTS` `T3`, 
    (
 
    SELECT
 
        `SNO`, `PNO`, `QTY`
 
    FROM
 
        `TSUPPLY` 
    WHERE 
        `SNO` = 'S1' AND `PNO` = 'P1'
 
    ) T1 
WHERE 
    `T2`.`PNO` = `T1`.`PNO` AND `T2`.`PNO` = `T3`.`PNO`;
 
SHOW WARNINGS;


SELECT
 
    `T2`.`PNO`, `T1`.`SNO`, `T1`.`PNO`, `T1`.`QTY`
 
FROM
 
    `TPARTS` `T2`, `TPARTS` `T3`, 
    (
 
    SELECT
 
        `SNO`, `PNO`, `QTY`
 
    FROM
 
        `TSUPPLY` 
    WHERE 
        `SNO` = 'S1' AND `PNO` = 'P1'
 
    ) T1 
WHERE 
    `T2`.`PNO` = `T1`.`PNO` AND `T2`.`PNO` = `T3`.`PNO`
 
;
 
SHOW VARIABLES;

The output will be quite long, that's normal, copy-paste all of it.

Thanks.

Comment by Elena Stepanova [ 2017-06-24 ]

If you have further information on the issue, please comment and it will be re-opened.

Generated at Thu Feb 08 08:00:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.