[ODBC-57] MS Access + MariaDB connector crashes when running specific query Created: 2016-09-29  Updated: 2016-10-28  Resolved: 2016-10-28

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: None
Affects Version/s: 2.0.11, 2.0.12
Fix Version/s: 3.0.0

Type: Bug Priority: Major
Reporter: IT Particip Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: None
Environment:

MS Access 2010 + MariaDB connector (Windows 7) + MariaDB 10.1.17 (Debian 8)


 Description   

In my company we use a MS Access application with a MariaDB Galera cluster. For the moment we use the MySQL connector, but we would like to migrate to the MariaDB connector at some point.

Therefore we are performing some test right now and realised that the development version of our application fails to open one specific window. The changes made in that window, and which definitely are causing the issue (the simple query works), are just a query change from this simple query:

SELECT id, titleen as title FROM tblorg

to this query:

SELECT tblomain.id, tblomain.title
 
FROM (
 
SELECT tbloaux1.id, tbloaux1.titleen as title 
FROM tblorg tbloaux1 
UNION 
SELECT tbloaux2.id, tbloaux2.titlelongen as title 
FROM tblorg tbloaux2)  
AS tblomain
 
ORDER BY tblomain.title;

When including this last query, the application fails to open the window correctly and crashes.

I am aware that this query is somehow weird, but this is quite a special request from our users and access sql is also a bit weird for certain things sometimes.

Thanks in advance for having a look to this issue.

 Comments   
Comment by IT Particip [ 2016-09-29 ]

Just a heads up. With a simplified version of the query, it does not seem to work either:

SELECT tbloaux1.id, tbloaux1.titleen as title 
FROM tblorg tbloaux1 
UNION 
SELECT tbloaux2.id, tbloaux2.titlelongen as title 
FROM tblorg tbloaux2
 
order by title

Comment by Lawrin Novitsky [ 2016-09-29 ]

Thank you for your report!
Even weird queries should behave and not crash applications.

Comment by Lawrin Novitsky [ 2016-10-05 ]

Would it be possible to provide structures of tables in the query?
Thank you in advance

Comment by IT Particip [ 2016-10-05 ]

Yes, of course. There is only one table being used, although referenced a few times. This is the structure:

CREATE TABLE `tblorg` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `niveau` int(11) DEFAULT NULL,
 
  `mother` int(11) DEFAULT NULL,
 
  `titleEn` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `titleLongEn` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
 
  `country` int(11) DEFAULT NULL,
 
  `type` int(11) DEFAULT NULL,
 
  `titleDe` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `titleLongDe` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
 
  `titleFr` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `titleLongFr` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
 
  `titleEs` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `titleLongEs` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
 
  `titlePt` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `titleLongPt` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
 
  `IsLegalEntity` tinyint(4) NOT NULL DEFAULT '0',
 
  `statute` int(2) DEFAULT NULL,
 
  `since` int(4) DEFAULT NULL,
 
  `affiliation` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `street` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
 
  `streetzip` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
 
  `pobox` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
 
  `poboxZip` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
 
  `town` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `www` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
 
  `remark` text CHARACTER SET latin1,
 
  `phone` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `fax` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `email` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `staffFull` int(7) DEFAULT NULL,
 
  `staffPart` int(7) DEFAULT NULL,
 
  `bank` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
 
  `account` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `bankcode` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
 
  `swift` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
 
  `debtornum` varchar(10) DEFAULT NULL,
 
  `creditornum` varchar(10) DEFAULT NULL,
 
  `VATnum` varchar(20) DEFAULT NULL,
 
  `CodeDate` datetime DEFAULT NULL,
 
  `CodePerson` datetime DEFAULT NULL,
 
  `director` int(11) DEFAULT NULL,
 
  `orgid` int(11) DEFAULT NULL,
 
  `organisationsid` int(11) DEFAULT NULL,
 
  `clientsid` int(11) DEFAULT NULL,
 
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `uq_tblo_debtornum` (`debtornum`),
 
  UNIQUE KEY `uq_tblo_creditornum` (`creditornum`),
 
  UNIQUE KEY `uq_tblo_VATnum` (`VATnum`),
 
  KEY `oldId` (`orgid`),
 
  KEY `fk_tblorg_country_tblco` (`country`),
 
  KEY `fk_tblorg_type_tbltp` (`type`),
 
  CONSTRAINT `fk_tblorg_country_tblco` FOREIGN KEY (`country`) REFERENCES `tblcountry` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 
  CONSTRAINT `fk_tblorg_type_tbltp` FOREIGN KEY (`type`) REFERENCES `tbltype` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 
) ENGINE=InnoDB AUTO_INCREMENT=9869 DEFAULT CHARSET=utf8;

Comment by Lawrin Novitsky [ 2016-10-28 ]

The fix iand the bugfix have been pushed. The reason was that MS Access adds parenthesis around each SELECT in the UNION statement. And that prevented connector to correctly establish type of query(needed for some optimizations)
Unfortunately it haven't made to 2.0.13(which has been already built), and appear first in 3.0.0alpha

Comment by IT Particip [ 2016-10-28 ]

Great, thanks! We will keep on testing the connector with the hope of being able to migrate at some point.

All the best

Generated at Thu Feb 08 03:25:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.