employee_sample.sql

DROP TABLE IF EXISTS logged_hours;
DROP TABLE IF EXISTS employee_hourly;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255),
    PRIMARY KEY (`id`)
);

INSERT INTO employees (name) VALUES ('Jane'), ('Jeff'), ('Jill'), ('Joe');


CREATE TABLE employee_hourly (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    employee_id int(11),
    hourly_rate_in_dollars int(11),
    PRIMARY KEY (`id`),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

INSERT INTO employee_hourly (employee_id, hourly_rate_in_dollars) VALUES (1, 105), (2, 75), (3, 85), (4, 100);


CREATE TABLE logged_hours (
    id int(11) NOT NULL AUTO_INCREMENT,
    employee_id int(11),
    work_date date,
    logged_date date,
    hours_logged int(11),
    PRIMARY KEY (`id`),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

INSERT INTO logged_hours (employee_id, work_date, logged_date, hours_logged) VALUES 
                         (1, '2000-01-01', '2000-01-02', 4),
                         (2, '2000-01-01', '2000-01-02', 2),
                         (3, '2000-01-07', '2000-01-07', 3),
                         (4, '2000-01-01', '2000-01-01', 2),
                         (4, '2000-01-01', '2000-01-01', 1);



--- The following will fail with error ERROR 1105 (HY000): table "employee_hourly" does not have column "hourly_rate_in_dollars"

SELECT employee_hourly.employee_id, 
       SUM(hours_logged) * employee_hourly.hourly_rate_in_dollars as pay_for_period, 
       '2000-01-01' as period 
FROM logged_hours

JOIN employee_hourly ON logged_hours.employee_id = employee_hourly.employee_id

WHERE logged_date >= '2000-01-01' AND logged_date <= '2000-01-31'

GROUP BY employee_hourly.employee_id;

This is a bug in Dolt. But this query works:

SELECT employee_hourly.employee_id,
    SUM(hours_logged) as total_hours, 
    hourly_rate_in_dollars, 
    sum(hours_logged) * hourly_rate_in_dollars as pay_for_period,
    '2000-01-01' as period
FROM logged_hours

JOIN employee_hourly ON logged_hours.employee_id = employee_hourly.employee_id

WHERE logged_date >= '2000-01-01' AND logged_date <= '2000-01-31'

GROUP BY employee_hourly.employee_id;

Returns:

+-------------+-------------+------------------------+----------------+------------+
| employee_id | total_hours | hourly_rate_in_dollars | pay_for_period | period     |
+-------------+-------------+------------------------+----------------+------------+
| 1           | 4           | 105                    | 420            | 2000-01-01 |
| 2           | 2           | 45                     | 90             | 2000-01-01 |
| 3           | 3           | 85                     | 255            | 2000-01-01 |
| 4           | 3           | 100                    | 300            | 2000-01-01 |
+-------------+-------------+------------------------+----------------+------------+