{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/22124","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/22124/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/22124/comments","events_url":"https://api.github.com/repos/apache/spark/issues/22124/events","html_url":"https://github.com/apache/spark/pull/22124","id":351323343,"node_id":"MDExOlB1bGxSZXF1ZXN0MjA4OTQ5NDcz","number":22124,"title":"[SPARK-25135][SQL] Insert datasource table may all null when select from view","user":{"login":"wangyum","id":5399861,"node_id":"MDQ6VXNlcjUzOTk4NjE=","avatar_url":"https://avatars0.githubusercontent.com/u/5399861?v=4","gravatar_id":"","url":"https://api.github.com/users/wangyum","html_url":"https://github.com/wangyum","followers_url":"https://api.github.com/users/wangyum/followers","following_url":"https://api.github.com/users/wangyum/following{/other_user}","gists_url":"https://api.github.com/users/wangyum/gists{/gist_id}","starred_url":"https://api.github.com/users/wangyum/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wangyum/subscriptions","organizations_url":"https://api.github.com/users/wangyum/orgs","repos_url":"https://api.github.com/users/wangyum/repos","events_url":"https://api.github.com/users/wangyum/events{/privacy}","received_events_url":"https://api.github.com/users/wangyum/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":10,"created_at":"2018-08-16T18:44:01Z","updated_at":"2018-08-19T17:06:05Z","closed_at":null,"author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/22124","html_url":"https://github.com/apache/spark/pull/22124","diff_url":"https://github.com/apache/spark/pull/22124.diff","patch_url":"https://github.com/apache/spark/pull/22124.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\nHow to reproduce:\r\n```scala\r\nval path = \"/tmp/spark/parquet\"\r\nval cnt = 30\r\nspark.range(cnt).selectExpr(\"cast(id as bigint) as col1\", \"cast(id as bigint) as col2\").write.mode(\"overwrite\").parquet(path)\r\nspark.sql(s\"CREATE TABLE table1(col1 bigint, col2 bigint) using parquet location '$path'\")\r\nspark.sql(\"create view view1 as select col1, col2 from table1 where col1 > -20\")\r\n// The column name of table2 is inconsistent with the column name of view1.\r\nspark.sql(\"create table table2 (COL1 BIGINT, COL2 BIGINT) using parquet\")\r\n// When querying the view, ensure that the column name of the query matches the column name of the target table.\r\nspark.sql(\"insert overwrite table table2 select COL1, COL2 from view1\")\r\n// At this time, the column name of the target table is uppercase, but the column name of the Parquet file is consistent with the case of the previous view(It's lower case). so all nulls.\r\nspark.table(\"table2\").show\r\n```\r\nThe root cause is when insert a table contains view. for example:\r\n```sql\r\ninsert overwrite table table2 select COL1, COL2 from view1\r\n```\r\nthe execution plan is:\r\n```sql\r\n...\r\nSchema: root\r\n-- SITE_ID: long (nullable = true)\r\n-- LEAF_CATEG_ID: long (nullable = true)\r\n), org.apache.spark.sql.execution.datasources.InMemoryFileIndex@fa5a0b98, [SITE_ID#86L, LEAF_CATEG_ID#87L]\r\n+- Project [SITE_ID#86L, LEAF_CATEG_ID#87L]\r\n   +- SubqueryAlias test_view\r\n      +- View (`default`.`test_view`, [site_id#86L,leaf_categ_id#87L])\r\n         +- Project [cast(site_id#80L as bigint) AS site_id#86L, cast(leaf_categ_id#81L as bigint) AS leaf_categ_id#87L]\r\n            +- Project [site_id#80L, leaf_categ_id#81L]\r\n               +- Filter (site_id#80L > cast(200 as bigint))\r\n                  +- SubqueryAlias dw_category_groupings\r\n                     +- Relation[site_id#80L,leaf_categ_id#81L] parquet\r\n```\r\n`[SITE_ID#86L, LEAF_CATEG_ID#87L]` and \r\n```View (`default`.`test_view`, [site_id#86L,leaf_categ_id#87L])``` Have the same `exprId`, but the `name` is inconsistent.\r\nBut `[SITE_ID#86L, LEAF_CATEG_ID#87L]` is exactly the same as the column name of the target table column.\r\nSo `DDLPreprocessingUtils.castAndRenameQueryOutput()` can matched, will not do any rename.\r\nSo the target table parquet file column is lower case.\r\nThis will triggers another issue [(SPARK-25132)](https://issues.apache.org/jira/browse/SPARK-25132) and return `null`.\r\n\r\n\r\nThis pr fix this issue.\r\n\r\n## How was this patch tested?\r\n\r\nunit tests\r\n"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/414141260","html_url":"https://github.com/apache/spark/pull/22124#issuecomment-414141260","issue_url":"https://api.github.com/repos/apache/spark/issues/22124","id":414141260,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNDE0MTI2MA==","user":{"login":"gengliangwang","id":1097932,"node_id":"MDQ6VXNlcjEwOTc5MzI=","avatar_url":"https://avatars0.githubusercontent.com/u/1097932?v=4","gravatar_id":"","url":"https://api.github.com/users/gengliangwang","html_url":"https://github.com/gengliangwang","followers_url":"https://api.github.com/users/gengliangwang/followers","following_url":"https://api.github.com/users/gengliangwang/following{/other_user}","gists_url":"https://api.github.com/users/gengliangwang/gists{/gist_id}","starred_url":"https://api.github.com/users/gengliangwang/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gengliangwang/subscriptions","organizations_url":"https://api.github.com/users/gengliangwang/orgs","repos_url":"https://api.github.com/users/gengliangwang/repos","events_url":"https://api.github.com/users/gengliangwang/events{/privacy}","received_events_url":"https://api.github.com/users/gengliangwang/received_events","type":"User","site_admin":false},"created_at":"2018-08-19T17:06:05Z","updated_at":"2018-08-19T17:06:05Z","author_association":"CONTRIBUTOR","body":"Hi @wangyum , thanks for working on this.\r\nCan you simplify the reproducing case? E.g. Select only one column should be enough.\r\nAlso, in the PR description, somehow there are column names `SITE_ID` and `LEAF_CATEG_ID` comes from nowhere."}}
{"actor":{"display_login":"gengliangwang"}}