Re: mysql_fdw trouble - Mailing list pgsql-general

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