NULL constraint violation

[studdugie]

Hello,

I have some SQL that's failing because of a SELECT list sub-query. The bug is that when the sub-query is run on the local MySQL server it runs fine but when used as part of a larger query in the FDW it fails w/ a NULL constraint violation error.

I've put together a test case for you. It has been written against MySQL 5.6.27 and PostgreSQL 9.5beta1. The top part is the MySQL SQL. You should be able to just copy/paste into a MySQL client/terminal. The second half is PostgreSQL. You'll need to modify the OPTIONs clauses to point to your MySQL instance and username/password. After that you should be able to run it by copy/pasting into psql.

--MySQL:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE locations (
  loc INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  label VARCHAR(70) NOT NULL,
  deleted BOOLEAN NOT NULL DEFAULT FALSE,
  UNIQUE (label)
);
INSERT INTO locations (label)
VALUES ('Location 1'),('Location 2'), ('Location 3');

CREATE TABLE series (
  cid BIGINT UNSIGNED NOT NULL,
  starts DATETIME NOT NULL,
  title VARCHAR(140) NOT NULL,
  description TEXT NOT NULL,
  location INTEGER UNSIGNED NOT NULL,
  can_join BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (starts, cid),
  FOREIGN KEY (location) REFERENCES locations (loc)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);
INSERT INTO series (cid, starts, title, description, location) VALUES
(1, '2015-01-01 15:00:00', 'Series #1 day 1', 'day 1', 1),
(1, '2015-01-02 15:00:00', 'Series #1 day 2', 'day 2 ', 1),
(1, '2015-01-03 15:00:00', 'Series #1 day 3', 'day 3 ', 1),
(2, '2015-02-01 15:00:00', 'Series #2 day 1', 'day 1', 2),
(2, '2015-02-02 15:00:00', 'Series #2 day 2', 'day 2 ', 2),
(2, '2015-02-03 15:00:00', 'Series #2 day 3', 'day 3 ', 2),
(3, '2015-03-01 15:00:00', 'Series #3 day 1', 'day 1', 3),
(3, '2015-03-02 15:00:00', 'Series #3 day 2', 'day 2 ', 3),
(3, '2015-03-03 15:00:00', 'Series #3 day 3', 'day 3 ', 3);

-- Verify that the tables exists and are populated.
SELECT * FROM locations;
SELECT * FROM series;

-- This query is the same query that fails when executed using the FDW later
SELECT (SELECT label FROM locations WHERE loc=location) AS location FROM series;

-- PostgreSQL (assuming already @ PostgreSQL terminal/psql):
DROP database IF EXISTS test;
CREATE database test;
\c test;

CREATE EXTENSION btree_gin;
CREATE EXTENSION btree_gist;
CREATE EXTENSION mysql_fdw;

CREATE TABLE locations (
  name VARCHAR(70) PRIMARY KEY,
  deleted BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE series (
  cid INT,
  day INT CHECK (day > 0),
  title VARCHAR(140)
        NOT NULL
        CHECK (length(trim(E' \r\n\t' FROM title)) BETWEEN 1 AND 140),
  description TEXT NOT NULL,
  location VARCHAR(70) NOT NULL REFERENCES locations ON UPDATE CASCADE,
  duration TSTZRANGE NOT NULL CHECK (NOT (lower_inf(duration) OR upper_inf(duration))),
  can_join BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (cid, day),
  CONSTRAINT series_no_overlap EXCLUDE USING GIST (cid WITH =, duration WITH &&)
);
CREATE INDEX ON series USING GIN (location);
CREATE INDEX ON series USING GIST (duration);

CREATE SERVER mysql_server
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'YOUR_HOST_OR_IP');

CREATE USER MAPPING FOR postgres
  SERVER mysql_server
  OPTIONS (username 'YOUR_USER_NAME');

CREATE FOREIGN TABLE _locations (
  loc INTEGER,
  label VARCHAR(70),
  deleted BOOLEAN
) SERVER mysql_server OPTIONS (dbname 'test', table_name 'locations');

CREATE FOREIGN TABLE _series (
  cid BIGINT,
  starts TIMESTAMPTZ,
  title VARCHAR(140),
  description TEXT,
  location INTEGER,
  instructor INTEGER,
  can_join BOOLEAN
) SERVER mysql_server OPTIONS (dbname 'test', table_name 'series');

-- The test case that fails w/ a NULL constraint violation.
INSERT INTO series (cid, day, title, description, location, duration, can_join)
  SELECT
    cid,
    row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
    title,
    description,
    location,
    duration,
    can_join
  FROM (
    SELECT
      cid,
      title,
      description,
      can_join::BOOLEAN,
      (SELECT label FROM _locations WHERE loc=location) AS location,
      ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || ']')::TSTZRANGE AS duration
    FROM
      _series
  ) AS v;

[ibrarahmad]

You have not null constraint on column location

-- location VARCHAR(70) NOT NULL REFERENCES locations ON -- UPDATE CASCADE,

And your inner query returns nulls which you try to insert into location
column. Here is the result of you inner query

postgres=#

SELECT
cid,
row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
title,
description,
location,
duration,
can_join
FROM (
SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series) AS a;
cid | row_number | title | description | location |
duration | can_join
-----+------------+-----------------+-------------+------------+-----------------------------------------------------+----------
1 | 1 | Series #1 day 1 | day 1 | Location 1 |
["2015-01-01 15:00:00+05","2015-01-01 19:00:00+05"] | t
1 | 2 | Series #1 day 2 | day 2 | |
["2015-01-02 15:00:00+05","2015-01-02 19:00:00+05"] | t
1 | 3 | Series #1 day 3 | day 3 | |
["2015-01-03 15:00:00+05","2015-01-03 19:00:00+05"] | t
2 | 1 | Series #2 day 1 | day 1 | |
["2015-02-01 15:00:00+05","2015-02-01 19:00:00+05"] | t
2 | 2 | Series #2 day 2 | day 2 | |
["2015-02-02 15:00:00+05","2015-02-02 19:00:00+05"] | t
2 | 3 | Series #2 day 3 | day 3 | |
["2015-02-03 15:00:00+05","2015-02-03 19:00:00+05"] | t
3 | 1 | Series #3 day 1 | day 1 | |
["2015-03-01 15:00:00+05","2015-03-01 19:00:00+05"] | t
3 | 2 | Series #3 day 2 | day 2 | |
["2015-03-02 15:00:00+05","2015-03-02 19:00:00+05"] | t
3 | 3 | Series #3 day 3 | day 3 | |
["2015-03-03 15:00:00+05","2015-03-03 19:00:00+05"] | t
(9 rows)

On Fri, Oct 30, 2015 at 2:28 AM, Dane notifications@github.com wrote:

Hello,
I have some SQL that's failing because of a SELECT list sub-query. The but
is that when the sub-query is run on the local MySQL server it runs fine
but when used as part of a larger query in the FDW it fails w/ a NULL
constraint violation error.

I've put together a test case for you. It has been written against MySQL
5.6.27 and PostgreSQL 9.5beta1. The top part is the MySQL SQL. You should
be able to just copy/paste into a MySQL client/terminal. The second half is
PostgreSQL. You'll need to modify the OPTIONs clauses to point to your
MySQL instance and username/password. After that you should be to run it by
copy/pasting into psql.

--MySQL:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE locations (
loc INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
label VARCHAR(70) NOT NULL,
deleted BOOLEAN NOT NULL DEFAULT FALSE,
UNIQUE (label)
);

CREATE TABLE series (
cid BIGINT UNSIGNED NOT NULL,
starts DATETIME NOT NULL,
title VARCHAR(140) NOT NULL,
description TEXT NOT NULL,
location INTEGER UNSIGNED NOT NULL,
can_join BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (starts, cid),
FOREIGN KEY (location) REFERENCES locations (loc)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
SHOW TABLES;

INSERT INTO locations (label)
VALUES ('Location 1'),('Location 2'), ('Location 3');

INSERT INTO series (cid, starts, title, description, location) VALUES
(1, '2015-01-01 15:00:00', 'Series #1 day 1', 'day 1', 1),
(1, '2015-01-02 15:00:00', 'Series #1 day 2', 'day 2 ', 1),
(1, '2015-01-03 15:00:00', 'Series #1 day 3', 'day 3 ', 1),
(2, '2015-02-01 15:00:00', 'Series #2 day 1', 'day 1', 2),
(2, '2015-02-02 15:00:00', 'Series #2 day 2', 'day 2 ', 2),
(2, '2015-02-03 15:00:00', 'Series #2 day 3', 'day 3 ', 2),
(3, '2015-03-01 15:00:00', 'Series #3 day 1', 'day 1', 3),
(3, '2015-03-02 15:00:00', 'Series #3 day 2', 'day 2 ', 3),
(3, '2015-03-03 15:00:00', 'Series #3 day 3', 'day 3 ', 3);

-- This query is the same query that fails when executed using the FDW later
SELECT (SELECT label FROM locations WHERE loc=location) AS location FROM series;

-- PostgreSQL (assuming already @ PostgreSQL terminal/psql):
DROP database IF EXISTS test;
CREATE database test;
\c test;

CREATE EXTENSION btree_gin;
CREATE EXTENSION btree_gist;
CREATE EXTENSION mysql_fdw;

CREATE TABLE locations (
name VARCHAR(70) PRIMARY KEY,
deleted BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE series (
cid INT,
day INT CHECK (day > 0),
title VARCHAR(140)
NOT NULL
CHECK (length(trim(E' \r\n\t' FROM title)) BETWEEN 1 AND 140),
description TEXT NOT NULL,
location VARCHAR(70) NOT NULL REFERENCES locations ON UPDATE CASCADE,
duration TSTZRANGE NOT NULL CHECK (NOT (lower_inf(duration) OR upper_inf(duration))),
can_join BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (cid, day),
CONSTRAINT series_no_overlap EXCLUDE USING GIST (cid WITH =, duration WITH &&)
);
CREATE INDEX ON series USING GIN (location);
CREATE INDEX ON series USING GIST (duration);

CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'YOUR_HOST_OR_IP');

CREATE USER MAPPING FOR postgres
SERVER mysql_server
OPTIONS (username 'YOUR_USER_NAME');

CREATE FOREIGN TABLE _locations (
loc INTEGER,
label VARCHAR(70),
deleted BOOLEAN
) SERVER mysql_server OPTIONS (dbname 'test', table_name 'locations');

CREATE FOREIGN TABLE _series (
cid BIGINT,
starts TIMESTAMPTZ,
title VARCHAR(140),
description TEXT,
location INTEGER,
instructor INTEGER,
can_join BOOLEAN
) SERVER mysql_server OPTIONS (dbname 'test', table_name 'series');

-- The test case that fails w/ a NULL constraint violation.
INSERT INTO series (cid, day, title, description, location, duration, can_join)
SELECT
cid,
row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
title,
description,
location,
duration,
can_join
FROM (
SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || ']')::TSTZRANGE AS duration
FROM
_series
) AS v;

—
Reply to this email directly or view it on GitHub
#71.

Ibrar Ahmed
EnterpriseDB http://www.enterprisedb.com