Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
1. when we select some but not all columns ex, Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
2. When we have aliases in column names: ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
3. When we have alias in table names: The name of the table in create statement should be alias name or original table name?
4. When we have JOIN queries with more than one tables: ex: SELECT * FROM (SELECT actor_id FROM actor as e INNER JOIN address as g on e.actor_id = g.city_id) as t1 Currently if we try to export some rows from the above query, it export nothing it goes on edit sql screen.
Please share your thoughts.
Thanks and Regards - Smita
2014-08-15 9:54 GMT+02:00 Smita kumarismita62@gmail.com:
Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
- when we select some but not all columns ex,
Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
Hi,
I agree that the "CREATE TABLE" should have the same structure that the data to "INSERT".
- When we have aliases in column names:
ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
I agree that we should have the field alias as the column name. With this feature, the user can keep the original name by not using aliases.
- When we have alias in table names:
The name of the table in create statement should be alias name or original table name?
Again, I agree with table alias as the name of new table, for the same reason.
- When we have JOIN queries with more than one tables:
ex: SELECT * FROM (SELECT actor_id FROM actor as e INNER JOIN address as g on e.actor_id = g.city_id) as t1 Currently if we try to export some rows from the above query, it export nothing it goes on edit sql screen.
I ran the query, select some lines and clicked on the link "Export" at the bottom of the table and had a file to download. I used the "Quick" export method.
BUT… It seems that the content of the file is not the expected result. I don't have any "INSERT" queries… After the "CREATE TABLE" I have this: -- Error reading data: (#1054 - Unknown column 'actor.actor_id' in 'where clause')
Do we have a bug into the export ?
Hugues.
Please share your thoughts.
Thanks and Regards
- Smita
Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
On Fri, Aug 15, 2014 at 3:45 PM, Hugues Peccatte hugues.peccatte@gmail.com wrote:
2014-08-15 9:54 GMT+02:00 Smita kumarismita62@gmail.com:
Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
- when we select some but not all columns ex,
Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
Hi,
I agree that the "CREATE TABLE" should have the same structure that the data to "INSERT".
- When we have aliases in column names:
ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
I agree that we should have the field alias as the column name. With this feature, the user can keep the original name by not using aliases.
- When we have alias in table names:
The name of the table in create statement should be alias name or original table name?
Again, I agree with table alias as the name of new table, for the same reason.
- When we have JOIN queries with more than one tables:
ex: SELECT * FROM (SELECT actor_id FROM actor as e INNER JOIN address as g on e.actor_id = g.city_id) as t1 Currently if we try to export some rows from the above query, it export nothing it goes on edit sql screen.
I ran the query, select some lines and clicked on the link "Export" at the bottom of the table and had a file to download. I used the "Quick" export method.
BUT… It seems that the content of the file is not the expected result. I don't have any "INSERT" queries… After the "CREATE TABLE" I have this: -- Error reading data: (#1054 - Unknown column 'actor.actor_id' in 'where clause')
Do we have a bug into the export ?
This is the Bug #4444.
Hugues.
Please share your thoughts.
Thanks and Regards
- Smita
On Fri, Aug 15, 2014 at 3:45 PM, Hugues Peccatte hugues.peccatte@gmail.com wrote:
2014-08-15 9:54 GMT+02:00 Smita kumarismita62@gmail.com:
Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
- when we select some but not all columns ex,
Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
Hi,
I agree that the "CREATE TABLE" should have the same structure that the data to "INSERT".
- When we have aliases in column names:
ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
I agree that we should have the field alias as the column name. With this feature, the user can keep the original name by not using aliases.
- When we have alias in table names:
The name of the table in create statement should be alias name or original table name?
Again, I agree with table alias as the name of new table, for the same reason.
Hi Hugues, Thanks. As Chirayu mentioned RFE #759 http://sourceforge.net/p/phpmyadmin/feature-requests/759/ seems to fix table and column aliasing issue(point 2 and 3), I tested with current master. Sorry, earlier I was working with QA_4_2 branch. But CREATE table has the same structure as original structure even if SELECT have some but not all the columns, I'm not sure if that is intended.
- When we have JOIN queries with more than one tables:
ex: SELECT * FROM (SELECT actor_id FROM actor as e INNER JOIN address as g on e.actor_id = g.city_id) as t1 Currently if we try to export some rows from the above query, it export nothing it goes on edit sql screen.
I ran the query, select some lines and clicked on the link "Export" at the bottom of the table and had a file to download. I used the "Quick" export method.
BUT… It seems that the content of the file is not the expected result. I don't have any "INSERT" queries… After the "CREATE TABLE" I have this: -- Error reading data: (#1054 - Unknown column 'actor.actor_id' in 'where clause')
Do we have a bug into the export ?
Looks like that. For me, when I select some lines from query result and click on export it shows me a screen to run the query with query editor, see 1.png. I'm using Firefox-28, Ubuntu 12.04
- Smita
Hugues.
Please share your thoughts.
Thanks and Regards
- Smita
Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
On Fri, Aug 15, 2014 at 11:48 PM, Smita kumarismita62@gmail.com wrote:
On Fri, Aug 15, 2014 at 3:45 PM, Hugues Peccatte < hugues.peccatte@gmail.com> wrote:
2014-08-15 9:54 GMT+02:00 Smita kumarismita62@gmail.com:
Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
- when we select some but not all columns ex,
Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
Hi,
I agree that the "CREATE TABLE" should have the same structure that the data to "INSERT".
- When we have aliases in column names:
ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
I agree that we should have the field alias as the column name. With this feature, the user can keep the original name by not using aliases.
- When we have alias in table names:
The name of the table in create statement should be alias name or original table name?
Again, I agree with table alias as the name of new table, for the same reason.
Hi Hugues, Thanks. As Chirayu mentioned RFE #759 http://sourceforge.net/p/phpmyadmin/feature-requests/759/ seems to fix table and column aliasing issue(point 2 and 3), I tested with current master. Sorry, earlier I was working with QA_4_2 branch. But CREATE table has the same structure as original structure even if SELECT have some but not all the columns, I'm not sure if that is intended.
That is intended, the reason is that its not only about CREATE table or INSERT queries. There are many other factors such as PROCEDURES, TRIGGERS and we may not be able to remove those columns (which are not in select query) as they might break them i.e. we have to sacrifice one thing over the other. We can't force the user to accept one thing sacrificing the other. So, its better to include complete structure (everything) though it may not be good for import as it is. And leave that up to the user to make desired changes.
On Fri, Aug 15, 2014 at 1:24 PM, Smita kumarismita62@gmail.com wrote:
Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
We don't have to worry about aliases of table/column names here, just insert queries should get generated with original names and column selected in query. Create table can have complete table structure.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
- when we select some but not all columns ex,
Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
I remember a discussion over this with Madhura. See https://sourceforge.net/p/phpmyadmin/bugs/4443/
- When we have aliases in column names:
ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
- When we have alias in table names:
The name of the table in create statement should be alias name or original table name?
For 2nd and 3rd questions, This feature is implemented and will be present in 4.3. See RFE #755 & RFE #759.
- When we have JOIN queries with more than one tables:
ex: SELECT * FROM (SELECT actor_id FROM actor as e INNER JOIN address as g on e.actor_id = g.city_id) as t1 Currently if we try to export some rows from the above query, it export nothing it goes on edit sql screen.
I also remember one discussion over this with Marc. I am quoting his reply here "exporting a join query makes no sense to me". Also see bug #4445.
Please share your thoughts.
Thanks and Regards
- Smita
On Fri, Aug 15, 2014 at 3:46 PM, Chirayu Chiripal < chirayu.chiripal@gmail.com> wrote:
On Fri, Aug 15, 2014 at 1:24 PM, Smita kumarismita62@gmail.com wrote:
Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
We don't have to worry about aliases of table/column names here, just insert queries should get generated with original names and column selected in query. Create table can have complete table structure.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
- when we select some but not all columns ex,
Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
I remember a discussion over this with Madhura. See https://sourceforge.net/p/phpmyadmin/bugs/4443/
- When we have aliases in column names:
ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
- When we have alias in table names:
The name of the table in create statement should be alias name or original table name?
For 2nd and 3rd questions, This feature is implemented and will be present in 4.3. See RFE #755 & RFE #759.
Hi Chirayu Thanks. I was testing with QA_4_2 branch. point 2,3 seems to working on current master.
- When we have JOIN queries with more than one tables:
ex: SELECT * FROM (SELECT actor_id FROM actor as e INNER JOIN address as g on e.actor_id = g.city_id) as t1 Currently if we try to export some rows from the above query, it export nothing it goes on edit sql screen.
I also remember one discussion over this with Marc. I am quoting his reply here "exporting a join query makes no sense to me". Also see bug #4445.
In that case, When we have results from Join query we should not show the
'export' option link. what you think ?
- Smita
Please share your thoughts.
Thanks and Regards
- Smita
-- Regards, Chirayu Chiripal phpMyAdmin Intern - Google Summer of Code 2014 https://chirayuchiripal.wordpress.com/
Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
On Fri, Aug 15, 2014 at 11:53 PM, Smita kumarismita62@gmail.com wrote:
On Fri, Aug 15, 2014 at 3:46 PM, Chirayu Chiripal < chirayu.chiripal@gmail.com> wrote:
On Fri, Aug 15, 2014 at 1:24 PM, Smita kumarismita62@gmail.com wrote:
Hi,
I came across the bug #4444 http://sourceforge.net/p/phpmyadmin/bugs/4444/ related to export feature for queries. I'm not sure how exactly this feature is supposed to behave if we are exporting rows with some but not all columns and also in case we have aliases and even when we have join queries.
We don't have to worry about aliases of table/column names here, just insert queries should get generated with original names and column selected in query. Create table can have complete table structure.
Assume we have tables: actor (actor_id, first_name, last_name, city_id) with actor_id as primary key. address (address_id, adress, city_id, city_id, district, postal_code, phone) with address_id as primary key and phone have not null unique constraints.
- when we select some but not all columns ex,
Select address_id, address from address: when exported few of the rows, it have create table statement for table 'address' with all the original columns not just these two. and data from just these two columns in the Insert statements. so here when the same exported file, we wish to import back will not work as phone have unique constraint and we are not inserting the phone values. Also If user is seeing just two of the columns in the table structure at screen so they might be doing export thinking that it will be exporting the same structure as on the screen.
I remember a discussion over this with Madhura. See https://sourceforge.net/p/phpmyadmin/bugs/4443/
- When we have aliases in column names:
ex: select actor_id, first_name as fname from actor; So when we export few of the resultant rows, should the create table statement have a column name first_name or fname ?. As I think of it, It should be fname, if user have in mind that he is exporting the structure of the table he is looking at screen.
- When we have alias in table names:
The name of the table in create statement should be alias name or original table name?
For 2nd and 3rd questions, This feature is implemented and will be present in 4.3. See RFE #755 & RFE #759.
Hi Chirayu Thanks. I was testing with QA_4_2 branch. point 2,3 seems to working on current master.
- When we have JOIN queries with more than one tables:
ex: SELECT * FROM (SELECT actor_id FROM actor as e INNER JOIN address as g on e.actor_id = g.city_id) as t1 Currently if we try to export some rows from the above query, it export nothing it goes on edit sql screen.
I also remember one discussion over this with Marc. I am quoting his reply here "exporting a join query makes no sense to me". Also see bug #4445.
In that case, When we have results from Join query we should not show the
'export' option link. what you think ?
Hi, we might need that link to export in formats other than the SQL.
-
Chirayu Chiripal -
Hugues Peccatte -
Smita