Re: mysql_fdw trouble

On 10/30/2015 09:55 AM, Dane Foster wrote:
> On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 10/30/2015 09:36 AM, Dane Foster wrote:
>
> On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
>
> On 10/30/2015 08:13 AM, Dane Foster wrote:
>
>
> On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>>> wrote:
>
> On 10/30/2015 07:21 AM, Dane Foster wrote:
>
>
> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
> ​
> It returns all the locations.

You know where we are going:

SELECT label, starts, ('[' || starts || ', ' || (starts + INTERVAL '4
HOUR') || ']')::TSTZRANGE AS duration FROM _locations, _series WHERE
loc=location;

>
> Dane
> ​
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com