[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