[ https://issues.apache.org/jira/browse/TAJO-162?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hyunsik Choi reassigned TAJO-162:
---------------------------------
Assignee: Hyunsik Choi
> 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
> Assignee: Hyunsik Choi
>
> 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