Re: mysql_fdw trouble

On 10/30/2015 09:36 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

    On 10/30/2015 08:13 AM, Dane Foster wrote:


        On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
        <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>> wrote:

             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>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>

                 <mailto: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>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>

                 <mailto: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 <mailto:adrian.klaver@aklaver.com>

        <mailto:adrian.klaver@aklaver.com

        <mailto:adrian.klaver@aklaver.com>>


        ​Before I answer your second query question I need to revise my
        response
        to the first. Yes the first query runs w/o an error message but
        the bit
        about "rows and all" was not entirely correct. Out of 313 rows
        only the
        first row had a location. The other 312 rows have NULL in the
        location
        column which is not supposed to happen. To verify this I changed the
        table names and removed the PostgreSQL transformations (i.e.,
        use of ||
        and :: for casting) and ran the query against the MySQL database; it
        returned 313 rows of data, location and all.


    You would think that would also cause an issue with the first row
    that is returned correctly. My suspicion is with this:

    row_number() OVER (PARTITION BY cid ORDER BY lower(duration))

    What happens if you run the full SELECT without it?




        Now that I've cleared that up.

        Your second query also runs w/o any error messages but like the
        first
        only the first row has a non NULL value in the location column.



        Dane
        ​



    --
    Adrian Klaver

    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

​
Your first query didn't use it and as discussed rows come back but only
the first row has a non NULL location column.

Forgot about that. Where I was going with this is that duration comes from:

('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || ']')::TSTZRANGE AS duration

and MySQL and Postgres have different ideas about timestamps. While I thinking about what that meant in the context of the query I realized I was stepping over the obvious:

SELECT label FROM _locations WHERE loc=location

So what does the below show:

SELECT label FROM _locations, _series WHERE loc=location;

Dane
​

--
Adrian Klaver
adrian.klaver@aklaver.com