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