-- Migrated from DuckDB test: test/sql/join/ expression join tests
-- Tests joins with complex expressions
CREATE TABLE measurements(measure_id INTEGER, sensor_id INTEGER, reading DOUBLE, measure_time TIMESTAMP, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE sensor_config(sensor_id INTEGER, min_threshold DOUBLE, max_threshold DOUBLE, calibration_factor DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO measurements VALUES
(1, 1, 22.5, '2023-01-01 10:00:00', 1000), (2, 1, 45.8, '2023-01-01 10:05:00', 2000),
(3, 2, 18.2, '2023-01-01 10:00:00', 3000), (4, 2, 89.7, '2023-01-01 10:05:00', 4000),
(5, 3, 35.1, '2023-01-01 10:00:00', 5000), (6, 3, 42.3, '2023-01-01 10:05:00', 6000);

Affected Rows: 6

INSERT INTO sensor_config VALUES
(1, 20.0, 50.0, 1.05, 1000), (2, 15.0, 85.0, 0.98, 2000), (3, 30.0, 70.0, 1.02, 3000);

Affected Rows: 3

-- Join with mathematical expressions
SELECT
  m.measure_id,
  m.reading,
  m.reading * sc.calibration_factor as calibrated_reading,
  sc.min_threshold,
  sc.max_threshold
FROM measurements m
INNER JOIN sensor_config sc ON m.sensor_id = sc.sensor_id
ORDER BY m.measure_id;

+------------+---------+--------------------+---------------+---------------+
| measure_id | reading | calibrated_reading | min_threshold | max_threshold |
+------------+---------+--------------------+---------------+---------------+
| 1          | 22.5    | 23.625             | 20.0          | 50.0          |
| 2          | 45.8    | 48.089999999999996 | 20.0          | 50.0          |
| 3          | 18.2    | 17.836             | 15.0          | 85.0          |
| 4          | 89.7    | 87.906             | 15.0          | 85.0          |
| 5          | 35.1    | 35.802             | 30.0          | 70.0          |
| 6          | 42.3    | 43.146             | 30.0          | 70.0          |
+------------+---------+--------------------+---------------+---------------+

-- Join with conditional expressions
SELECT
  m.measure_id,
  m.reading,
  sc.calibration_factor,
  CASE
    WHEN m.reading * sc.calibration_factor < sc.min_threshold THEN 'Below Range'
    WHEN m.reading * sc.calibration_factor > sc.max_threshold THEN 'Above Range'
    ELSE 'In Range'
  END as reading_status
FROM measurements m
INNER JOIN sensor_config sc
  ON m.sensor_id = sc.sensor_id
  AND m.reading BETWEEN sc.min_threshold * 0.5 AND sc.max_threshold * 1.5
ORDER BY m.measure_id;

+------------+---------+--------------------+----------------+
| measure_id | reading | calibration_factor | reading_status |
+------------+---------+--------------------+----------------+
| 1          | 22.5    | 1.05               | In Range       |
| 2          | 45.8    | 1.05               | In Range       |
| 3          | 18.2    | 0.98               | In Range       |
| 4          | 89.7    | 0.98               | Above Range    |
| 5          | 35.1    | 1.02               | In Range       |
| 6          | 42.3    | 1.02               | In Range       |
+------------+---------+--------------------+----------------+

-- Join with aggregated expressions
SELECT
  sc.sensor_id,
  COUNT(*) as total_readings,
  AVG(m.reading * sc.calibration_factor) as avg_calibrated,
  COUNT(CASE WHEN m.reading * sc.calibration_factor > sc.max_threshold THEN 1 END) as over_threshold_count
FROM measurements m
INNER JOIN sensor_config sc ON m.sensor_id = sc.sensor_id
GROUP BY sc.sensor_id, sc.calibration_factor, sc.max_threshold
HAVING AVG(m.reading * sc.calibration_factor) > 30.0
ORDER BY avg_calibrated DESC;

+-----------+----------------+--------------------+----------------------+
| sensor_id | total_readings | avg_calibrated     | over_threshold_count |
+-----------+----------------+--------------------+----------------------+
| 2         | 2              | 52.871             | 1                    |
| 3         | 2              | 39.474000000000004 | 0                    |
| 1         | 2              | 35.8575            | 0                    |
+-----------+----------------+--------------------+----------------------+

-- Join with string expression conditions
CREATE TABLE devices(device_id INTEGER, device_code VARCHAR, "status" VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE device_logs(log_id INTEGER, device_code VARCHAR, log_message VARCHAR, severity INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO devices VALUES
(1, 'DEV001', 'active', 1000), (2, 'DEV002', 'inactive', 2000), (3, 'DEV003', 'active', 3000);

Affected Rows: 3

INSERT INTO device_logs VALUES
(1, 'DEV001', 'System started', 1, 1000), (2, 'DEV001', 'Warning detected', 2, 2000),
(3, 'DEV002', 'Error occurred', 3, 3000), (4, 'DEV003', 'Normal operation', 1, 4000);

Affected Rows: 4

-- Join with string expression matching
SELECT
  d.device_id,
  d."status",
  dl.log_message,
  dl.severity
FROM devices d
INNER JOIN device_logs dl
  ON UPPER(d.device_code) = UPPER(dl.device_code)
  AND d."status" = 'active'
ORDER BY d.device_id, dl.severity DESC;

+-----------+--------+------------------+----------+
| device_id | status | log_message      | severity |
+-----------+--------+------------------+----------+
| 1         | active | Warning detected | 2        |
| 1         | active | System started   | 1        |
| 3         | active | Normal operation | 1        |
+-----------+--------+------------------+----------+

DROP TABLE measurements;

Affected Rows: 0

DROP TABLE sensor_config;

Affected Rows: 0

DROP TABLE devices;

Affected Rows: 0

DROP TABLE device_logs;

Affected Rows: 0

