tajo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hyunsik Choi (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (TAJO-162) TAJO-151 / Multiple union query with a table subquery causes unexpected results.
Date Mon, 09 Sep 2013 02:39:52 GMT

    [ https://issues.apache.org/jira/browse/TAJO-162?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13761584#comment-13761584
] 

Hyunsik Choi commented on TAJO-162:
-----------------------------------

I've started digging this problem.
                
> 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

Mime
View raw message