query option missing

[gubuntu]

I have a number of foreign tables defined with the query option, which seems to be no longer supported. Is this coming back or has it been removed intentionally or is it a bug?

[ibrarahmad]

The query option is no longer supported. You can still use the older branch if you want to use query.

On Thu, Dec 11, 2014 at 4:18 PM, Gavin Fleming [email protected] wrote:

I have a number of foreign tables defined with the query option, which seems to be no longer supported. Is this coming back or has it been removed intentionally or is it a bug?

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/31.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

[cimmanon]

With the query option removed, what is the recommended way of dealing with MySQL's love of invalid data? I have multiple tables that contain dates like the ever popular '0000-00-00', and cleaning those up is just not an option right now.

See: http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

[ibrarahmad]

I don't understand your problem correctly. Here is the sequence of SQL you are using.

1 - josh=# create extension mysql_fdw; 2 - josh=# create server mysql_svr foreign data wrapper mysql_fdw options (address '127.0.0.1', port '3306'); 3 - josh=# create user mapping for public server mysql_svr options (username 'josh', password ''); 5 - josh=# create foreign table bad_dates (mydate date) server mysql_svr options (query 'select * from test.bad_dates'); 6 - josh=# select * from bad_dates ; ERROR: date/time field value out of range: "0000-00-00"

In Step-5 you are using "SELECT * FROM test.bad_dates". Here you can use the table_name option, there will be no difference between the two.

If you want to avoid '0000-00-00' values you can create a view and create a FOREIGN TABLE on that VIEW.

On Tue, Dec 16, 2014 at 5:49 AM, cimmanon [email protected] wrote:

With the query option removed, what is the recommended way of dealing with MySQL's love of invalid data? I have multiple tables that contain dates like the ever popular '0000-00-00', and cleaning those up is just not an option right now.

See: http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67094938 .

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

[dpage]

Has the query option actually been removed? If so, please return it. That can be very useful, and views are not always a feasible option.

On Tue, Dec 16, 2014 at 1:08 PM, Ibrar Ahmed [email protected] wrote:

I don't understand your problem correctly. Here are sequence of SQL you are using

1 - josh=# create extension mysql_fdw; 2 - josh=# create server mysql_svr foreign data wrapper mysql_fdw options (address '127.0.0.1', port '3306'); 3 - josh=# create user mapping for public server mysql_svr options (username 'josh', password ''); 5 - josh=# create foreign table bad_dates (mydate date) server mysql_svr options (query 'select * from test.bad_dates'); 6 - josh=# select * from bad_dates ; ERROR: date/time field value out of range: "0000-00-00"

In Step-5 you are using "SELECT * FROM test.bad_dates". Here you can use the table_name option, there will be no difference between the two.

If you can avoid '0000-00-00' values you can create a view and create a foreign data wrapper on that VIEW.

On Tue, Dec 16, 2014 at 5:49 AM, cimmanon [email protected] wrote:

With the query option removed, what is the recommended way of dealing with MySQL's love of invalid data? I have multiple tables that contain dates like the ever popular '0000-00-00', and cleaning those up is just not an option right now.

See: http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67094938>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67156854 .

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

[ibrarahmad]

Yes, this option is removed, because we can create FT on Views. Otherwise, we have to handle query option separately because we are deparsing queries based on the actual query columns. We also need to add a check to disable writable support.

AFAIK, I don't know any other FDW supporting query option Is any?

On Tue, Dec 16, 2014 at 6:12 PM, Dave Page [email protected] wrote:

Has the query option actually been removed? If so, please return it. That can be very useful, and views are not always a feasible option.

On Tue, Dec 16, 2014 at 1:08 PM, Ibrar Ahmed [email protected] wrote:

I don't understand your problem correctly. Here are sequence of SQL you are using

1 - josh=# create extension mysql_fdw; 2 - josh=# create server mysql_svr foreign data wrapper mysql_fdw options (address '127.0.0.1', port '3306'); 3 - josh=# create user mapping for public server mysql_svr options (username 'josh', password ''); 5 - josh=# create foreign table bad_dates (mydate date) server mysql_svr options (query 'select * from test.bad_dates'); 6 - josh=# select * from bad_dates ; ERROR: date/time field value out of range: "0000-00-00"

In Step-5 you are using "SELECT * FROM test.bad_dates". Here you can use the table_name option, there will be no difference between the two.

If you can avoid '0000-00-00' values you can create a view and create a foreign data wrapper on that VIEW.

On Tue, Dec 16, 2014 at 5:49 AM, cimmanon [email protected] wrote:

With the query option removed, what is the recommended way of dealing with MySQL's love of invalid data? I have multiple tables that contain dates like the ever popular '0000-00-00', and cleaning those up is just not an option right now.

See: http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67094938>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67156854>

.

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67157287 .

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

[dpage]

There are often cases where it's not feasible to use a view, and the query option was included to cater to those cases. Other FDWs may not support it, but that doesn't mean it's not a good idea and clearly people are missing it now.

On Tue, Dec 16, 2014 at 1:18 PM, Ibrar Ahmed [email protected] wrote:

Yes, this option is removed, because we can create FT on Views. Otherwise, we have to handle query option separately because we are deparsing queries based on the actual query columns. We also need to add a check to disable writable support.

AFAIK, I don't know any other FDW supporting query option Is any?

On Tue, Dec 16, 2014 at 6:12 PM, Dave Page [email protected] wrote:

Has the query option actually been removed? If so, please return it. That can be very useful, and views are not always a feasible option.

On Tue, Dec 16, 2014 at 1:08 PM, Ibrar Ahmed [email protected] wrote:

I don't understand your problem correctly. Here are sequence of SQL you are using

1 - josh=# create extension mysql_fdw; 2 - josh=# create server mysql_svr foreign data wrapper mysql_fdw options (address '127.0.0.1', port '3306'); 3 - josh=# create user mapping for public server mysql_svr options (username 'josh', password ''); 5 - josh=# create foreign table bad_dates (mydate date) server mysql_svr options (query 'select * from test.bad_dates'); 6 - josh=# select * from bad_dates ; ERROR: date/time field value out of range: "0000-00-00"

In Step-5 you are using "SELECT * FROM test.bad_dates". Here you can use the table_name option, there will be no difference between the two.

If you can avoid '0000-00-00' values you can create a view and create a foreign data wrapper on that VIEW.

On Tue, Dec 16, 2014 at 5:49 AM, cimmanon [email protected] wrote:

With the query option removed, what is the recommended way of dealing with MySQL's love of invalid data? I have multiple tables that contain dates like the ever popular '0000-00-00', and cleaning those up is just not an option right now.

See: http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67094938>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67156854>

.

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67157287>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67157920 .

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

[ibrarahmad]

Ok, I will add that support in new code as early as possible, because it's not as straight forward as it looks.

On Tue, Dec 16, 2014 at 6:22 PM, Dave Page [email protected] wrote:

There are often cases where it's not feasible to use a view, and the query option was included to cater to those cases. Other FDWs may not support it, but that doesn't mean it's not a good idea and clearly people are missing it now.

On Tue, Dec 16, 2014 at 1:18 PM, Ibrar Ahmed [email protected] wrote:

Yes, this option is removed, because we can create FT on Views. Otherwise, we have to handle query option separately because we are deparsing queries based on the actual query columns. We also need to add a check to disable writable support.

AFAIK, I don't know any other FDW supporting query option Is any?

On Tue, Dec 16, 2014 at 6:12 PM, Dave Page [email protected] wrote:

Has the query option actually been removed? If so, please return it. That can be very useful, and views are not always a feasible option.

On Tue, Dec 16, 2014 at 1:08 PM, Ibrar Ahmed [email protected]

wrote:

I don't understand your problem correctly. Here are sequence of SQL you are using

1 - josh=# create extension mysql_fdw; 2 - josh=# create server mysql_svr foreign data wrapper mysql_fdw options (address '127.0.0.1', port '3306'); 3 - josh=# create user mapping for public server mysql_svr options (username 'josh', password ''); 5 - josh=# create foreign table bad_dates (mydate date) server mysql_svr options (query 'select * from test.bad_dates'); 6 - josh=# select * from bad_dates ; ERROR: date/time field value out of range: "0000-00-00"

In Step-5 you are using "SELECT * FROM test.bad_dates". Here you can use the table_name option, there will be no difference between the two.

If you can avoid '0000-00-00' values you can create a view and create a foreign data wrapper on that VIEW.

On Tue, Dec 16, 2014 at 5:49 AM, cimmanon [email protected]

wrote:

With the query option removed, what is the recommended way of dealing with MySQL's love of invalid data? I have multiple tables that contain dates like the ever popular '0000-00-00', and cleaning those up is just not an option right now.

See: http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67094938>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67156854>

.

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67157287>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67157920>

.

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67158363 .

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

[dpage]

Thank you.

On Tue, Dec 16, 2014 at 1:27 PM, Ibrar Ahmed [email protected] wrote:

Ok, I will add that support in new code as early as possible, because it's not as straight forward as it looks.

On Tue, Dec 16, 2014 at 6:22 PM, Dave Page [email protected] wrote:

There are often cases where it's not feasible to use a view, and the query option was included to cater to those cases. Other FDWs may not support it, but that doesn't mean it's not a good idea and clearly people are missing it now.

On Tue, Dec 16, 2014 at 1:18 PM, Ibrar Ahmed [email protected] wrote:

Yes, this option is removed, because we can create FT on Views. Otherwise, we have to handle query option separately because we are deparsing queries based on the actual query columns. We also need to add a check to disable writable support.

AFAIK, I don't know any other FDW supporting query option Is any?

On Tue, Dec 16, 2014 at 6:12 PM, Dave Page [email protected] wrote:

Has the query option actually been removed? If so, please return it. That can be very useful, and views are not always a feasible option.

On Tue, Dec 16, 2014 at 1:08 PM, Ibrar Ahmed < [email protected]>

wrote:

I don't understand your problem correctly. Here are sequence of SQL you are using

1 - josh=# create extension mysql_fdw; 2 - josh=# create server mysql_svr foreign data wrapper mysql_fdw options (address '127.0.0.1', port '3306'); 3 - josh=# create user mapping for public server mysql_svr options (username 'josh', password ''); 5 - josh=# create foreign table bad_dates (mydate date) server mysql_svr options (query 'select * from test.bad_dates'); 6 - josh=# select * from bad_dates ; ERROR: date/time field value out of range: "0000-00-00"

In Step-5 you are using "SELECT * FROM test.bad_dates". Here you can use the table_name option, there will be no difference between the two.

If you can avoid '0000-00-00' values you can create a view and create a foreign data wrapper on that VIEW.

On Tue, Dec 16, 2014 at 5:49 AM, cimmanon < [email protected]>

wrote:

With the query option removed, what is the recommended way of dealing with MySQL's love of invalid data? I have multiple tables that contain dates like the ever popular '0000-00-00', and cleaning those up is just not an option right now.

See: http://blog.endpoint.com/2013/05/foreign-data-wrappers.html

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67094938>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67156854>

.

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67157287>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub <

https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67157920>

.

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67158363>

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/31#issuecomment-67158921 .

Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company

[tsykes]

This is a very useful enhancement. The Oracle FDW supports queries now.

[MunyuShizumi]

I know this is some 6.5 years old at this point, but thought I'd still clarify this part:

I don't understand your problem correctly. Here is the sequence of SQL you are using.

5 - josh=# create foreign table bad_dates (mydate date) server mysql_svr options (query 'select * from test.bad_dates'); 6 - josh=# select * from bad_dates ; ERROR: date/time field value out of range: "0000-00-00"

In Step-5 you are using "SELECT * FROM test.bad_dates". Here you can use the table_name option, there will be no difference between the two.

The issue is that select * from test.bad_dates will throw a FDW error instead of producing a resultset if any date or datetime column contains MySQL/MariaDB's notorious 0000-00-00 value (which seems to be used as a default value rather commonly, ex. in WordPress and/or its plugins). This can elegantly be solved in the given example by using select nullif(mydate, '0000-00-00') from test.bad_dates as the foreign table query (not clearly explained in the article).

If you want to avoid '0000-00-00' values you can create a view and create a FOREIGN TABLE on that VIEW.

Yes, though one might not have the create view privilege on the foreign server, but select privilege only (or altering the foreign server might not be appropriate for whichever code-unrelated reason). In that case, the commonly used workaround is:

1. Alter the type of all datetime (MySQL)/timestamp (PSQL) fields in the foreign table to text/varchar
2. Create a (materialized) view on the PSQL server instead, using nullif(col_name, '0000-00-00')::date or nullif(col_name, '0000-00-00 00:00:00')::timestamp for date/datetime columns respectively (add decimals to string for non-0 datetime precision)

However, this still requires a view to serve as a middleman and introduces two (perhaps unintuitive) back-and-forth casts (from date to text and back to date) in order to write a nullif somewhere. I'm not sure how much this affects performance when it comes to FDWs, but design-wise it's about as sensible as:

create table date_as_text(
  not_date text
);

create view date_as_date as
  select
      not_date::date as is_date
    from date_as_text
;
-- Why not use date type in the actual table?

IMO, it makes more sense to have this hack contained within FDW options since it's an issue related to data format translation (due to MySQL/MariaDB & PSQL formats being incompatible directly), adding a view on either end creates unnecessary clutter and requires additional explanation, whereas a nullif within the FDW query is self-explanatory.

Perhaps a quicker solution for the date/datetime issue is to add a "null if out of range" column option to the FDW instead of reimplementing the entire query table option. I'm currently not aware of any other (major/common) issue with data translation between MySQL/MariaDB and PSQL, so this might be a sufficient solution?

[surajkharage19]

Thanks for your suggestion. We will surely like to revisit this problem and do a feasibility study on this. As of now, this is not on an immediate plan, and we would surely consider this in the future per other priorities.