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