Raised priority as the last ~10 times I tried to edit data I was only able to do it 1 once due to column name "clashes" :(

It looks like (or I don't remember since I'd given up on using queries with this issue) that some work may have been done to this limitation allowing you to pick a column to support editing when they are ambiguous? Couple of things I noticed:

• Even though the query has only one column that has an ambiguous name across the queried tables, the dialog is still asking me to pick which table it is (i.e. select a.Created from a, b where a.id = b.id whereas both a and b have Created will ask me which Created)
• Aliasing an ambiguous column avoids the confirmation dialog but also does not allow editing of the field

I would also like to bribe the developers to work on this if possible ;)

Hmmm... I'm sure you know the underlying details much better then I do, but isn't table available via ResultSetMetaData.getTableName(colNum)?

• https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html#getTableName(int)

I just ran a quick test using MySql, Postgresql, Hsqldb (can provide the specific server & jdbc driver versions if needed) and all return the appropriate table names using that call.

While I understand that not all jdbc drivers may return that detail, can this functionality, ie automatically determining ambiguous columns and allowing editing, be enabled for those that do?

And thanks for the detailed response!

@jasonk: Thanks for the info. We will look into this further in ADS v18 and post our findings here.

I'd disagree this is a minor priority-- this issue is a show-stopper for the use of the Data Editor-- I don't have a single database where I can use this functionality as almost every table has "id" or "createdBy" or "createdTimestamp" or "FKSomething", etc that prevents me from updating any of these columns. I've given up on using Data Editor because of this limitation which is sad because that tool has the potential to be very powerful and isn't available on any other platform

@jasonk: we're continuing to look into this issue to determine whether we can come up w/ a low impact fix to include in v18. We have some code that works w/ MySQL but need to regression test against all DBs. Will keep you posted.

Thanks for the update. For what it's worth, MySql is my primary ;)

To QA, here is a test scenario example using MySQL.

create table questions (
  id int not null,
  text varchar(50) not null,
  primary key (id)
)
go
create table answers (
  id int not null,
  question_id int not null,
  text varchar(50) not null,
  primary key (id)
)
go
insert into questions 
values (1, 'question 1'), (2, 'question 2')
go
insert into answers 
values (1, 2, 'answer 1 to question 2'),
 (2, 1, 'answer 1 to question 1'),
 (3, 1, 'answer 2 to question 1')
go

In Query Analyzer, enter the following query and then choose the "Execute Edit" option. Also, in Query Builder, build the following query and then choose the "Execute Edit" option.

select a.text, a.id, q.text, q.id
from answers a, questions q
where a.question_id = q.id
go

1. If a JDBC Driver supports the ResultSetMetaData.getTableName method (e.g. MySQL, PostgreSQL, Sybase ASE, DB2 LUW), the Table Data Editor will be displayed. When you edit a column, the appropriate UPDATE statement for the correct table should be generated.

2. If a JDBC Driver doesn't support the ResultSetMetaData.getTableName method (e.g. SQL Server, Oracle), you will be prompted with the "Select Table" dialog for each column that is ambiguous. After going through all the "Select Table" dialogs, the Table Data Editor will be displayed. When you edit a column, the generated UPDATE statement is based on the table information collected with the "Select Table" dialogs.

Please verify all database types/versions.

You guys rock!

Verified for all databases and listed results here , so marking issue as verified

I've trying this in 18-preview-12 and there are some confusing behaviors.

For the example, assuming I have two tables, each with a column named DisplayText

If I execute "select a.DisplayText, b.DisplayText from a, b" then I get two dialogs asking me to pick a table-- and if I pick table a and then table b, updates to each column are correctly reflected in the preview SQL. Which is what I would expect-- it's just the two dialogs that can be confusing... are these dialogs even needed? Can't we determine which table automatically? This isn't a show stopper, just a question.

Also, is it possible to add some type of indicator that a column isn't editable (see my original request above)?

For the example, assuming I have two tables, each with a column named DisplayText

If I execute "select a.DisplayText, b.DisplayText from a, b" then I get two dialogs asking me to pick a table

If the JDBC Driver doesn't support the ResultSetMetaData.getTableName method, you will be prompted to select the table for an ambiguous column.

Our testing shows that the following JDBC Drivers support the ResultSetMetaData.getTableName method:

MySQL
PostgreSQL
Greenplum
Sybase ASE
Sybase Anywhere
Sybase IQ
SAP HANA
SQLite
TeraData
DB2 z/OS
DB2 LUW
Vertica

The following JDBC Drivers don't support the ResultSetMetaData.getTableName method:

Oracle
MS SQL Server
MS SQL Azure
Amazon Redshift
ParAccel
Informix
VoltDB
Netezza
TeraData Aster
DB2 iSeries

I'm currently testing with MySQL-- which based on your comment means I shouldn't get prompted?

You shouldn't get prompted for MySQL. I've just tested 18-preview-12 to verify it. Using my example in this comment, I don't get the "Select Table" dialog.

Ok-- so something is causing the display-- let me see if I can narrow it down to a test case and I'll get back to you!

Ok-- I verified your test case works, and I've also verified mine (still) doesn't. Still working on creating a test case

Ok (again!), here is a test case. I've been slowly narrowing it down from my actual schema.. what is weird, and hence don't trust my testing at this point, is that if I rename the tables to "table1" and "table2", it works.

CREATE TABLE QuestionMapChoice  ( 
   id int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
   text               tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
   PRIMARY KEY(id)
);

CREATE TABLE QuestionChoice  ( 
   id mediumint(8) UNSIGNED AUTO_INCREMENT NOT NULL,
   text               tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
   PRIMARY KEY(id)
);

select qc.text, qmc.text
from QuestionMapChoice qmc, QuestionChoice qc
where qmc.id = qc.id;

I found out what is going on. For MySQL, ResultSetMetaData.getTableName returns the table name in lower case. That's why it works for "table1" and "table2" but doesn't for "QuestionMapChoice" and "QuestionChoice". Since ResultSetMetaData.getTableName returns "questionmapchoice" and "questionchoice", we can't match the tables and therefore the prompt is displayed.

Good news. I hope this means you can fix it too ;)

Fixed the table name matching algorithm to check whether table names are case sensitive or not for MySQL. We check the "lower_case_table_names" system variable. If its value is 0, table names are case sensitive. If table names are case sensitive, we'll do an "equals" comparison. If table names are case insensitive, we'll do an "equalsIgnoreCase" comparison.

Notes to QA: The problem reported in this comment is only reproducible if the MySQL server is on Windows. It is not reproducible if the MySQL server is on Linux. The code changes are specific to MySQL only.

Jason: I've logged a separate issue for your second request to add an indicator that a column is not editable in the Table Data Editor. It's issue #14963.

Thanks Jenny!

Also note, I saw the reference about my issue being only reproducible on Windows-- it's also reproducible on a Mac :)

And thanks for the second ticket!

Verified in 18.0.0-preview-43 when the MySQL in on Windows - Please refer attached screenshot

Verified in ADS 18.0.0-preview-82,when the MySQL server is installed on Windows -  VerifiedIn-Preview82.png