MyDAC update code, why does where include non primary fields

[AndreyZ]

MyDAC includes all fields to the WHERE statement only if a table doesn't have Primary Key. Please check that your tables have Primary Key.

[NoComprende]

MyDAC includes all fields to the WHERE statement only if a table doesn't have Primary Key. Please check that your tables have Primary Key.

All of the tables in the query are linked through primary keys, including the first mentioned table which automatically becomes the UpdatingTable.

If what you say is accurate Andrey don't you think you should let Dimon know given his response to my opening post on this thread 3 years ago which gives a simple example of 2 tables joined on a single key column?

[AndreyZ]

I cannot reproduce the problem. Please specify the following:
- the script to create your tables;
- the exact version of MyDAC. You can learn it from the About sheet of TMyConnection Editor;
- the exact version of your IDE;
- the exact version of MySQL server and client. You can learn it from the Info sheet of TMyConnection Editor.

[NoComprende]

Andrey, when I started this thread I mistakenly thought that TMyQuery was putting all columns in the WHERE condition for EVERY query (Dimon's reply didn't exactly put me right) so the example I gave in the opening post wasn't meant as a working example.

I've recreated a simplified version of the problem below. Hope you appreciate it as it's wasted my whole day.

DROP TABLE IF EXISTS `master`;
CREATE TABLE `master` (
`MasterKey` int(10) unsigned NOT NULL DEFAULT '0',
`DetailKey` int(10) unsigned NOT NULL DEFAULT '0',
`MasterFloatColumn` float unsigned DEFAULT '0',
PRIMARY KEY (`MasterKey`),
KEY `Other` (`DetailKey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `detail`;
CREATE TABLE `detail` (
`DetailKey` int(10) unsigned NOT NULL DEFAULT '0',
`DetailFloatColumn` float unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`DetailKey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED;

INSERT INTO master VALUES (1, 1, 0), (2, 2, 0);

INSERT INTO detail VALUES (0, 0), (1, 0);

Builder code
------------

TMyQuery *Q = new TMyQuery(this);

Q->SQL->Text="select * from Master inner join Detail using (DetailKey) order by DetailFloatColumn;";

Q->Open();
Q->Edit();
Q->FieldByName("MasterFloatColumn")->AsFloat=100;
Q->Post();

In the simple example given this won't generate an error but if you monitor the SQL generated it will be

"UPDATE Master\r\nSET\r\n MasterFloatColumn = 100\r\nWHERE\r\n DetailKey = 1 AND MasterKey = 1 AND MasterFloatColumn = 0"

Note that the WHERE condition includes all columns and not just the primary key column.

I notice that if you remove the ORDER BY in Q->SQL->Text (or even order by any master column) the correct SQL is generated with only the primary key column in the WHERE condition. I don't really get why ordering by a detail column should matter though.

MyDAC 5.90.0.60
RAD Studio 2009
MySQL server version: 5.1.30-community
MySQL client version: Direct

[AndreyZ]

The point is that MySQL doesn't return information about PRIMARY KEY of the master table if there is the ORDER BY statement in a query that contains fields from the detail table. That's why an update statement contains all fields in the WHERE clause. We cannot influence such MySQL behaviour. To solve this problem, you can use local ordering (the IndexFieldNames property) instead of server ordering (the ORDER BY statement).

[NoComprende]

The point is that MySQL doesn't return information about PRIMARY KEY of the master table if there is the ORDER BY statement in a query that contains fields from the detail table. That's why an update statement contains all fields in the WHERE clause. We cannot influence such MySQL behaviour. To solve this problem, you can use local ordering (the IndexFieldNames property) instead of server ordering (the ORDER BY statement).

A fair enough explanation Andrey, albeit 3 years late.

[AndreyZ]

Feel free to contact us if you have any further questions about MyDAC.