Issue Details (XML | Word | Printable)

Key: ODBC-217
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Alexander Potapchenko
Reporter: Nathan Waite
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
ODBC Driver

Union in Access generates bad SQL

Created: 10/Mar/20 08:40 PM   Updated: 10/Mar/20 08:40 PM
Component/s: None
Affects Version/s: 2.0.5
Fix Version/s: None

Environment: Windows 10, ODBC 2.0.5.156, local Firebird 2.5.9 / 3.0.5


 Description  « Hide
Is this an ODBC bug? When I union two Access queries [that have criteria] bad SQL is generated.

Create a table in Firebird and link to it in Access. I created table TEST with integer ID and varchar VAL.

In Access create two queries to your linked table selecting a single row each. (ID=1 and ID=2) Now create a union query.

select * from qry_Test1
UNION
select * from qry_Test2;

You'll receive an ODBC error due to a malformed statement:

SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 1 ) UNION (SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 2 ) )

Interestingly if you remove the first query's criteria, it no longer wraps the 2nd query in parenthesis and works ok.

SELECT "ID" ,"VAL" FROM "TEST" UNION SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 2 )

Also, try changing the two Access queries to group by all fields. Now the first query has bad syntax.

SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 1 GROUP BY "ID" ,"VAL" ) UNION (SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 2 ) GROUP BY "ID" ,"VAL" )


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.