Re: mysql_fdw trouble

On 10/30/2015 07:21 AM, Dane Foster wrote:
>
> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 10/29/2015 05:38 PM, Dane Foster wrote:
>
> Hello,
>
> I think I've tripped over another mysq_fdw bug. I've filed a bug
> report
> on github already but just in case the problem is w/ my query I
> figured
> I would post it here in case someone sees something obvious.
>
> The error message I get is: null value in column "location" violates
> not-null constraint.
>
> The DDL is here: https://github.com/EnterpriseDB/mysql_fdw/issues/71
>
> For the record I know top posting is a crime against god and
> humanity
> but I feel justified because this post is not directly related
> to the
> original. So there! Granted it's in the same milieu; and yes this
> current sentence exists for the sole purpose of me being able to
> use the
> word milieu because the opportunity to use it is so few and far
> between.
>
> ​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') ||
> ']')::TSZ_PERIOD AS duration
> FROM
> _series
> ) AS v​
>
> ​Regards,​
>
>
> So what do you get when you do?:
>
> 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
> );
>
>
> Dane
>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
> ​
> I get rows of data, location and all.

And when you do?:

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

>
> Dane
> ​
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com