Re: mysql_fdw trouble - Mailing list pgsql-general

On 10/30/2015 07:21 AM, Dane Foster wrote:
>
> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com