Search Postgresql Archives

Re: mysql_fdw trouble

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 10/30/2015 07:21 AM, Dane Foster wrote:

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>


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

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

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux