JaeHwa Jung created TAJO-162:
--------------------------------
Summary: TAJO-151 / Multiple union query with a table subquery
causes unexpected results.
Key: TAJO-162
URL: https://issues.apache.org/jira/browse/TAJO-162
Project: Tajo
Issue Type: Bug
Reporter: JaeHwa Jung
I tested a little queries which include multiple union query with a table
subquery. So, I found that Tajo can't handle 4 or more union queries with a
table subquery.
I wrote 4 more union queries as follows:
{code:xml}
SELECT *
FROM
(
SELECT
a.reg_date,
a.user_id
FROM
(SELECT buy_date AS bdate
FROM buy_history
WHERE host='a0') as a JOIN (SELECT * FROM category_info WHERE
category_id ='A1') as b ON a.id=b.id
UNION ALL
SELECT
a.reg_date,
a.user_id
FROM
(SELECT buy_date AS bdate
FROM buy_history
WHERE host='b0') as a JOIN (SELECT * FROM category_info WHERE
category_id ='B1') as b ON a.id=b.id
UNION ALL
SELECT
a.reg_date,
a.user_id
FROM
(SELECT buy_date AS bdate
FROM buy_history
WHERE host='c0') as a JOIN (SELECT * FROM category_info WHERE
category_id ='C1') as b ON a.id=b.id
UNION ALL
SELECT
a.reg_date,
a.user_id
FROM
(SELECT buy_date AS bdate
FROM buy_history
WHERE host='d0') as a JOIN (SELECT * FROM category_info WHERE
category_id ='D1') as b ON a.id=b.id
) as T
{code}
In this case, SQLAnalyzer prints as follows:
{code:xml}
{
"all": true,
"distinct": false,
"child": {
"relations": [
{
"subquery": {
"distinct": false,
"left": {
"distinct": false,
"left": {
"all": false,
"distinct": false,
"targets": [
{
"expr": {
"tableName": "a",
"name": "reg_date",
"opType": "Column"
},
"opType": "Target"
},
{
"expr": {
"tableName": "a",
"name": "user_id",
"opType": "Column"
},
"opType": "Target"
}
],
"child": {
"relations": [
{
"joinType": "INNER",
"joinQual": {
"left": {
"tableName": "a",
"name": "id",
"opType": "Column"
},
"right": {
"tableName": "b",
"name": "id",
"opType": "Column"
},
"opType": "Equals"
},
"natural": false,
"left": {
"subquery": {
"all": false,
"distinct": false,
"targets": [
{
"expr": {
"name": "buy_date",
"opType": "Column"
},
"alias": "bdate",
"opType": "Target"
}
],
"child": {
"qual": {
"left": {
"name": "host",
"opType": "Column"
},
"right": {
"value": "a0",
"valueType": "String",
"opType": "Literal"
},
"opType": "Equals"
},
"child": {
"relations": [
{
"tableName": "buy_history",
"opType": "Relation"
}
],
"opType": "RelationList"
},
"opType": "Filter"
},
"opType": "Projection"
},
"tableName": "a",
"opType": "TableSubQuery"
},
"right": {
"subquery": {
"all": true,
"distinct": false,
"child": {
"qual": {
"left": {
"name": "category_id",
"opType": "Column"
},
"right": {
"value": "A1",
"valueType": "String",
"opType": "Literal"
},
"opType": "Equals"
},
"child": {
"relations": [
{
"tableName": "category_info",
"opType": "Relation"
}
],
"opType": "RelationList"
},
"opType": "Filter"
},
"opType": "Projection"
},
"tableName": "b",
"opType": "TableSubQuery"
},
"opType": "Join"
}
],
"opType": "RelationList"
},
"opType": "Projection"
},
"right": {
"all": false,
"distinct": false,
"targets": [
{
"expr": {
"tableName": "a",
"name": "reg_date",
"opType": "Column"
},
"opType": "Target"
},
{
"expr": {
"tableName": "a",
"name": "user_id",
"opType": "Column"
},
"opType": "Target"
}
],
"child": {
"relations": [
{
"joinType": "INNER",
"joinQual": {
"left": {
"tableName": "a",
"name": "id",
"opType": "Column"
},
"right": {
"tableName": "b",
"name": "id",
"opType": "Column"
},
"opType": "Equals"
},
"natural": false,
"left": {
"subquery": {
"all": false,
"distinct": false,
"targets": [
{
"expr": {
"name": "buy_date",
"opType": "Column"
},
"alias": "bdate",
"opType": "Target"
}
],
"child": {
"qual": {
"left": {
"name": "host",
"opType": "Column"
},
"right": {
"value": "b0",
"valueType": "String",
"opType": "Literal"
},
"opType": "Equals"
},
"child": {
"relations": [
{
"tableName": "buy_history",
"opType": "Relation"
}
],
"opType": "RelationList"
},
"opType": "Filter"
},
"opType": "Projection"
},
"tableName": "a",
"opType": "TableSubQuery"
},
"right": {
"subquery": {
"all": true,
"distinct": false,
"child": {
"qual": {
"left": {
"name": "category_id",
"opType": "Column"
},
"right": {
"value": "B1",
"valueType": "String",
"opType": "Literal"
},
"opType": "Equals"
},
"child": {
"relations": [
{
"tableName": "category_info",
"opType": "Relation"
}
],
"opType": "RelationList"
},
"opType": "Filter"
},
"opType": "Projection"
},
"tableName": "b",
"opType": "TableSubQuery"
},
"opType": "Join"
}
],
"opType": "RelationList"
},
"opType": "Projection"
},
"opType": "Union"
},
"right": {
"all": false,
"distinct": false,
"targets": [
{
"expr": {
"tableName": "a",
"name": "reg_date",
"opType": "Column"
},
"opType": "Target"
},
{
"expr": {
"tableName": "a",
"name": "user_id",
"opType": "Column"
},
"opType": "Target"
}
],
"child": {
"relations": [
{
"joinType": "INNER",
"joinQual": {
"left": {
"tableName": "a",
"name": "id",
"opType": "Column"
},
"right": {
"tableName": "b",
"name": "id",
"opType": "Column"
},
"opType": "Equals"
},
"natural": false,
"left": {
"subquery": {
"all": false,
"distinct": false,
"targets": [
{
"expr": {
"name": "buy_date",
"opType": "Column"
},
"alias": "bdate",
"opType": "Target"
}
],
"child": {
"qual": {
"left": {
"name": "host",
"opType": "Column"
},
"right": {
"value": "c0",
"valueType": "String",
"opType": "Literal"
},
"opType": "Equals"
},
"child": {
"relations": [
{
"tableName": "buy_history",
"opType": "Relation"
}
],
"opType": "RelationList"
},
"opType": "Filter"
},
"opType": "Projection"
},
"tableName": "a",
"opType": "TableSubQuery"
},
"right": {
"subquery": {
"all": true,
"distinct": false,
"child": {
"qual": {
"left": {
"name": "category_id",
"opType": "Column"
},
"right": {
"value": "C1",
"valueType": "String",
"opType": "Literal"
},
"opType": "Equals"
},
"child": {
"relations": [
{
"tableName": "category_info",
"opType": "Relation"
}
],
"opType": "RelationList"
},
"opType": "Filter"
},
"opType": "Projection"
},
"tableName": "b",
"opType": "TableSubQuery"
},
"opType": "Join"
}
],
"opType": "RelationList"
},
"opType": "Projection"
},
"opType": "Union"
},
"tableName": "T",
"opType": "TableSubQuery"
}
],
"opType": "RelationList"
},
"opType": "Projection"
}
{code}
But I can't find 'd0' and 'D1' token in the result.
So I tested 3 union queries with table subquery. As a result, SQLAnalyzer
printed all tokens without omission.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
