Re: mysql_fdw trouble

On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver <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>> 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>>> 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>>
>>
>> ​
>> 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>
>>
>>
>> ​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
>
​
Your first query didn't use it and as discussed rows come back but only the
first row has a non NULL location column.

Dane
​