[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16755749#comment-16755749
]
Hyukjin Kwon commented on SPARK-26777:
--
Please narrow down the problem, and describe your analysis. self-contained
reproducer if possible. It sounds like just requesting investigations.
Please reopen after filling sufficient narrowed-down analysis, and
self-contained reproducer.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.i
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16755821#comment-16755821
]
Yuri Budilov commented on SPARK-26777:
--
hello,
this is self-contained, just create the schema as I supplied above and run the
Spark SQL query as I supplied.
Only 1 table is involved and you dont need any data as query fails before it
reads data.
It throws the error as described above in Spark 2.4 only and it brings back the
correct results in Spark 2.3.2 (both on AWS EMR).
I am not sure what else you need ?
Here is how I created the session from Pyspark:
spark = SparkSession \
.builder \
.appName("Python Spark SQL data source example") \
.config("hive.metastore.client.factory.class",
"com.amazonaws.glue.catalog.metastore.AWSGlueDataCatalogHiveClientFactory") \
.config("hive.exec.dynamic.partition", "true") \
.config("spark.sql.hive.convertMetastoreParquet", "false") \
.config("spark.sql.parquet.mergeSchema", "true") \
.config("hive.exec.dynamic.partition.mode", "nonstrict") \
.config("spark.debug.maxToStringFields", 200) \
.config("parquet.enable.dictionary", "true") \
.enableHiveSupport() \
.getOrCreate()
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecrea
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16755949#comment-16755949
]
Hyukjin Kwon commented on SPARK-26777:
--
Please don't reopen. Take a look https://spark.apache.org/contributing.html
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.item.series` string, `message.item.detailsurl` string,
> `message.item.pricetype` string, `message.item.description` string,
> `message.item.colour` string, `message
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16755947#comment-16755947
]
Hyukjin Kwon commented on SPARK-26777:
--
Please provide a _minimised_, _self-runninable_ reproducers if possible rather
than making people putting duplicated efforts. This just sounds like requesting
investigation.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.item.series` string, `message.item.detailsurl` string,
> `message.
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16756515#comment-16756515
]
Yuri Budilov commented on SPARK-26777:
--
good luck.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.item.series` string, `message.item.detailsurl` string,
> `message.item.pricetype` string, `message.item.description` string,
> `message.item.colour` string, `message.item.badge` string,
> `message.item.odometer` string, `message
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16766526#comment-16766526
]
t oo commented on SPARK-26777:
--
[~yuri.budilov] - can you try in spark-shell instead of pyspark? Also with
plain spark instead of EMR spark?
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.item.series` string, `message.item.detailsurl` string,
> `message.item.pricetype` string, `message.item.description` string,
> `message.item.colour
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16766613#comment-16766613
]
Yuri Budilov commented on SPARK-26777:
--
I split the above query from 1 into 3 and it worked OK on 2.4.0 EMR Spark, so I
was able to fix my production job on latest EMR Spark 2.4, which is what I am
paid to do. This SQL statement works "as is" on Presto/Athena, so it is valid
SQL code on Athena and on EMR Spark 2.3.x.
Since I posted this, I also tried using *explicit* query table *aliases*
everywhere in SQL (i.e. *_select_* *_from table_name as alias z_*, in all
*sub-select* statements), it still *failed even with explicit aliases.*
Regarding your request: sorry, I do not and will not use Scala and I will not
build a standalone Spark cluster on-premises either - because both of these
tests are totally irrelevant to me, even if they do work OK in 2.4, it of no
value to me, the end user of Spark.
Fact is - it does not work on PySpark Spark 2.4 EMR but it used to work OK on
Pyspark EMR Spark 2.3.x.
This is all I care about. It looks like a regression bug to me. If you folks
don't want to fix it, this is your choice, I do understand this is the world of
free-software so I do not expect anything.
If the problem is not a bug but a feature-by-design and if I am doing something
wrong with my SQL then some documentation would be greatly appreciated so I and
future readers can learn what not to do on Spark SQL. Perhaps delete this Jira
Issue so it does not mislead anyone else.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16766653#comment-16766653
]
Jungtaek Lim commented on SPARK-26777:
--
I'd rather concern about EMR vs vanilla (Apache version), and if vanilla has
same issue IMHO we can say it's a bug.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.item.series` string, `message.item.detailsurl` string,
> `message.item.pricetype` string, `message.item.description` string,
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16766698#comment-16766698
]
Hyukjin Kwon commented on SPARK-26777:
--
There are already bunches of issues open (2500+). Leaving this resolved as
Incomplete, Cannot Reproduce, or Invalid does not necessarily mean the issues
are completely fixed with full details.
If there are developers who's willing to take a look for those issues, people
can investigate, reopen, and make a PR for the JIRA.
I am _resolving_ JIRAs (not _closing_ permanently). Those details should be
made to fix the issue anyway. This Jira can be reopened at that stage.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16767008#comment-16767008
]
Jungtaek Lim commented on SPARK-26777:
--
I actually missed reporter's last comment. Looks like it is the case of former:
so I'd rather just say Apache community is not responsible to resolve any issue
on versions which are specific to packaging vendors and cloud vendors. So if
you mind reproducing it in Apache version, you may need to just ask to AWS
technical support rather than complaining to Apache community. What you're
paying to AWS includes the right to ask to AWS for technical issue.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `m
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16771304#comment-16771304
]
Ilya Peysakhov commented on SPARK-26777:
[~hyukjin.kwon]
[~yuri.budilov]
[~kabhwan]
Hello folks,
i went ahead and ran this in vanilla Scala Spark 2.4 on windows, EMR 5.20
(scala, spark 2.4) and EMR 5.17 (scala, spark 2.3.1) and have replicated the
issue.
Here is the code
spark.sql("select '2018-01-01' as latest_date, 'source1' as source UNION ALL
select '2018-01-02', 'source2' UNION ALL select '2018-01-03' , 'source3' UNION
ALL select '2018-01-04' ,'source4' ").write.save("/latest_dates")
val mydatetable = spark.read.load("/latest_dates")
mydatetable.createOrReplaceTempView("latest_dates")
spark.sql("select 50 as mysum, '2018-01-01' as date UNION ALL select 100,
'2018-01-02' UNION ALL select 300, '2018-01-03' UNION ALL select 3444,
'2018-01-01' UNION ALL select 600, '2018-08-30'
").write.partitionBy("date").save("/mypartitioneddata")
val source1 = spark.read.load("/mypartitioneddata")
source1.createOrReplaceTempView("source1")
spark.sql("select max(date), 'source1' as category from source1 where date >=
(select latest_date from latest_dates where source='source1') ").show
The error comes up in vanilla spark 2.4 on windows and EMR 5.20, not vanilla
spark 2.3.1 or EMR 5.17. This is definitely not an AWS/EMR issue.
Let me know if you need more details. Should i post a new issue?
I did notice that if you just use a subquery on hardcoded values, this does not
happen, it only happens when you subquery an existing table (like in the method
above, reading data into a view, or in AWS using the Glue catalog).
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._i
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16771486#comment-16771486
]
Hyukjin Kwon commented on SPARK-26777:
--
Please open a separate issue if you're not very sure if it's the same issue or
not.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.item.series` string, `message.item.detailsurl` string,
> `message.item.pricetype` string, `message.item.description` string,
> `message.item.colour` string,
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16772071#comment-16772071
]
Ilya Peysakhov commented on SPARK-26777:
[~hyukjin.kwon]
It's the same error/issue, i just addressed the questions that [~kabhwan] and
[~toopt4] brought up about this being potentially exclusive to EMR and not
vanilla Spark. It's observed in standard Spark 2.4 in Scala with the Scala code
that i posted within a spark-shell (also ran into these issues when compiled
into a JAR file and ran using spark-submit).
If you rather me open a new issue, let me know. Happy to add any details if it
helps.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` st
[jira] [Commented] (SPARK-26777) SQL worked in 2.3.2 and fails in 2.4.0
[
https://issues.apache.org/jira/browse/SPARK-26777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16777642#comment-16777642
]
Jungtaek Lim commented on SPARK-26777:
--
[~ilya745]
You may want to post a new issue to update the summary and description to your
reproducer. If your reproducer requires some input files zipping your project
and attaching would be much helpful, like attachment in SPARK-22000.
> SQL worked in 2.3.2 and fails in 2.4.0
> --
>
> Key: SPARK-26777
> URL: https://issues.apache.org/jira/browse/SPARK-26777
> Project: Spark
> Issue Type: Bug
> Components: Spark Core
>Affects Versions: 2.4.0
>Reporter: Yuri Budilov
>Priority: Major
>
> Following SQL worked in Spark 2.3.2 and now fails on 2.4.0 (AWS EMR Spark)
> PySpark call below:
> spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
> from datalake_reporting.copy_of_leads_notification \
> where partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification) \
> and partition_month_utc = \
> (select max(partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m \
> where \
> m.partition_year_utc = (select max(partition_year_utc) from
> datalake_reporting.copy_of_leads_notification)) \
> and partition_day_utc = (select max(d.partition_day_utc) from
> datalake_reporting.copy_of_leads_notification as d \
> where d.partition_month_utc = \
> (select max(m1.partition_month_utc) from
> datalake_reporting.copy_of_leads_notification as m1 \
> where m1.partition_year_utc = \
> (select max(y.partition_year_utc) from
> datalake_reporting.copy_of_leads_notification as y) \
> ) \
> ) \
> order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
> Error: (no need for data, this is syntax).
> py4j.protocol.Py4JJavaError: An error occurred while calling o1326.showString.
> : java.lang.UnsupportedOperationException: Cannot evaluate expression:
> scalar-subquery#4495 []
>
> Note: all 3 columns in query are Partitioned columns - see bottom of the
> schema)
>
> Hive EMR AWS Schema is:
>
> CREATE EXTERNAL TABLE `copy_of_leads_notification`(
> `message.environment.siteorigin` string, `dcpheader.dcploaddateutc` string,
> `message.id` int, `source.properties._country` string, `message.created`
> string, `dcpheader.generatedmessageid` string, `message.tags` bigint,
> `source.properties._enqueuedtimeutc` string, `source.properties._leadtype`
> string, `message.itemid` string, `message.prospect.postcode` string,
> `message.prospect.email` string, `message.referenceid` string,
> `message.item.year` string, `message.identifier` string,
> `dcpheader.dcploadmonthutc` string, `message.processed` string,
> `source.properties._tenant` string, `message.item.price` string,
> `message.subscription.confirmresponse` boolean, `message.itemtype` string,
> `message.prospect.lastname` string, `message.subscription.insurancequote`
> boolean, `source.exchangename` string,
> `message.prospect.identificationnumbers` bigint,
> `message.environment.ipaddress` string, `dcpheader.dcploaddayutc` string,
> `source.properties._itemtype` string, `source.properties._requesttype`
> string, `message.item.make` string, `message.prospect.firstname` string,
> `message.subscription.survey` boolean, `message.prospect.homephone` string,
> `message.extendedproperties` bigint, `message.subscription.financequote`
> boolean, `message.uniqueidentifier` string, `source.properties._id` string,
> `dcpheader.sourcemessageguid` string, `message.requesttype` string,
> `source.routingkey` string, `message.service` string, `message.item.model`
> string, `message.environment.pagesource` string, `source.source` string,
> `message.sellerid` string, `partition_date_utc` string,
> `message.selleridentifier` string, `message.subscription.newsletter` boolean,
> `dcpheader.dcploadyearutc` string, `message.leadtype` string,
> `message.history` bigint, `message.callconnect.calloutcome` string,
> `message.callconnect.datecreatedutc` string,
> `message.callconnect.callrecordingurl` string,
> `message.callconnect.transferoutcome` string,
> `message.callconnect.hiderecording` boolean,
> `message.callconnect.callstartutc` string, `message.callconnect.code` string,
> `message.callconnect.callduration` string, `message.fraudnetinfo` string,
> `message.callconnect.answernumber` string, `message.environment.sourcedevice`
> string, `message.comments` string, `message.fraudinfo.servervariables`
> bigint, `message.callconnect.servicenumber` string,
> `message.callconnect.callid` string, `message.callconnect.voicemailurl`
> string, `message.item.stocknumber` string,
> `message.callconnect.answerduration` string, `message.callconnect.callendutc`
> string, `message.item
