[ https://issues.apache.org/jira/browse/SPARK-30332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17042851#comment-17042851 ]
Izek Greenfield commented on SPARK-30332: ----------------------------------------- Code to reproduce the problem: {code:scala} import java.nio.file.{Files, Paths} import org.apache.spark.sql.SparkSession object Test { def main(args: Array[String]): Unit = { val spark = { SparkSession .builder() .master("local[*]") .config("spark.serializer", "org.apache.spark.serializer.KryoSerializer") .config("spark.sql.cbo.enabled", "true") .config("spark.scheduler.mode", "FAIR") .config("spark.sql.crossJoin.enabled", "true") .config("spark.sql.adaptive.enabled", "true") .config("spark.sql.parquet.filterPushdown", "true") .config("spark.sql.shuffle.partitions", "500") .config("spark.executor.heartbeatInterval", "600s") .config("spark.network.timeout", "1200s") .config("spark.sql.broadcastTimeout", "1200s") .config("spark.shuffle.file.buffer", "64k") .appName("error") .enableHiveSupport() .getOrCreate() } val pathToCsvFiles = "db" import scala.collection.JavaConverters._ Files.walk(Paths.get(pathToCsvFiles)).iterator().asScala.map(_.toFile).foreach{ file => if (!file.isDirectory){ val name = file.getName spark.read.format("csv") .option("inferSchema", "true") .option("header", "true") .option("mode", "DROPMALFORMED") .load(file.getAbsolutePath) .createOrReplaceGlobalTempView(name.split("\\.").head) } } spark.sql( """ |SELECT BT_capital.asof_date, |BT_capital.run_id, |BT_capital.v, |BT_capital.id, |BT_capital.entity, |BT_capital.level_1, |BT_capital.level_2, |BT_capital.level_3, |BT_capital.level_4, |BT_capital.level_5, |BT_capital.level_6, |BT_capital.path_bt_capital, |BT_capital.line_item, |t0.target_line_item, |t0.line_description, |BT_capital.col_item, |BT_capital.rep_amount, |root.orgUnitId, |root.cptyId, |root.instId, |root.startDate, |root.maturityDate, |root.amount, |root.nominalAmount, |root.quantity, |root.lkupAssetLiability, |root.lkupCurrency, |root.lkupProdType, |root.interestResetDate, |root.interestResetTerm, |root.noticePeriod, |root.historicCostAmount, |root.dueDate, |root.lkupResidence, |root.lkupCountryOfUltimateRisk, |root.lkupSector, |root.lkupIndustry, |root.lkupAccountingPortfolioType, |root.lkupLoanDepositTerm, |root.lkupFixedFloating, |root.lkupCollateralType, |root.lkupRiskType, |root.lkupEligibleRefinancing, |root.lkupHedging, |root.lkupIsOwnIssued, |root.lkupIsSubordinated, |root.lkupIsQuoted, |root.lkupIsSecuritised, |root.lkupIsSecuritisedServiced, |root.lkupIsSyndicated, |root.lkupIsDeRecognised, |root.lkupIsRenegotiated, |root.lkupIsTransferable, |root.lkupIsNewBusiness, |root.lkupIsFiduciary, |root.lkupIsNonPerforming, |root.lkupIsInterGroup, |root.lkupIsIntraGroup, |root.lkupIsRediscounted, |root.lkupIsCollateral, |root.lkupIsExercised, |root.lkupIsImpaired, |root.facilityId, |root.lkupIsOTC, |root.lkupIsDefaulted, |root.lkupIsSavingsPosition, |root.lkupIsForborne, |root.lkupIsDebtRestructuringLoan, |root.interestRateAAR, |root.interestRateAPRC, |root.custom1, |root.custom2, |root.custom3, |root.lkupSecuritisationType, |root.lkupIsCashPooling, |root.lkupIsEquityParticipationGTE10, |root.lkupIsConvertible, |root.lkupEconomicHedge, |root.lkupIsNonCurrHeldForSale, |root.lkupIsEmbeddedDerivative, |root.lkupLoanPurpose, |root.lkupRegulated, |root.lkupRepaymentType, |root.glAccount, |root.lkupIsRecourse, |root.lkupIsNotFullyGuaranteed, |root.lkupImpairmentStage, |root.lkupIsEntireAmountWrittenOff, |root.lkupIsLowCreditRisk, |root.lkupIsOBSWithinIFRS9, |root.lkupIsUnderSpecialSurveillance, |root.lkupProtection, |root.lkupIsGeneralAllowance, |root.lkupSectorUltimateRisk, |root.cptyOrgUnitId, |root.name, |root.lkupNationality, |root.lkupSize, |root.lkupIsSPV, |root.lkupIsCentralCounterparty, |root.lkupIsMMRMFI, |root.lkupIsKeyManagement, |root.lkupIsOtherRelatedParty, |root.lkupResidenceProvince, |root.lkupIsTradingBook, |root.entityHierarchy_entityId, |root.entityHierarchy_Residence, |root.lkupLocalCurrency, |root.cpty_entityhierarchy_entityId, |root.lkupRelationship, |root.cpty_lkupRelationship, |root.entityNationality, |root.lkupRepCurrency, |root.startDateFinancialYear, |root.numEmployees, |root.numEmployeesTotal, |root.collateralAmount, |root.guaranteeAmount, |root.impairmentSpecificIndividual, |root.impairmentSpecificCollective, |root.impairmentGeneral, |root.creditRiskAmount, |root.provisionSpecificIndividual, |root.provisionSpecificCollective, |root.provisionGeneral, |root.writeOffAmount, |root.interest, |root.fairValueAmount, |root.grossCarryingAmount, |root.carryingAmount, |root.code, |root.lkupInstrumentType, |root.price, |root.amountAtIssue, |root.yield, |root.totalFacilityAmount, |root.facility_rate, |root.spec_indiv_est, |root.spec_coll_est, |root.coll_inc_loss, |root.impairment_amount, |root.provision_amount, |root.accumulated_impairment, |root.exclusionFlag, |root.lkupIsHoldingCompany, |root.instrument_startDate, |root.entityResidence, |fxRate.enumerator, |fxRate.lkupFromCurrency, |fxRate.rate, |fxRate.custom1, |fxRate.custom2, |fxRate.custom3, |GB_position.lkupIsECGDGuaranteed, |GB_position.lkupIsMultiAcctOffsetMortgage, |GB_position.lkupIsIndexLinked, |GB_position.lkupIsRetail, |GB_position.lkupCollateralLocation, |GB_position.percentAboveBBR, |GB_position.lkupIsMoreInArrears, |GB_position.lkupIsArrearsCapitalised, |GB_position.lkupCollateralPossession, |GB_position.lkupIsLifetimeMortgage, |GB_position.lkupLoanConcessionType, |GB_position.lkupIsMultiCurrency, |GB_position.lkupIsJointIncomeBasis, |GB_position.ratioIncomeMultiple, |GB_position.interestRate, |GB_position.exclusionFlag, |GB_position.lkupFDIDirection, |GB_position.lkupIsRTGS, |GB_positionExtended.nonRecourseFinanceAmount, |GB_positionExtended.arrearsAmount, |GB_Counterparty.lkupIsClearingFirm, |GB_Counterparty.lkupIsIntermediateFinCorp, |GB_Counterparty.lkupIsImpairedCreditHistory, |GB_Counterparty.lkupFDIRelationship FROM portfolio_41446 BT_capital |JOIN aggr_41390 root ON (root.id = BT_capital.id AND root.entity = BT_capital.entity AND (root.instance_id = 'e3b82807-9371-44f4-9c97-d63cde21dbf2')) |JOIN aggr_41380 fxRate ON (fxRate.lkupToCurrency = root.lkupRepCurrency AND (root.lkupCurrency = fxRate.lkupFromCurrency) AND (fxRate.instance_id = '641f3e44-8d0a-4040-8f26-1d459c5a47a3')) |LEFT OUTER JOIN aggr_41418 GB_position ON (GB_position.id = root.id AND (GB_position.instance_id = 'a8eb71eb-906b-4b76-a977-589618f5f1b5')) |LEFT OUTER JOIN aggr_41406 GB_positionExtended ON (GB_positionExtended.id = GB_position.id AND (GB_positionExtended.instance_id = 'bc7415ca-8cda-4c64-a323-fdfe72b00d54')) |LEFT OUTER JOIN aggr_41410 GB_Counterparty ON (GB_Counterparty.id = root.cptyId AND (GB_Counterparty.instance_id = '6e0d1229-c142-4411-81e2-c1cc5f636843')) |LEFT OUTER JOIN t_41233 t0 ON (t0.line_item = BT_capital.line_item) |WHERE ((BT_capital.instance_id = '4fa9a3a5-67e2-4c78-9708-76849d424123')) AND (root.exclusionFlag!='yes' |) AND (GB_position.exclusionFlag!='yes') |AND |((((NVL(t0.target_line_item,BT_capital.line_item) = '10') AND |(BT_capital.col_item = 'Euro') AND |(BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '10') AND |(BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '10') AND |(BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '11A') AND (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '11A') AND (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '11A') AND (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '11B') AND (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '11B') AND (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '11B') AND (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19A') AND (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19A') AND (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19A') AND (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19B') AND (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19B') AND (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19B') AND (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19BL') AND (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19BL') AND (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19BL') AND (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19CCA') AND (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19CCA') AND (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) OR |((NVL(t0.target_line_item,BT_capital.line_item) = '19CCA') AND (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002'))) AND (BT_capital.level_1 != 'Orphans')) limit 101 |""".stripMargin) } } {code} > When running sql query with limit catalyst throw StackOverFlow exception > ------------------------------------------------------------------------- > > Key: SPARK-30332 > URL: https://issues.apache.org/jira/browse/SPARK-30332 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 3.0.0 > Environment: spark version 3.0.0-preview > Reporter: Izek Greenfield > Priority: Major > Attachments: AGGR_41380.csv, AGGR_41390.csv, AGGR_41406.csv, > AGGR_41406.csv, AGGR_41410.csv, AGGR_41418.csv, PORTFOLIO_41446.csv, > T_41233.csv > > > Running that SQL: > {code:sql} > SELECT BT_capital.asof_date, > BT_capital.run_id, > BT_capital.v, > BT_capital.id, > BT_capital.entity, > BT_capital.level_1, > BT_capital.level_2, > BT_capital.level_3, > BT_capital.level_4, > BT_capital.level_5, > BT_capital.level_6, > BT_capital.path_bt_capital, > BT_capital.line_item, > t0.target_line_item, > t0.line_description, > BT_capital.col_item, > BT_capital.rep_amount, > root.orgUnitId, > root.cptyId, > root.instId, > root.startDate, > root.maturityDate, > root.amount, > root.nominalAmount, > root.quantity, > root.lkupAssetLiability, > root.lkupCurrency, > root.lkupProdType, > root.interestResetDate, > root.interestResetTerm, > root.noticePeriod, > root.historicCostAmount, > root.dueDate, > root.lkupResidence, > root.lkupCountryOfUltimateRisk, > root.lkupSector, > root.lkupIndustry, > root.lkupAccountingPortfolioType, > root.lkupLoanDepositTerm, > root.lkupFixedFloating, > root.lkupCollateralType, > root.lkupRiskType, > root.lkupEligibleRefinancing, > root.lkupHedging, > root.lkupIsOwnIssued, > root.lkupIsSubordinated, > root.lkupIsQuoted, > root.lkupIsSecuritised, > root.lkupIsSecuritisedServiced, > root.lkupIsSyndicated, > root.lkupIsDeRecognised, > root.lkupIsRenegotiated, > root.lkupIsTransferable, > root.lkupIsNewBusiness, > root.lkupIsFiduciary, > root.lkupIsNonPerforming, > root.lkupIsInterGroup, > root.lkupIsIntraGroup, > root.lkupIsRediscounted, > root.lkupIsCollateral, > root.lkupIsExercised, > root.lkupIsImpaired, > root.facilityId, > root.lkupIsOTC, > root.lkupIsDefaulted, > root.lkupIsSavingsPosition, > root.lkupIsForborne, > root.lkupIsDebtRestructuringLoan, > root.interestRateAAR, > root.interestRateAPRC, > root.custom1, > root.custom2, > root.custom3, > root.lkupSecuritisationType, > root.lkupIsCashPooling, > root.lkupIsEquityParticipationGTE10, > root.lkupIsConvertible, > root.lkupEconomicHedge, > root.lkupIsNonCurrHeldForSale, > root.lkupIsEmbeddedDerivative, > root.lkupLoanPurpose, > root.lkupRegulated, > root.lkupRepaymentType, > root.glAccount, > root.lkupIsRecourse, > root.lkupIsNotFullyGuaranteed, > root.lkupImpairmentStage, > root.lkupIsEntireAmountWrittenOff, > root.lkupIsLowCreditRisk, > root.lkupIsOBSWithinIFRS9, > root.lkupIsUnderSpecialSurveillance, > root.lkupProtection, > root.lkupIsGeneralAllowance, > root.lkupSectorUltimateRisk, > root.cptyOrgUnitId, > root.name, > root.lkupNationality, > root.lkupSize, > root.lkupIsSPV, > root.lkupIsCentralCounterparty, > root.lkupIsMMRMFI, > root.lkupIsKeyManagement, > root.lkupIsOtherRelatedParty, > root.lkupResidenceProvince, > root.lkupIsTradingBook, > root.entityHierarchy_entityId, > root.entityHierarchy_Residence, > root.lkupLocalCurrency, > root.cpty_entityhierarchy_entityId, > root.lkupRelationship, > root.cpty_lkupRelationship, > root.entityNationality, > root.lkupRepCurrency, > root.startDateFinancialYear, > root.numEmployees, > root.numEmployeesTotal, > root.collateralAmount, > root.guaranteeAmount, > root.impairmentSpecificIndividual, > root.impairmentSpecificCollective, > root.impairmentGeneral, > root.creditRiskAmount, > root.provisionSpecificIndividual, > root.provisionSpecificCollective, > root.provisionGeneral, > root.writeOffAmount, > root.interest, > root.fairValueAmount, > root.grossCarryingAmount, > root.carryingAmount, > root.code, > root.lkupInstrumentType, > root.price, > root.amountAtIssue, > root.yield, > root.totalFacilityAmount, > root.facility_rate, > root.spec_indiv_est, > root.spec_coll_est, > root.coll_inc_loss, > root.impairment_amount, > root.provision_amount, > root.accumulated_impairment, > root.exclusionFlag, > root.lkupIsHoldingCompany, > root.instrument_startDate, > root.entityResidence, > fxRate.enumerator, > fxRate.lkupFromCurrency, > fxRate.rate, > fxRate.custom1, > fxRate.custom2, > fxRate.custom3, > GB_position.lkupIsECGDGuaranteed, > GB_position.lkupIsMultiAcctOffsetMortgage, > GB_position.lkupIsIndexLinked, > GB_position.lkupIsRetail, > GB_position.lkupCollateralLocation, > GB_position.percentAboveBBR, > GB_position.lkupIsMoreInArrears, > GB_position.lkupIsArrearsCapitalised, > GB_position.lkupCollateralPossession, > GB_position.lkupIsLifetimeMortgage, > GB_position.lkupLoanConcessionType, > GB_position.lkupIsMultiCurrency, > GB_position.lkupIsJointIncomeBasis, > GB_position.ratioIncomeMultiple, > GB_position.interestRate, > GB_position.exclusionFlag, > GB_position.lkupFDIDirection, > GB_position.lkupIsRTGS, > GB_positionExtended.nonRecourseFinanceAmount, > GB_positionExtended.arrearsAmount, > GB_Counterparty.lkupIsClearingFirm, > GB_Counterparty.lkupIsIntermediateFinCorp, > GB_Counterparty.lkupIsImpairedCreditHistory, > GB_Counterparty.lkupFDIRelationship FROM portfolio_41446 BT_capital > JOIN aggr_41390 root ON (root.id = BT_capital.id AND root.entity = > BT_capital.entity AND (root.instance_id = > 'e3b82807-9371-44f4-9c97-d63cde21dbf2')) > JOIN aggr_41380 fxRate ON (fxRate.lkupToCurrency = root.lkupRepCurrency AND > (root.lkupCurrency = fxRate.lkupFromCurrency) AND (fxRate.instance_id = > '641f3e44-8d0a-4040-8f26-1d459c5a47a3')) > LEFT OUTER JOIN aggr_41418 GB_position ON (GB_position.id = root.id AND > (GB_position.instance_id = 'a8eb71eb-906b-4b76-a977-589618f5f1b5')) > LEFT OUTER JOIN aggr_41406 GB_positionExtended ON (GB_positionExtended.id = > GB_position.id AND (GB_positionExtended.instance_id = > 'bc7415ca-8cda-4c64-a323-fdfe72b00d54')) > LEFT OUTER JOIN aggr_41410 GB_Counterparty ON (GB_Counterparty.id = > root.cptyId AND (GB_Counterparty.instance_id = > '6e0d1229-c142-4411-81e2-c1cc5f636843')) > LEFT OUTER JOIN t_41233 t0 ON (t0.line_item = BT_capital.line_item) > WHERE ((BT_capital.instance_id = '4fa9a3a5-67e2-4c78-9708-76849d424123')) AND > (root.exclusionFlag!='yes' > ) AND (GB_position.exclusionFlag!='yes') > AND > ((((NVL(t0.target_line_item,BT_capital.line_item) = '10') AND > (BT_capital.col_item = 'Euro') AND > (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '10') AND > (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) > OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '10') AND > (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '11A') AND > (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '11A') AND > (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) > OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '11A') AND > (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '11B') AND > (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '11B') AND > (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) > OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '11B') AND > (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19A') AND > (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19A') AND > (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) > OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19A') AND > (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19B') AND > (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19B') AND > (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) > OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19B') AND > (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19BL') AND > (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19BL') AND > (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) > OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19BL') AND > (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19CCA') AND > (BT_capital.col_item = 'Euro') AND (BT_capital.entity = 'E002')) OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19CCA') AND > (BT_capital.col_item = 'Other currencies') AND (BT_capital.entity = 'E002')) > OR > ((NVL(t0.target_line_item,BT_capital.line_item) = '19CCA') AND > (BT_capital.col_item = 'Sterling') AND (BT_capital.entity = 'E002'))) AND > (BT_capital.level_1 != 'Orphans')) limit 101 > {code} > I get a StackOverflow exception. remove the LIMIT at the end and all is > working. > with spark version 2.4.4 it does not happen -- This message was sent by Atlassian Jira (v8.3.4#803005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org