-- ============================================================
-- Reproduce SYSDATE() corruption in ColumnStore cross-engine
-- INSERT...SELECT with window functions
-- ============================================================

DROP DATABASE IF EXISTS sysdate_repro;
CREATE DATABASE sysdate_repro;
USE sysdate_repro;

-- Source table (ColumnStore - mimics LTE_SPOT_MEASUREMENTS_DAILY)
CREATE TABLE measurements_cs (
   Latitude DOUBLE,
   Longitude DOUBLE,
   ServingSectorHandle INT,
   CountBest INT,
   CountSeen INT,
   RsrqSeenSum DOUBLE,
   RsrpSeenSum DOUBLE,
   RsrqBestSum DOUBLE,
   RsrpBestSum DOUBLE,
   Date DATE,
   FCN DOUBLE,
   DeviceID INT
) ENGINE=ColumnStore;

-- InnoDB temp table (mimics TEMPSPOTSECTORS_ODD)
CREATE TABLE TempSpotSectors (
   RequestID INT,
   SectorID INT,
   SectLat DOUBLE,
   SectLon DOUBLE,
   Azimuth DOUBLE,
   Beamwidth DOUBLE
) ENGINE=InnoDB;

-- InnoDB temp table (mimics TEMPSPOTBINLOCS_ODD)
CREATE TABLE TempSpotBinLocs (
   RequestID INT,
   BinLon DOUBLE,
   BinLat DOUBLE
) ENGINE=InnoDB;

-- Target table (mimics TEMPSPOTNBRANALYSIS_ODD)
CREATE TABLE TempSpotNbrAnalysis (
   RequestID INT,
   UserID INT,
   CreatedDate DATETIME,
   SectorID INT,
   Latitude DOUBLE,
   Longitude DOUBLE,
   SectLat DOUBLE,
   SectLon DOUBLE,
   Azimuth DOUBLE,
   AdjustedBeamwidth DOUBLE,
   BearingToBin DOUBLE,
   SumM1 DOUBLE,
   M1Samples INT,
   SumM2 DOUBLE,
   M2Samples INT,
   CountSeen INT,
   PrimarySectorID INT,
   PrimaryM1 DOUBLE,
   `Rank` INT
) ENGINE=InnoDB;

-- Insert sample data into ColumnStore source
INSERT INTO measurements_cs VALUES
(51.5074, -0.1278, 62017, 10, 20, -5.5, -85.0, -4.0, -80.0, '2015-07-30', 3350.0, 2),
(51.5075, -0.1279, 62017, 8, 15, -6.0, -87.0, -5.0, -82.0, '2015-07-30', 3350.0, 2),
(51.5074, -0.1278, 61953, 5, 12, -8.0, -90.0, -7.0, -88.0, '2015-07-30', 3350.0, 3),
(51.5075, -0.1279, 61953, 3, 8, -9.0, -92.0, -8.0, -90.0, '2015-07-30', 3350.0, 4),
(51.5076, -0.1280, 62018, 12, 25, -4.0, -82.0, -3.5, -78.0, '2015-07-30', 3350.0, 2),
(51.5076, -0.1280, 61954, 6, 14, -7.5, -89.0, -6.5, -86.0, '2015-07-30', 3350.0, 3);

-- Insert data into InnoDB temp tables
INSERT INTO TempSpotSectors VALUES
(851435695, 62017, 51.5080, -0.1270, 120.0, 65.0),
(851435695, 61953, 51.5060, -0.1290, 240.0, 65.0),
(851435695, 62018, 51.5090, -0.1260, 0.0, 65.0),
(851435695, 61954, 51.5050, -0.1300, 180.0, 65.0);

INSERT INTO TempSpotBinLocs VALUES
(851435695, -0.1278, 51.5074),
(851435695, -0.1279, 51.5075),
(851435695, -0.1280, 51.5076);

-- ============================================================
-- This is the query that triggers the bug.
-- SYSDATE() in position 3 should return current datetime but
-- may produce a corrupted value like '19207-09-20 05:53:58'
-- when ColumnStore materializes an internal temp table for
-- the window functions.
-- ============================================================
INSERT INTO TempSpotNbrAnalysis
SELECT TempSpotSectors.RequestID,
      6,
      SYSDATE(),
      SubQuery1.SectorID,
      Latitude,
      Longitude,
      TempSpotSectors.SectLat,
      TempSpotSectors.SectLon,
      TempSpotSectors.Azimuth,
      TempSpotSectors.Beamwidth + 2 * 90 AS AdjustedBeamwidth,
      MOD(450 - ATAN2(SubQuery1.Latitude - TempSpotSectors.SectLat,
          (SubQuery1.Longitude - TempSpotSectors.SectLon) *
          COS(((TempSpotSectors.SectLat + SubQuery1.Latitude) / 2) * 0.0174532925199432957692369076848861271344))
          / 0.0174532925199432957692369076848861271344, 360) AS BearingToBin,
      rsrqseensum AS SumM1,
      countseen AS M1Samples,
      rsrpseensum AS SumM2,
      countseen AS M2Samples,
      CountSeen,
      FIRST_VALUE(SubQuery1.SectorID) OVER(PARTITION BY Latitude, Longitude
          ORDER BY countbest DESC, rsrqseensum_Over_countseen DESC, rsrpseensum_Over_countseen DESC,
                   countseen DESC) AS PrimarySectorID,
      FIRST_VALUE(AVGrsrqseensum_Over_countseen) OVER(PARTITION BY Latitude, Longitude
          ORDER BY countbest DESC, rsrqseensum_Over_countseen DESC, rsrpseensum_Over_countseen DESC,
                   countseen DESC) AS PrimaryM1,
      ROW_NUMBER() OVER(PARTITION BY Latitude, Longitude
          ORDER BY countbest DESC, rsrqseensum_Over_countseen DESC, rsrpseensum_Over_countseen DESC,
                   countseen DESC) AS `Rank`
FROM
(
      SELECT Latitude,
             Longitude,
             ServingSectorHandle AS SectorID,
             SUM(rsrqseensum) / SUM(countseen) AS AVGrsrqseensum_Over_countseen,
             SUM(countbest) AS countbest,
             SUM(countseen) AS countseen,
             SUM(rsrpseensum) AS rsrpseensum,
             AVG(rsrpseensum / countseen) AS rsrpseensum_Over_countseen,
             SUM(rsrqseensum) AS rsrqseensum,
             AVG(rsrqseensum / countseen) AS rsrqseensum_Over_countseen
      FROM (
          SELECT CountBest, CountSeen, Latitude, Longitude, RsrpBestSum, RsrpSeenSum, RsrqBestSum, RsrqSeenSum, ServingSectorHandle
          FROM measurements_cs s
          JOIN TempSpotBinLocs l ON s.Longitude = l.BinLon AND s.Latitude = l.BinLat AND l.RequestID = 851435695
          WHERE s.Date = DATE('2015-07-30')
            AND FCN = 3350.0
            AND DeviceID IN (2, 4, 3)
      ) Measurements
      GROUP BY Latitude, Longitude, ServingSectorHandle
) SubQuery1
JOIN TempSpotSectors ON SubQuery1.SectorID = TempSpotSectors.SectorID
   AND TempSpotSectors.RequestID = 851435695
   AND SubQuery1.SectorID IN (62017, 61953, 62018, 61954);

-- Check what was inserted - inspect the CreatedDate column
SELECT RequestID, UserID, CreatedDate, SectorID, `Rank`
FROM TempSpotNbrAnalysis;

-- Cleanup
-- DROP DATABASE sysdate_repro;