[jira] [Commented] (SPARK-28229) How to implement the same functionality as presto's TRY(expr) ?
[ https://issues.apache.org/jira/browse/SPARK-28229?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16881007#comment-16881007 ] U Shaw commented on SPARK-28229: [~hyukjin.kwon] Ok. > How to implement the same functionality as presto's TRY(expr) ? > --- > > Key: SPARK-28229 > URL: https://issues.apache.org/jira/browse/SPARK-28229 > Project: Spark > Issue Type: Question > Components: SQL >Affects Versions: 2.4.3 >Reporter: U Shaw >Priority: Major > > How to implement the same functionality as presto's TRY(expr) ? > Is there already a similar function? > -- > TRY > try(expression) > Evaluate an expression and handle certain types of errors by returning NULL. > In cases where it is preferable that queries produce NULL or default values > instead of failing when corrupt or invalid data is encountered, the TRY > function may be useful. To specify default values, the TRY function can be > used in conjunction with the COALESCE function. > The following errors are handled by TRY: > Division by zero > Invalid cast or function argument > Numeric value out of range > Examples > Source table with some invalid data: > SELECT * FROM shipping; > origin_state | origin_zip | packages | total_cost > --++--+ > California | 94131 | 25 |100 > California | P332a |5 | 72 > California | 94025 |0 |155 > New Jersey | 08544 | 225 |490 > (4 rows) > Query failure without TRY: > SELECT CAST(origin_zip AS BIGINT) FROM shipping; > Query failed: Can not cast 'P332a' to BIGINT > NULL values with TRY: > SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping; > origin_zip > > 94131 > NULL > 94025 > 08544 > (4 rows) > Query failure without TRY: > SELECT total_cost / packages AS per_package FROM shipping; > Query failed: / by zero > Default values with TRY and COALESCE: > SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping; > per_package > - > 4 > 14 > 0 > 19 > (4 rows) -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Created] (SPARK-28229) How to implement the same functionality as presto's TRY(expr) ?
U Shaw created SPARK-28229: -- Summary: How to implement the same functionality as presto's TRY(expr) ? Key: SPARK-28229 URL: https://issues.apache.org/jira/browse/SPARK-28229 Project: Spark Issue Type: Question Components: SQL Affects Versions: 2.4.3 Reporter: U Shaw How to implement the same functionality as presto's TRY(expr) ? Is there already a similar function? -- TRY try(expression) Evaluate an expression and handle certain types of errors by returning NULL. In cases where it is preferable that queries produce NULL or default values instead of failing when corrupt or invalid data is encountered, the TRY function may be useful. To specify default values, the TRY function can be used in conjunction with the COALESCE function. The following errors are handled by TRY: Division by zero Invalid cast or function argument Numeric value out of range Examples Source table with some invalid data: SELECT * FROM shipping; origin_state | origin_zip | packages | total_cost --++--+ California | 94131 | 25 |100 California | P332a |5 | 72 California | 94025 |0 |155 New Jersey | 08544 | 225 |490 (4 rows) Query failure without TRY: SELECT CAST(origin_zip AS BIGINT) FROM shipping; Query failed: Can not cast 'P332a' to BIGINT NULL values with TRY: SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping; origin_zip 94131 NULL 94025 08544 (4 rows) Query failure without TRY: SELECT total_cost / packages AS per_package FROM shipping; Query failed: / by zero Default values with TRY and COALESCE: SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping; per_package - 4 14 0 19 (4 rows) -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Resolved] (SPARK-28011) SQL parse error when there are too many aliases in the table
[ https://issues.apache.org/jira/browse/SPARK-28011?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] U Shaw resolved SPARK-28011. Resolution: Invalid > SQL parse error when there are too many aliases in the table > > > Key: SPARK-28011 > URL: https://issues.apache.org/jira/browse/SPARK-28011 > Project: Spark > Issue Type: Bug > Components: SQL >Affects Versions: 2.1.1 >Reporter: U Shaw >Priority: Major > > A sql syntax error is reported when the following statement is executed. > .. > FROM > menu_item_categories_tmp t1 > LEFT JOIN menu_item_categories_tmp t2 ON t1.icat_id = > t2.icat_parent_icat_id > AND t1.tenant_id = t2.tenant_id > AND t2.icat_status != 'd' > LEFT JOIN menu_item_categories_tmp t3 ON t2.icat_id = > t3.icat_parent_icat_id > AND t2.tenant_id = t3.tenant_id > AND t3.icat_status != 'd' > LEFT JOIN menu_item_categories_tmp t4 ON t3.icat_id = > t4.icat_parent_icat_id > AND t3.tenant_id = t4.tenant_id > AND t4.icat_status != 'd' > LEFT JOIN menu_item_categories_tmp t5 ON t4.icat_id = > t5.icat_parent_icat_id > AND t4.tenant_id = t5.tenant_id > AND t5.icat_status != 'd' > LEFT JOIN menu_item_categories_tmp t6 ON t5.icat_id = > t6.icat_parent_icat_id > AND t5.tenant_id = t6.tenant_id > AND t6.icat_status != 'd' > WHERE > t1.icat_parent_icat_id = '0' > AND t1.icat_status != 'd' > ) SELECT DISTINCT > tenant_id AS tenant_id, > type AS type, > CASE > > WHEN t2.num >= 1 THEN > level0 ELSE NULL > END AS level0, > CASE > > WHEN t2.num >= 2 THEN > level1 ELSE NULL > END AS level1, > CASE > > WHEN t2.num >= 3 THEN > level2 ELSE NULL > END AS level2, > CASE > .. > -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Created] (SPARK-28011) SQL parse error when there are too many aliases in the table
U Shaw created SPARK-28011: -- Summary: SQL parse error when there are too many aliases in the table Key: SPARK-28011 URL: https://issues.apache.org/jira/browse/SPARK-28011 Project: Spark Issue Type: Bug Components: SQL Affects Versions: 2.1.1 Reporter: U Shaw A sql syntax error is reported when the following statement is executed. .. FROM menu_item_categories_tmp t1 LEFT JOIN menu_item_categories_tmp t2 ON t1.icat_id = t2.icat_parent_icat_id AND t1.tenant_id = t2.tenant_id AND t2.icat_status != 'd' LEFT JOIN menu_item_categories_tmp t3 ON t2.icat_id = t3.icat_parent_icat_id AND t2.tenant_id = t3.tenant_id AND t3.icat_status != 'd' LEFT JOIN menu_item_categories_tmp t4 ON t3.icat_id = t4.icat_parent_icat_id AND t3.tenant_id = t4.tenant_id AND t4.icat_status != 'd' LEFT JOIN menu_item_categories_tmp t5 ON t4.icat_id = t5.icat_parent_icat_id AND t4.tenant_id = t5.tenant_id AND t5.icat_status != 'd' LEFT JOIN menu_item_categories_tmp t6 ON t5.icat_id = t6.icat_parent_icat_id AND t5.tenant_id = t6.tenant_id AND t6.icat_status != 'd' WHERE t1.icat_parent_icat_id = '0' AND t1.icat_status != 'd' ) SELECT DISTINCT tenant_id AS tenant_id, type AS type, CASE WHEN t2.num >= 1 THEN level0 ELSE NULL END AS level0, CASE WHEN t2.num >= 2 THEN level1 ELSE NULL END AS level1, CASE WHEN t2.num >= 3 THEN level2 ELSE NULL END AS level2, CASE .. -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Updated] (SPARK-27741) Transitivity on predicate pushdown
[ https://issues.apache.org/jira/browse/SPARK-27741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] U Shaw updated SPARK-27741: --- Fix Version/s: 2.4.0 > Transitivity on predicate pushdown > --- > > Key: SPARK-27741 > URL: https://issues.apache.org/jira/browse/SPARK-27741 > Project: Spark > Issue Type: New Feature > Components: SQL >Affects Versions: 2.1.1 >Reporter: U Shaw >Priority: Major > Fix For: 2.4.0 > > > When using inner join, where conditions can be passed to join on, and when > using outer join, even if the conditions are the same, only the predicate is > pushed down to left or right. > As follows: > select * from t1 left join t2 on t1.id=t2.id where t1.id=1 > --> select * from t1 left join on t1.id=t2.id and t2.id=1 where t1.id=1 > Is Catalyst can support transitivity on predicate pushdown ? -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Updated] (SPARK-27741) Transitivity on predicate pushdown
[ https://issues.apache.org/jira/browse/SPARK-27741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] U Shaw updated SPARK-27741: --- Affects Version/s: (was: 2.4.3) 2.1.1 > Transitivity on predicate pushdown > --- > > Key: SPARK-27741 > URL: https://issues.apache.org/jira/browse/SPARK-27741 > Project: Spark > Issue Type: New Feature > Components: SQL >Affects Versions: 2.1.1 >Reporter: U Shaw >Priority: Major > > When using inner join, where conditions can be passed to join on, and when > using outer join, even if the conditions are the same, only the predicate is > pushed down to left or right. > As follows: > select * from t1 left join t2 on t1.id=t2.id where t1.id=1 > --> select * from t1 left join on t1.id=t2.id and t2.id=1 where t1.id=1 > Is Catalyst can support transitivity on predicate pushdown ? -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Updated] (SPARK-27741) Transitivity on predicate pushdown
[ https://issues.apache.org/jira/browse/SPARK-27741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] U Shaw updated SPARK-27741: --- Description: When using inner join, where conditions can be passed to join on, and when using outer join, even if the conditions are the same, only the predicate is pushed down to left or right. As follows: select * from t1 left join t2 on t1.id=t2.id where t1.id=1 --> select * from t1 left join on t1.id=t2.id and t2.id=1 where t1.id=1 Is Catalyst can support transitivity on predicate pushdown ? > Transitivity on predicate pushdown > --- > > Key: SPARK-27741 > URL: https://issues.apache.org/jira/browse/SPARK-27741 > Project: Spark > Issue Type: New Feature > Components: SQL >Affects Versions: 2.4.3 >Reporter: U Shaw >Priority: Major > > When using inner join, where conditions can be passed to join on, and when > using outer join, even if the conditions are the same, only the predicate is > pushed down to left or right. > As follows: > select * from t1 left join t2 on t1.id=t2.id where t1.id=1 > --> select * from t1 left join on t1.id=t2.id and t2.id=1 where t1.id=1 > Is Catalyst can support transitivity on predicate pushdown ? -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Created] (SPARK-27741) Transitivity on predicate pushdown
U Shaw created SPARK-27741: -- Summary: Transitivity on predicate pushdown Key: SPARK-27741 URL: https://issues.apache.org/jira/browse/SPARK-27741 Project: Spark Issue Type: New Feature Components: SQL Affects Versions: 2.4.3 Reporter: U Shaw -- This message was sent by Atlassian JIRA (v7.6.3#76005) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
