You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2022/10/20 15:01:20 UTC
[GitHub] [doris] lide-reed opened a new issue, #13529: [Bug] Return Unknown column when left join a view within multiple inner join query statement.
lide-reed opened a new issue, #13529:
URL: https://github.com/apache/doris/issues/13529
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues.
### Version
doris 1.1.3-rc02
### What's Wrong?
MySQL [db]> select
-> f1.k1
-> from (
-> select
-> t2.c as k1
-> from t5
-> inner join t2 on t2.x=t5.x
-> inner join t4 on t2.a=t4.a and t2.x in ('ZCR', 'ZDR')
-> ) f1
-> left join v1 on f1.k1=v1.k1;
ERROR 1054 (42S22): errCode = 2, detailMessage = Unknown column 'x' in 't5'
### What You Expected?
No error and do the query.
### How to Reproduce?
DROP table if exists t1;
DROP table if exists t2;
DROP table if exists t4;
DROP table if exists t5;
DROP view if exists v1;
CREATE TABLE `t2` (
`a` varchar(1) NULL COMMENT "",
`x` varchar(1) NULL COMMENT "",
`c` varchar(1) NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`a`)
DISTRIBUTED BY HASH(`a`) BUCKETS 8;
CREATE TABLE `t5` (
`x` varchar(5) NULL COMMENT "",
`d` varchar(3) NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`x`)
DISTRIBUTED BY HASH(`x`) BUCKETS 8;
CREATE TABLE `t4` (
`a` varchar(1) NULL COMMENT "",
`e` varchar(1) NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`a`)
DISTRIBUTED BY HASH(`a`) BUCKETS 8;
CREATE TABLE `t1` (
`k1` varchar(20) NULL COMMENT "",
`dt` date NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`k1`)
DISTRIBUTED BY HASH(`k1`) BUCKETS 6;
CREATE VIEW v1
AS
SELECT k1
FROM t1
WHERE `dt` = (
SELECT max(`dt`)
FROM t1
);
select
f1.k1
from (
select
t2.c as k1
from t5
inner join t2 on t2.x=t5.x
inner join t4 on t2.a=t4.a and t2.x in ('ZCR', 'ZDR')
) f1
left join v1 on f1.k1=v1.k1;
### Anything Else?
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hf200012 commented on issue #13529: [Bug] Return Unknown column when left join a view within multiple inner join query statement.
Posted by GitBox <gi...@apache.org>.
hf200012 commented on issue #13529:
URL: https://github.com/apache/doris/issues/13529#issuecomment-1291734579
It can be avoided by rewriting sql first
```
select
f1.k1
from (
select
t2.c as k1
from t5
inner join t2 on t2.x=t5.x
inner join t4 on t2.a=t4.a WHERE t2.x in ('ZCR', 'ZDR')
) f1
left join v1 on f1.k1=v1.k1;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hf200012 commented on issue #13529: [Bug] Return Unknown column when left join a view within multiple inner join query statement.
Posted by GitBox <gi...@apache.org>.
hf200012 commented on issue #13529:
URL: https://github.com/apache/doris/issues/13529#issuecomment-1285744761
```
mysql> select
-> f1.k1
-> from (
->
-> select
-> bbb.c as k1
-> from t5 aaa
-> left join t2 bbb on bbb.x=aaa.x
-> left join t4 ccc on bbb.a=ccc.a and bbb.x in ('ZCR', 'ZDR')
-> ) f1
-> left join v1 on f1.k1=v1.k1;
Empty set (0.01 sec)
```
The inner join bug
Left join is normal
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org