Re: mysql_fdw trouble

On Fri, Oct 30, 2015 at 1:02 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

Converting my query to its JOIN equivalent did indeed do the trick. O the
joys of relational algebra and calculus.

Well played Sir (or Madam) ! And thanks.

Dane

P.S. Now if only you can fix the trim/btrim problem then I'll be happy ...
until I find something new to complain about.
​