Bug #113188 LEFT JOIN returns results that do not exist in table

Submitted: 22 Nov 2023 17:51 Modified: 21 Dec 2023 10:18
Reporter: Anton Grigorev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.2.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: left join

[22 Nov 2023 17:51] Anton Grigorev 
Description:
Hello,

Sorry for huge tables, but I couldn't reproduce this bag on smaller tables.

We create four tables: ARRegiter, ARTran, Note and PreferencesGeneral and then insert one row into each of them expect ARTran, so ARTran contains no data. But query

SELECT `ARTran`.*
FROM `ARRegister` `ARInvoice_ARRegister`
LEFT JOIN `ARTran` `ARTran` ON `ARTran`.`CompanyID` = 2 AND `ARInvoice_ARRegister`.`RefNbr` = `ARTran`.`RefNbr`
LEFT JOIN `Note` `InvoiceNote` ON `InvoiceNote`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`InvoiceNote`.`CompanyMask`, 1, 1)) & 8 AND `ARInvoice_ARRegister`.`NoteID` = `InvoiceNote`.`NoteID`
INNER JOIN `PreferencesGeneral` `PreferencesGeneral` ON `PreferencesGeneral`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`PreferencesGeneral`.`CompanyMask`, 1, 1)) & 8
WHERE `ARInvoice_ARRegister`.`DocType` = 'INV' AND `ARInvoice_ARRegister`.`RefNbr` = 'AR004767' AND `ARInvoice_ARRegister`.`CompanyID` = 2

returns non-null results in ARTran's fields.

If we remove JOIN of Note or PreferencesGeneral table, everything is OK, ARTran's fields are all nulls.

I couldn't find workaround for this problem.

How to repeat:
CREATE DATABASE  IF NOT EXISTS `ETest2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ETest2`;

CREATE TABLE `ARRegister` (
  `CompanyID` int NOT NULL DEFAULT '0',
  `BranchID` int NOT NULL,
  `DocType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `RefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `BatchNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `BatchSeq` smallint DEFAULT NULL,
  `CustomerID` int NOT NULL,
  `CustomerLocationID` int NOT NULL,
  `ARAccountID` int NOT NULL,
  `ARSubID` int NOT NULL,
  `PrepaymentAccountID` int DEFAULT NULL,
  `PrepaymentSubID` int DEFAULT NULL,
  `DocDate` datetime NOT NULL COMMENT 'sy_smalldate;',
  `OrigDocDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `DueDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `DocDesc` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CuryInfoID` bigint NOT NULL,
  `CuryOrigDocAmt` decimal(19,4) NOT NULL,
  `OrigDocAmt` decimal(19,4) NOT NULL,
  `CuryDocBal` decimal(19,4) NOT NULL,
  `DocBal` decimal(19,4) NOT NULL,
  `CuryInitDocBal` decimal(19,4) NOT NULL,
  `InitDocBal` decimal(19,4) NOT NULL,
  `CuryDiscBal` decimal(19,4) NOT NULL,
  `DiscBal` decimal(19,4) NOT NULL,
  `CuryDiscTaken` decimal(19,4) NOT NULL,
  `DiscTaken` decimal(19,4) NOT NULL,
  `CuryChargeAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `ChargeAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `OpenDoc` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `Released` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `Hold` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `DisableAutomaticTaxCalculation` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
  `TaxCalcMode` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `IsTaxValid` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsTaxSaved` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsTaxPosted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `NonTaxable` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `Status` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `LineCntr` int NOT NULL,
  `AdjCntr` int NOT NULL,
  `DRSchedCntr` int DEFAULT NULL,
  `CuryID` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `RGOLAmt` decimal(19,4) NOT NULL,
  `CuryRoundDiff` decimal(19,4) NOT NULL,
  `RoundDiff` decimal(19,4) NOT NULL,
  `ScheduleID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ImpRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `StatementDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `tstamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `CreatedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `CreatedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `CreatedDateTime` datetime(6) NOT NULL,
  `LastModifiedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `LastModifiedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `LastModifiedDateTime` datetime(6) NOT NULL,
  `Voided` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `Scheduled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `PendingProcessing` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `CuryOrigDiscAmt` decimal(19,4) NOT NULL,
  `OrigDiscAmt` decimal(19,4) NOT NULL,
  `FinPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `TranPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `ClosedTranPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `ClosedFinPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `ClosedDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `SalesPersonID` int DEFAULT NULL,
  `DeletedDatabaseRecord` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `OrigModule` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `OrigDocType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `OrigRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `HasPPDTaxes` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `PendingPPD` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsMigratedRecord` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `PaymentsByLinesAllowed` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `ApproverID` int DEFAULT NULL,
  `ApproverWorkGroupID` int DEFAULT NULL,
  `Approved` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `Rejected` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `DontApprove` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
  `RetainageAcctID` int DEFAULT NULL,
  `RetainageSubID` int DEFAULT NULL,
  `RetainageApply` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsRetainageDocument` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsRetainageReversing` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `DefRetainagePct` decimal(9,6) NOT NULL DEFAULT '0.000000',
  `CuryLineRetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `LineRetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainageReleased` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainageReleased` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainageUnreleasedAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainageUnreleasedAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainageUnpaidTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainageUnpaidTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainedTaxTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainedTaxTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainedDiscTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainedDiscTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `IsCancellation` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsCorrection` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsUnderCorrection` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `Canceled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `PendingPayment` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `ExternalRef` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `RefNoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
  PRIMARY KEY (`CompanyID`,`DocType`,`RefNbr`),
  UNIQUE KEY `ARRegister_NoteID` (`CompanyID`,`NoteID`),
  KEY `ARRegister_CompanyID_CuryID_CuryDocBal` (`CompanyID`,`CuryID`,`CuryDocBal`),
  KEY `ARRegister_Correction` (`CompanyID`,`IsCorrection`,`DocType`,`RefNbr`),
  KEY `ARRegister_CreatedByID_CreatedDateTime` (`CreatedByID`,`CreatedDateTime`),
  KEY `ARRegister_CreatedDateTime` (`CreatedDateTime`),
  KEY `ARRegister_CustomerID_OpenDoc_Released_DueDate` (`CompanyID`,`CustomerID`,`Released`,`OpenDoc`,`DueDate`),
  KEY `ARRegister_ExternalRefNbr` (`CompanyID`,`ExternalRef`),
  KEY `ARRegister_N1` (`DocDate`,`CustomerID`),
  KEY `ARRegister_Origin` (`CompanyID`,`BranchID`,`OrigDocType`,`OrigRefNbr`,`Released`,`DeletedDatabaseRecord`),
  KEY `ARRegister_OrigRefNbr` (`OrigRefNbr`,`OrigDocType`),
  KEY `ARRegister_ScheduleID_Index` (`CompanyID`,`ScheduleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ARTran` (
  `CompanyID` int NOT NULL DEFAULT '0',
  `BranchID` int NOT NULL,
  `TranType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `RefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `LineNbr` int NOT NULL,
  `SortOrder` int DEFAULT NULL,
  `AccountID` int NOT NULL,
  `SubID` int NOT NULL,
  `ExpenseAccrualAccountID` int DEFAULT NULL,
  `ExpenseAccrualSubID` int DEFAULT NULL,
  `ExpenseAccountID` int DEFAULT NULL,
  `ExpenseSubID` int DEFAULT NULL,
  `AccrueCost` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `CostBasis` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `CuryInfoID` bigint NOT NULL,
  `CuryTranAmt` decimal(19,4) DEFAULT NULL,
  `TranAmt` decimal(19,4) DEFAULT NULL,
  `CuryCashDiscBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CashDiscBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryOrigRetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `OrigRetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainageBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainageBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryOrigTranAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `OrigTranAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryTranBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `TranBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryTaxableAmt` decimal(19,4) DEFAULT NULL,
  `TaxableAmt` decimal(19,4) DEFAULT NULL,
  `CuryTaxAmt` decimal(19,4) DEFAULT NULL,
  `TaxAmt` decimal(19,4) DEFAULT NULL,
  `CuryOrigTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `OrigTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryOrigTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `OrigTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainedTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainedTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRetainedTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainedTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `Qty` decimal(25,6) DEFAULT NULL,
  `BaseQty` decimal(25,6) DEFAULT NULL,
  `CuryAccruedCost` decimal(19,6) NOT NULL DEFAULT '0.000000',
  `AccruedCost` decimal(19,6) NOT NULL DEFAULT '0.000000',
  `TranCost` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `TranCostOrig` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `IsTranCostFinal` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `CuryExtPrice` decimal(19,6) DEFAULT NULL,
  `ExtPrice` decimal(19,6) DEFAULT NULL,
  `CuryLineAmt` decimal(19,6) DEFAULT NULL,
  `LineAmt` decimal(19,6) DEFAULT NULL,
  `DiscountsAppliedToLine` longblob,
  `OrigLineNbr` int DEFAULT NULL,
  `OrigGroupDiscountRate` decimal(21,18) NOT NULL,
  `OrigDocumentDiscountRate` decimal(21,18) NOT NULL,
  `GroupDiscountRate` decimal(21,18) DEFAULT NULL,
  `DocumentDiscountRate` decimal(21,18) NOT NULL,
  `TranClass` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `DrCr` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `TranDate` datetime NOT NULL COMMENT 'sy_smalldate;',
  `OrigInvoiceDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `TranDesc` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Released` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `InventoryID` int DEFAULT NULL,
  `SiteID` int DEFAULT NULL,
  `ReasonCode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UOM` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LineType` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `TaxCategoryID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `AvalaraCustomerUsageType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `tstamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `CreatedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `CreatedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `CreatedDateTime` datetime(6) NOT NULL,
  `LastModifiedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `LastModifiedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `LastModifiedDateTime` datetime(6) NOT NULL,
  `CuryUnitPrice` decimal(19,6) NOT NULL DEFAULT '0.000000',
  `UnitPrice` decimal(19,6) NOT NULL DEFAULT '0.000000',
  `SalesPersonID` int DEFAULT NULL,
  `EmployeeID` int DEFAULT NULL,
  `CommnPct` decimal(9,6) NOT NULL DEFAULT '0.000000',
  `CommnAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryCommnAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `Commissionable` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `FinPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `TranPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `DeferredCode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ScheduleID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `DefScheduleID` int DEFAULT NULL,
  `CustomerID` int NOT NULL,
  `ProjectID` int DEFAULT NULL,
  `TaskID` int DEFAULT NULL,
  `CostCodeID` int DEFAULT NULL,
  `CaseID_obsolete` int DEFAULT NULL,
  `CaseCD` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PMDeltaOption` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Date` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `TaxID` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ExpenseDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `SOOrderType` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `SOOrderNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `SOOrderLineNbr` int DEFAULT NULL,
  `SOOrderLineOperation` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `SOOrderSortOrder` int DEFAULT NULL,
  `SOShipmentType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `SOShipmentNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `SOShipmentLineNbr` int DEFAULT NULL,
  `DiscountID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `DiscountSequenceID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CuryDiscAmt` decimal(19,4) DEFAULT NULL,
  `DiscPct` decimal(9,6) DEFAULT NULL,
  `DiscAmt` decimal(19,4) DEFAULT NULL,
  `ManualPrice` tinyint(1) DEFAULT '0' COMMENT 'sy_boolean;',
  `AutomaticDiscountsDisabled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `ManualDisc` tinyint(1) DEFAULT '0' COMMENT 'sy_boolean;',
  `DisableAutomaticTaxCalculation` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `SkipLineDiscounts` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `IsRUTROTDeductible` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `RUTROTWorkType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `RUTROTWorkTypeID` int DEFAULT NULL,
  `RUTROTItemType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `CuryRUTROTAvailableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `CuryRUTROTTaxAmountDeductible` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RUTROTAvailableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RUTROTTaxAmountDeductible` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `DRTermStartDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `DRTermEndDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `CuryUnitPriceDR` decimal(19,6) DEFAULT NULL,
  `DiscPctDR` decimal(9,6) DEFAULT NULL,
  `RetainagePct` decimal(9,6) NOT NULL DEFAULT '0.000000',
  `CuryRetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `RetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `OrigDocType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `OrigRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `SubItemID` int DEFAULT NULL,
  `InvtMult` smallint NOT NULL DEFAULT '0',
  `LocationID` int DEFAULT NULL,
  `LotSerialNbr` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ExpireDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
  `PlanID` bigint DEFAULT NULL,
  `OrigInvoiceType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `OrigInvoiceNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `OrigInvoiceLineNbr` int DEFAULT NULL,
  `InvtDocType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `InvtRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `IsStockItem` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
  `IsCancellation` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `Canceled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `SubstitutionRequired` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `AssociatedOrderLineNbr` int DEFAULT NULL,
  `GiftMessage` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `BlanketType` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `BlanketNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `BlanketLineNbr` int DEFAULT NULL,
  `BlanketSplitLineNbr` int DEFAULT NULL,
  `SOOrderLineSign` smallint DEFAULT NULL,
  PRIMARY KEY (`CompanyID`,`TranType`,`RefNbr`,`LineNbr`),
  UNIQUE KEY `ARTran_NoteID` (`CompanyID`,`NoteID`),
  KEY `ARTran_CuryInfoID` (`CuryInfoID`),
  KEY `ARTran_OrigInvoiceType_OrigInvoiceNbr` (`CompanyID`,`OrigInvoiceType`,`OrigInvoiceNbr`),
  KEY `ARTran_ProjectID` (`CompanyID`,`BranchID`,`ProjectID`,`TaskID`,`AccountID`,`InventoryID`,`CostCodeID`),
  KEY `ARTran_RefNbr_LineType` (`CompanyID`,`RefNbr`,`LineType`),
  KEY `ARTran_RevenueSchedule` (`CompanyID`,`CustomerID`,`ScheduleID`),
  KEY `ARTran_SOLine` (`SOOrderType`,`SOOrderNbr`,`SOOrderLineNbr`),
  KEY `ARTran_SortOrder` (`CompanyID`,`TranType`,`RefNbr`,`SortOrder`,`LineNbr`),
  KEY `ARTran_SOShipLine` (`SOShipmentNbr`,`SOShipmentType`,`SOShipmentLineNbr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `Note` (
  `CompanyID` int NOT NULL DEFAULT '0',
  `oldNoteID` bigint DEFAULT NULL,
  `ExternalKey` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NoteText` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `NotePopupText` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `GraphType` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `EntityType` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `CompanyMask` varbinary(32) NOT NULL DEFAULT 0xAA COMMENT 'df_MaskAA;',
  `NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '00112233-4455-6677-8899-AABBCCDDEEFF' COMMENT 'sy_guid;',
  PRIMARY KEY (`NoteID`,`CompanyID`),
  KEY `ExternalKey_Ind` (`ExternalKey`(191)),
  KEY `Note_REVERSED` (`CompanyID`,`NoteID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `PreferencesGeneral` (
  `CompanyID` int NOT NULL DEFAULT '0',
  `MaxUploadSize` int NOT NULL,
  `MapViewer` int DEFAULT NULL,
  `TimeZone` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `GridActionsText` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `GridFastFilterCondition` int NOT NULL,
  `GridFastFilterMaxLength` int NOT NULL,
  `GetLinkTemplate` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
  `HeaderFont` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `HeaderFontSize` smallint DEFAULT NULL,
  `HeaderFontColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `HeaderFillColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `HeaderFontType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `BodyFont` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `BodyFontSize` smallint DEFAULT NULL,
  `BodyFontColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `BodyFillColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `BodyFontType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `Border` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
  `BorderColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `HiddenSkip` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
  `EditorFontName` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `EditorFontSize` int DEFAULT NULL,
  `SpellCheck` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
  `Theme` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `HomePage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
  `HelpPage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
  `UseMLSearch` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
  `DeletingMLEventsMode` int NOT NULL DEFAULT '0',
  `MLEventsRetentionAge` int NOT NULL,
  `PortalHomePage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
  `DashboardPortalHomePage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
  `PortalExternalAccessLink` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PersonNameFormat` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `CreatedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `CreatedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `CreatedDateTime` datetime(6) NOT NULL,
  `LastModifiedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `LastModifiedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `LastModifiedDateTime` datetime(6) NOT NULL,
  `CompanyMask` varbinary(32) NOT NULL DEFAULT 0xAA COMMENT 'df_MaskAA;',
  `tstamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
  `ApplyToEmptyCells` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
  `AddressLookupPluginID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`CompanyID`),
  UNIQUE KEY `PreferencesGeneral_NoteID` (`CompanyID`,`NoteID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ARRegister` VALUES (2,16,'INV','AR004767','AR002930',0,4986,5029,1154,467,NULL,NULL,'2017-01-05 00:00:00',NULL,'2017-02-04 00:00:00','Contract Billing 38SUPPORTP: Support contract for Westwood Manor.',650577,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0,1,0,NULL,'T',0,0,0,0,'C',0,0,NULL,'USD',0.0000,0.0000,0.0000,NULL,NULL,NULL,'2023-08-10 20:13:00.194153','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','CT501000','2017-01-03 14:06:59.747000','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','AR509900','2017-01-03 14:25:24.257000',0,0,0,0.0000,0.0000,'201701','201701','201701','201701','2017-02-01 00:00:00',38,0,'AR',NULL,NULL,0,0,0,0,NULL,NULL,1,0,NULL,NULL,NULL,0,0,0,0.000000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0,0,0,0,0,'92bbb8cc-e7d1-e611-946d-126b3844335e',NULL,NULL);
INSERT INTO `Note` VALUES (2,NULL,NULL,'',NULL,'PX.Objects.AR.ARInvoiceEntry','PX.Objects.AR.ARInvoice',x'AA','92bbb8cc-e7d1-e611-946d-126b3844335e');
INSERT INTO `PreferencesGeneral` VALUES (2,25000,0,'GMTE0000U',1,6,100,NULL,'Calibri',11,'White','Navy','1','Calibri',11,'Black','Transparent','1',0,'Transparent',1,'Arial',2,0,'Default',NULL,'e66a3027-ff57-46a4-bf51-5cdf7c0723ac',1,0,90,NULL,NULL,NULL,'LEGACY','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','CS209000','2008-01-31 00:00:00.000000','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','SM202550','2015-05-18 17:30:23.810000',x'AA','2022-08-13 08:55:56.378301','5a0c3b2c-d87f-e411-beca-00b56d0561c2',0,NULL);

SELECT `ARTran`.*
FROM `ARRegister` `ARInvoice_ARRegister`
LEFT JOIN `ARTran` `ARTran` ON `ARTran`.`CompanyID` = 2 AND `ARInvoice_ARRegister`.`RefNbr` = `ARTran`.`RefNbr`
LEFT JOIN `Note` `InvoiceNote` ON `InvoiceNote`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`InvoiceNote`.`CompanyMask`, 1, 1)) & 8 AND `ARInvoice_ARRegister`.`NoteID` = `InvoiceNote`.`NoteID`
INNER JOIN `PreferencesGeneral` `PreferencesGeneral` ON `PreferencesGeneral`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`PreferencesGeneral`.`CompanyMask`, 1, 1)) & 8
WHERE `ARInvoice_ARRegister`.`DocType` = 'INV' AND `ARInvoice_ARRegister`.`RefNbr` = 'AR004767' AND `ARInvoice_ARRegister`.`CompanyID` = 2
[23 Nov 2023 9:14] MySQL Verification Team 
Hello Anton Grigorev,

Thank you for the report and test case.

regards,
Umesh
[21 Dec 2023 10:18] Erlend Dahl 
Fixed in the upcoming 8.3.0 release under the heading of

Bug#35789589 Assertion `m_index_cursor.is_positioned()' failed.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.046 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.