Error:

DROP SCHEMA IF EXISTS `demo_logic_bug`;

CREATE SCHEMA `demo_logic_bug`;

USE demo_logic_bug;

DROP TEMPORARY TABLE IF EXISTS `users`;

CREATE TEMPORARY TABLE users (

    user_id int primary key  AUTO_INCREMENT,

    name varchar(100) not null,

    favorite_color varchar(30),

    gender varchar(20),

    department_id int NOT NULL

);

DROP TEMPORARY TABLE IF EXISTS `department`;

CREATE TEMPORARY TABLE department (

    department_id int primary key AUTO_INCREMENT,

    name varchar(64) not null,

    building varchar(60),

    floor int default 1

);

-- Seed some departments

INSERT INTO department(name, building, floor)

VALUES ('IT', 'Main', 2);

INSERT INTO department(name, building, floor)

VALUES ('Sales', 'Main', 1);

INSERT INTO department(name, building, floor)

VALUES ('Engineering', 'Building 1', 1);

INSERT INTO department(name, building, floor)

VALUES ('Accounting', 'Building 1', 2);

INSERT INTO department(name, building, floor)

VALUES ('Support', 'Building 2', 1);

-- Seed some users

INSERT INTO users(name, favorite_color, gender, department_id)

VALUES ('Alan Smith', 'Red', 'Male', 2);

INSERT INTO users(name, favorite_color, gender, department_id)

VALUES ('Ron Jones', 'Blue', 'Male', 4);

INSERT INTO users(name, favorite_color, gender, department_id)

VALUES ('Lisa Watkins', 'Red', 'Female', 1);

INSERT INTO users(name, favorite_color, gender, department_id)

VALUES ('Karina Washington', 'Red', 2, 1);

INSERT INTO users(name, favorite_color, gender, department_id)

VALUES ('Alice Sampson', 'Orange', 'Female', 3);

-- A simple test query...

SELECT  u.*, d.name, d.building, d.floor, 'This is a test query to get started.' AS note

FROM    users u

    INNER JOIN department d on u.department_id = d.department_id

/*

This issue is caused ONLY when there are 4 specific conditions in a select query:

1) The keyword DISTINCT is used in a subquery

2) A CASE statement is used, specifically with the syntax of =>  CASE field WHEN 'this' then 'that' END

3) A column alias is assigned to that result

4) The column alias is used in the WHERE clause for filtering

The queries below exhibit these combinations and will replicate the defect. This was discovered on MariaDB 10.11.9

with the latest maintenance applied to an AWS RDS instance of MariaDB.

 */

-- BROKEN: This query uses all the required criteria and will result in the following error:

--   Error: unexpected end of stream, read 0 bytes from 4 (socket was closed by server)

-- If the query is retried again, within about 20 seconds, a different error is displayed:

--   Error: Socket fail to connect to host:<aws-instance-id>.us-east-1.rds.amazonaws.com, port:3306. Connection refused

-- This query works on MySQL, but not the latest MariaDB.

/*

SELECT  d.*, 'This FAILS because it has all 4 error conditions present.' as note

FROM (SELECT DISTINCT

             department_id,

             name,

             CASE building

                WHEN 'Main' THEN 'North'

                WHEN 'Building 1' THEN 'East'

                WHEN 'Building 2' THEN 'West' END AS direction

             FROM department) d

WHERE direction = 'North';

*/

-- A slight refactoring of the CASE statement misses the bug.

SELECT  d.*, 'Refactoring the CASE statement to a less optimal syntax misses the error.' as note

FROM (SELECT DISTINCT

             department_id,

             name,

             building,

             CASE

                WHEN building = 'Main' THEN 'North'

                WHEN building = 'Building 1' THEN 'East'

                WHEN building = 'Building 2' THEN 'West' END AS direction

             FROM department) d

WHERE direction = 'North'

UNION

-- Removing the DISTINCT keyword in the subquery misses the bug.

SELECT  d.*, 'This doesn\'t use the DISTINCT keyword in the subquery, so it works.'  as note

FROM (SELECT

             department_id,

             name,

             building,

             CASE building

                WHEN 'Main' THEN 'North'

                WHEN 'Building 1' THEN 'East'

                WHEN 'Building 2' THEN 'West' END AS direction

             FROM department) d

WHERE direction = 'North'

UNION

SELECT  d.*, 'Removing the reference to the column alias in the WHERE clause bypasses the error condition.' as note

FROM (SELECT DISTINCT

             department_id,

             name,

             building,

             CASE building

                WHEN 'Main' THEN 'North'

                WHEN 'Building 1' THEN 'East'

                WHEN 'Building 2' THEN 'West' END AS direction

             FROM department) d

WHERE d.building = 'Main';