Re: mysql_fdw trouble - Mailing list pgsql-general

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>> 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>>> 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>>

        ​
        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>


​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