Thread: mysql_fdw trouble
Hello,
I have a MySQL/PHP app that I want to port to PostgreSQL so I just installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw because I'd like to do the data migration in SQL if possible.
Installation and set up worked flawlessly but when I run the following query
SELECT
title,
description,
'[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
_filler
WHERE
starts IS NOT NULL
AND description IS NOT NULL
AND LENGTH(TRIM(title)) > 0
AND LENGTH(TRIM(description)) > 0;
SELECT
title,
description,
'[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
_filler
WHERE
starts IS NOT NULL
AND description IS NOT NULL
AND LENGTH(TRIM(title)) > 0
AND LENGTH(TRIM(description)) > 0;
I get the following error:
ERROR: failed to prepare the MySQL query:
FUNCTION latest.btrim does not exist
FUNCTION latest.btrim does not exist
FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL tables.
Any help would be appreciated.
Thanks,
Dane
On 10/29/2015 10:47 AM, Dane Foster wrote: > Hello, > > I have a MySQL/PHP app that I want to port to PostgreSQL so I just > installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw > because I'd like to do the data migration in SQL if possible. > > Installation and set up worked flawlessly but when I run the following query > SELECT > title, > description, > '[' || starts || ', ' || COALESCE(ends, 'infinity') || ']' > FROM > _filler > WHERE > starts IS NOT NULL > AND description IS NOT NULL > AND LENGTH(TRIM(title)) > 0 > AND LENGTH(TRIM(description)) > 0; > > I get the following error: > > > ERROR: failed to prepare the MySQL query: > FUNCTION latest.btrim does not exist > > FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL > tables. > > > Any help would be appreciated. What version of Postgres are you using? > > Thanks, > > > Dane -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Oct 29, 2015 at 1:56 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/29/2015 10:47 AM, Dane Foster wrote:Hello,
I have a MySQL/PHP app that I want to port to PostgreSQL so I just
installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw
because I'd like to do the data migration in SQL if possible.
Installation and set up worked flawlessly but when I run the following query
SELECT
title,
description,
'[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
_filler
WHERE
starts IS NOT NULL
AND description IS NOT NULL
AND LENGTH(TRIM(title)) > 0
AND LENGTH(TRIM(description)) > 0;
I get the following error:
ERROR: failed to prepare the MySQL query:
FUNCTION latest.btrim does not exist
FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL
tables.
Any help would be appreciated.
What version of Postgres are you using?
Thanks,
Dane
--
Adrian Klaver
adrian.klaver@aklaver.com
My bad. I should know better. It's PostgreSQL 9.5beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-bit
Thanks,
Dane
Dane Foster <studdugie@gmail.com> writes:
> Installation and set up worked flawlessly but when I run the following query
> ...
> I get the following error:
> ERROR: failed to prepare the MySQL query:
> FUNCTION latest.btrim does not exist
It looks like mysql_fdw is messing up by sending the trim() checks for
remote execution when there is no suitable function on the remote side.
Don't know whether that's a bug in mysql_fdw, or whether there's some
setup you're supposed to perform on the mysql server and have omitted.
regards, tom lane
On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dane Foster <studdugie@gmail.com> writes:
> Installation and set up worked flawlessly but when I run the following query
> ...
> I get the following error:
> ERROR: failed to prepare the MySQL query:
> FUNCTION latest.btrim does not exist
It looks like mysql_fdw is messing up by sending the trim() checks for
remote execution when there is no suitable function on the remote side.
Don't know whether that's a bug in mysql_fdw, or whether there's some
setup you're supposed to perform on the mysql server and have omitted.
regards, tom lane
I think you are correct about mysql_fdw "... sending the trim() checks for remote execution" because according to the docs:
I guess using mysql_fdw is a no-go for my data migration needs.
"The latest version will push-down the foreign table where clause to the foreign server. The where condition on the foreign table will be executed on the foreign server hence there will be fewer rows to to bring across to PostgreSQL. This is a performance feature."
Dane
On 10/29/2015 11:20 AM, Dane Foster wrote: > On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > Dane Foster <studdugie@gmail.com <mailto:studdugie@gmail.com>> writes: > > Installation and set up worked flawlessly but when I run the following query > > ... > > I get the following error: > > ERROR: failed to prepare the MySQL query: > > FUNCTION latest.btrim does not exist > > It looks like mysql_fdw is messing up by sending the trim() checks for > remote execution when there is no suitable function on the remote side. > Don't know whether that's a bug in mysql_fdw, or whether there's some > setup you're supposed to perform on the mysql server and have omitted. > > regards, tom lane > > I think you are correct about mysql_fdw "... sending the trim() checks > for remote execution" because according to the docs: > > "The latest version will push-down the foreign table where clause to the > foreign server. The where condition on the foreign table will be > executed on the foreign server hence there will be fewer rows to to > bring across to PostgreSQL. This is a performance feature." > > I guess using mysql_fdw is a no-go for my data migration needs. Before you give up I would file an issue here: https://github.com/EnterpriseDB/mysql_fdw/issues including the MySQL version also. This issue: https://github.com/EnterpriseDB/mysql_fdw/issues/42 indicates this type of thing has come up before and was fixed. > > > Dane > > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/29/2015 11:20 AM, Dane Foster wrote:
I think you are correct about mysql_fdw "... sending the trim() checks for remote execution" because according to the docs:"The latest version will push-down the foreign table where clause to the foreign server. The where condition on the foreign table will be executed on the foreign server hence there will be fewer rows to to bring across to PostgreSQL. This is a performance feature."
the alternative would be to fetch the whole table across the FDW interface, then run the where locally, for a large table where you're only selecting a few rows, this would be very painful.
I guess using mysql_fdw is a no-go for my data migration needs.
or, rewrite that WHERE clause to be mysql compatible.
-- john r pierce, recycling bits in santa cruz
On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pierce@hogranch.com> wrote:
On 10/29/2015 11:20 AM, Dane Foster wrote:I think you are correct about mysql_fdw "... sending the trim() checks for remote execution" because according to the docs:"The latest version will push-down the foreign table where clause to the foreign server. The where condition on the foreign table will be executed on the foreign server hence there will be fewer rows to to bring across to PostgreSQL. This is a performance feature."
the alternative would be to fetch the whole table across the FDW interface, then run the where locally, for a large table where you're only selecting a few rows, this would be very painful.I guess using mysql_fdw is a no-go for my data migration needs.
or, rewrite that WHERE clause to be mysql compatible.
Easier said than done because the LENGTH and TRIM functions both exist in MySQL but I guess under the covers in PostgreSQL btrim is being invoked when TRIM is called therefore that is what is being "pushed down" to the MySQL and there is nothing I can do about that.
I guess I could leave out the call to trim, and copy the data into a temp table on the PostgreSQL side, and blah blah blah. My point being why should I have to jump through hoops because mysql_fdw is broken? I'll just go back to writing the migration script as a PHP program because if mysql_fdw didn't exist that's what I would have to do anyway.
I guess I could leave out the call to trim, and copy the data into a temp table on the PostgreSQL side, and blah blah blah. My point being why should I have to jump through hoops because mysql_fdw is broken? I'll just go back to writing the migration script as a PHP program because if mysql_fdw didn't exist that's what I would have to do anyway.
-- john r pierce, recycling bits in santa cruz
Dane
On 10/29/2015 12:10 PM, Dane Foster wrote: > On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pierce@hogranch.com > <mailto:pierce@hogranch.com>> wrote: > > On 10/29/2015 11:20 AM, Dane Foster wrote: >> I think you are correct about mysql_fdw "... sending the trim() >> checks for remote execution" because according to the docs: >> >> "The latest version will push-down the foreign table where clause >> to the foreign server. The where condition on the foreign table >> will be executed on the foreign server hence there will be fewer >> rows to to bring across to PostgreSQL. This is a performance feature." >> > > the alternative would be to fetch the whole table across the FDW > interface, then run the where locally, for a large table where > you're only selecting a few rows, this would be very painful. > >> I guess using mysql_fdw is a no-go for my data migration needs. > > or, rewrite that WHERE clause to be mysql compatible. > > Easier said than done because the LENGTH and TRIM functions both exist > in MySQL but I guess under the covers in PostgreSQL btrim is being > invoked when TRIM is called therefore that is what is being "pushed > down" to the MySQL and there is nothing I can do about that. > > I guess I could leave out the call to trim, and copy the data into a > temp table on the PostgreSQL side, and blah blah blah. My point being > why should I have to jump through hoops because mysql_fdw is broken? > I'll just go back to writing the migration script as a PHP program > because if mysql_fdw didn't exist that's what I would have to do anyway. Remember you are using a Beta version of Postgres, so it is not entirely unexpected that things might be broken, especially when working with non-core extensions. In the spirit of testing, that Beta implies, why not help fix mysql_fdw by filing an issue? If you already have, my apologies. > > > -- > john r pierce, recycling bits in santa cruz > > Dane > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/29/2015 12:10 PM, Dane Foster wrote:On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.com>> wrote:
On 10/29/2015 11:20 AM, Dane Foster wrote:I think you are correct about mysql_fdw "... sending the trim()
checks for remote execution" because according to the docs:
"The latest version will push-down the foreign table where clause
to the foreign server. The where condition on the foreign table
will be executed on the foreign server hence there will be fewer
rows to to bring across to PostgreSQL. This is a performance feature."
the alternative would be to fetch the whole table across the FDW
interface, then run the where locally, for a large table where
you're only selecting a few rows, this would be very painful.I guess using mysql_fdw is a no-go for my data migration needs.
or, rewrite that WHERE clause to be mysql compatible.
Easier said than done because the LENGTH and TRIM functions both exist
in MySQL but I guess under the covers in PostgreSQL btrim is being
invoked when TRIM is called therefore that is what is being "pushed
down" to the MySQL and there is nothing I can do about that.
I guess I could leave out the call to trim, and copy the data into a
temp table on the PostgreSQL side, and blah blah blah. My point being
why should I have to jump through hoops because mysql_fdw is broken?
I'll just go back to writing the migration script as a PHP program
because if mysql_fdw didn't exist that's what I would have to do anyway.
Remember you are using a Beta version of Postgres, so it is not entirely unexpected that things might be broken, especially when working with non-core extensions. In the spirit of testing, that Beta implies, why not help fix mysql_fdw by filing an issue? If you already have, my apologies.
I'm fully aware of that fact and gladly accept my responsibility which is why I have opened an issue:https://github.com/EnterpriseDB/mysql_fdw/issues/70
For me reporting the issue in the hopes that they will fix it is a separate issue from expending energy working around the bug because the great thing about the procedural code is that it's littered w/ the same SQL that a pure SQL migration script would contain. So if they fix it in reasonable amount of time then all that's required to create a pure SQL migration script is copy/paste.
Dane
--
john r pierce, recycling bits in santa cruz
Dane
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/29/2015 12:56 PM, Dane Foster wrote: > On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 10/29/2015 12:10 PM, Dane Foster wrote: > > On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce > <pierce@hogranch.com <mailto:pierce@hogranch.com> > <mailto:pierce@hogranch.com <mailto:pierce@hogranch.com>>> wrote: > > On 10/29/2015 11:20 AM, Dane Foster wrote: > > I think you are correct about mysql_fdw "... sending > the trim() > checks for remote execution" because according to the docs: > > "The latest version will push-down the foreign table > where clause > to the foreign server. The where condition on the > foreign table > will be executed on the foreign server hence there will > be fewer > rows to to bring across to PostgreSQL. This is a > performance feature." > > > the alternative would be to fetch the whole table across > the FDW > interface, then run the where locally, for a large table where > you're only selecting a few rows, this would be very painful. > > I guess using mysql_fdw is a no-go for my data > migration needs. > > > or, rewrite that WHERE clause to be mysql compatible. > > Easier said than done because the LENGTH and TRIM functions > both exist > in MySQL but I guess under the covers in PostgreSQL btrim is being > invoked when TRIM is called therefore that is what is being "pushed > down" to the MySQL and there is nothing I can do about that. > > I guess I could leave out the call to trim, and copy the data into a > temp table on the PostgreSQL side, and blah blah blah. My point > being > why should I have to jump through hoops because mysql_fdw is broken? > I'll just go back to writing the migration script as a PHP program > because if mysql_fdw didn't exist that's what I would have to do > anyway. > > > Remember you are using a Beta version of Postgres, so it is not > entirely unexpected that things might be broken, especially when > working with non-core extensions. In the spirit of testing, that > Beta implies, why not help fix mysql_fdw by filing an issue? If you > already have, my apologies. > > I'm fully aware of that fact and gladly accept my responsibility which > is why I have opened an > issue:https://github.com/EnterpriseDB/mysql_fdw/issues/70 Great and thanks. > > For me reporting the issue in the hopes that they will fix it is a > separate issue from expending energy working around the bug because the > great thing about the procedural code is that it's littered w/ the same > SQL that a pure SQL migration script would contain. So if they fix it in > reasonable amount of time then all that's required to create a pure SQL > migration script is copy/paste. > > Dane > > > > -- > john r pierce, recycling bits in santa cruz > > Dane > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
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 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
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,
Dane
On Thu, Oct 29, 2015 at 4:37 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/29/2015 12:56 PM, Dane Foster wrote:On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
On 10/29/2015 12:10 PM, Dane Foster wrote:
On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce
<pierce@hogranch.com <mailto:pierce@hogranch.com><mailto:pierce@hogranch.com <mailto:pierce@hogranch.com>>> wrote:
On 10/29/2015 11:20 AM, Dane Foster wrote:
I think you are correct about mysql_fdw "... sending
the trim()
checks for remote execution" because according to the docs:
"The latest version will push-down the foreign table
where clause
to the foreign server. The where condition on the
foreign table
will be executed on the foreign server hence there will
be fewer
rows to to bring across to PostgreSQL. This is a
performance feature."
the alternative would be to fetch the whole table across
the FDW
interface, then run the where locally, for a large table where
you're only selecting a few rows, this would be very painful.
I guess using mysql_fdw is a no-go for my data
migration needs.
or, rewrite that WHERE clause to be mysql compatible.
Easier said than done because the LENGTH and TRIM functions
both exist
in MySQL but I guess under the covers in PostgreSQL btrim is being
invoked when TRIM is called therefore that is what is being "pushed
down" to the MySQL and there is nothing I can do about that.
I guess I could leave out the call to trim, and copy the data into a
temp table on the PostgreSQL side, and blah blah blah. My point
being
why should I have to jump through hoops because mysql_fdw is broken?
I'll just go back to writing the migration script as a PHP program
because if mysql_fdw didn't exist that's what I would have to do
anyway.
Remember you are using a Beta version of Postgres, so it is not
entirely unexpected that things might be broken, especially when
working with non-core extensions. In the spirit of testing, that
Beta implies, why not help fix mysql_fdw by filing an issue? If you
already have, my apologies.
I'm fully aware of that fact and gladly accept my responsibility which
is why I have opened an
issue:https://github.com/EnterpriseDB/mysql_fdw/issues/70
Great and thanks.
For me reporting the issue in the hopes that they will fix it is a
separate issue from expending energy working around the bug because the
great thing about the procedural code is that it's littered w/ the same
SQL that a pure SQL migration script would contain. So if they fix it in
reasonable amount of time then all that's required to create a pure SQL
migration script is copy/paste.
Dane
--
john r pierce, recycling bits in santa cruz
Dane
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thu, Oct 29, 2015 at 8:22 PM, CaT <cat@zip.com.au> wrote:
On Thu, Oct 29, 2015 at 08:38:49PM -0400, Dane Foster wrote:
> 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
Then it should be a new thread or the old post should not be quoted.
So there! ;)
--
"A search of his car uncovered pornography, a homemade sex aid, women's
stockings and a Jack Russell terrier."
- http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1111118083480
Well played sir!
Dane
On Thu, Oct 29, 2015 at 2:20 PM, Dane Foster <studdugie@gmail.com> wrote:
On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Dane Foster <studdugie@gmail.com> writes:
> Installation and set up worked flawlessly but when I run the following query
> ...
> I get the following error:
> ERROR: failed to prepare the MySQL query:
> FUNCTION latest.btrim does not exist
It looks like mysql_fdw is messing up by sending the trim() checks for
remote execution when there is no suitable function on the remote side.
Don't know whether that's a bug in mysql_fdw, or whether there's some
setup you're supposed to perform on the mysql server and have omitted.
regards, tom laneI think you are correct about mysql_fdw "... sending the trim() checks for remote execution" because according to the docs:I guess using mysql_fdw is a no-go for my data migration needs."The latest version will push-down the foreign table where clause to the foreign server. The where condition on the foreign table will be executed on the foreign server hence there will be fewer rows to to bring across to PostgreSQL. This is a performance feature."
Dane
I'm not sure who to direct this question to but if the root cause is really automatic push-down what about instead of automatic push-down of the WHERE clause the mysql_fdw detected when the WHERE clause contained PostgreSQL specific functions and not push the WHERE clause to MySQL? The docs suggest that the old version did not push the WHERE clause down which suggests that WHERE clause processing occurred on the PostgreSQL side. So what if that PostgreSQL side WHERE clause processing code is revived and used in the case where the WHERE clause shouldn't be pushed down?
This is all speculation of course and I don't have the time nor expertise to go hacking on this idea. So I won't be offended if no one thinks it's a good idea nor volunteers to write the code.
This is all speculation of course and I don't have the time nor expertise to go hacking on this idea. So I won't be offended if no one thinks it's a good idea nor volunteers to write the code.
Dane
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
On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver <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
I get rows of data, location and all.
Dane
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>> 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> > > > 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
On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver <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 Klaveradrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><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
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
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
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
On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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
<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>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
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
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
On 10/30/2015 09:36 AM, Dane Foster wrote: > On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > 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> > <mailto: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>> > <mailto: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>> > <mailto: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> > <mailto: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 <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com
On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/30/2015 09:36 AM, Dane Foster wrote:On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
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><mailto:adrian.klaver@aklaver.com<mailto: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>>
<mailto: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>>
<mailto: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>adrian.klaver@aklaver.com <mailto: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
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;
It returns all the locations.
Dane
On 10/30/2015 09:55 AM, Dane Foster wrote: > On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 10/30/2015 09:36 AM, Dane Foster wrote: > > On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> wrote: > > 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> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto: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>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > <mailto: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>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > <mailto: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> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > > <mailto: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 <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com
On Fri, Oct 30, 2015 at 1:02 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/30/2015 09:55 AM, Dane Foster wrote:On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
On 10/30/2015 09:36 AM, Dane Foster wrote:
On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>> wrote:
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>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>><mailto:adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto: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>>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>
<mailto: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>>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>
<mailto: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>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto: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 <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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;
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.