MySQL view definition oddity
-
Much of my current job involves combining data from various sources into one SQL Server database. One of the sources is a MySQL database and I have noticed something odd. One of the first steps I take when I get access to a new data source is to investigate the schema. As a fan of ADO.net and database-agnosticism, my main tools for doing this are: System.Data.Common.DbConnection.GetSchema() , System.Data.Common.DbConnection.GetSchema(string) , and System.Data.IDataReader.GetSchemaTable() which every compliant ADO.net provider should implement. The way I use GetSchemaTable is to cobble up a
SELECT * FROM name WHERE 0=1query for each table and view, call ExecuteReader, call GetSchemaTable, and display the results. TheWHERE 0=1clause is intended to keep the server from doing a bunch of needless work, but just return an empty dataset. This works, but I have since found that it hides problems -- mostly related to views that are out of sync with their sources. So I removed the WHERE clause and was surprised that one column in one of the MySQL views was reported as Int64 rather than Int32 as it is when I use the WHERE clause. I got the latest version of the MySQL Connector/net and the situation persists. What I then found, by usingGetSchema("VIEWS"), is that the column is defined likeIF((id IS NOT NULL),id,NULL) AS 'id'(id is involved in a JOIN among several tables). I don't have access the tables so I don't know what type the actual field is. And I see comments on the Web thatIFcan return unexpected types. I have been unable to reproduce the oddity in my own test database. But my question is, "why use theIFfunction in this case?" How is this better than just returningidwhether it's NULL or not? Is there some historical reason? -
Much of my current job involves combining data from various sources into one SQL Server database. One of the sources is a MySQL database and I have noticed something odd. One of the first steps I take when I get access to a new data source is to investigate the schema. As a fan of ADO.net and database-agnosticism, my main tools for doing this are: System.Data.Common.DbConnection.GetSchema() , System.Data.Common.DbConnection.GetSchema(string) , and System.Data.IDataReader.GetSchemaTable() which every compliant ADO.net provider should implement. The way I use GetSchemaTable is to cobble up a
SELECT * FROM name WHERE 0=1query for each table and view, call ExecuteReader, call GetSchemaTable, and display the results. TheWHERE 0=1clause is intended to keep the server from doing a bunch of needless work, but just return an empty dataset. This works, but I have since found that it hides problems -- mostly related to views that are out of sync with their sources. So I removed the WHERE clause and was surprised that one column in one of the MySQL views was reported as Int64 rather than Int32 as it is when I use the WHERE clause. I got the latest version of the MySQL Connector/net and the situation persists. What I then found, by usingGetSchema("VIEWS"), is that the column is defined likeIF((id IS NOT NULL),id,NULL) AS 'id'(id is involved in a JOIN among several tables). I don't have access the tables so I don't know what type the actual field is. And I see comments on the Web thatIFcan return unexpected types. I have been unable to reproduce the oddity in my own test database. But my question is, "why use theIFfunction in this case?" How is this better than just returningidwhether it's NULL or not? Is there some historical reason?Is this
idexisting in more than one table and is therefore used as a join condition in the view? About your question, why the use of theIFfunction, I can only speculate that the original developer had a brain fart.Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
Is this
idexisting in more than one table and is therefore used as a join condition in the view? About your question, why the use of theIFfunction, I can only speculate that the original developer had a brain fart.Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
Jörgen Andersson wrote:
used as a join condition in the view?
Yes.
Jörgen Andersson wrote:
had a brain fart
That's what I'm thinking, but I'm no MySQL expert.
-
Jörgen Andersson wrote:
used as a join condition in the view?
Yes.
Jörgen Andersson wrote:
had a brain fart
That's what I'm thinking, but I'm no MySQL expert.
PIEBALDconsult wrote:
but I'm no MySQL expert
Neither am I, I have never worked with MySQL. But I have an idea that might explain the weird behavior. In Oracle 10g you could define a query as:
SELECT ID
FROM A,B
WHERE A.ID = B.IDNote that you didn't need to specify which table the column should come from if it existed in the join condition. What happens if the ID Columns have different types, will there be an implicit cast? And which table will the selected column come from? In Oracle 11g you always have to specify the table.column
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
PIEBALDconsult wrote:
but I'm no MySQL expert
Neither am I, I have never worked with MySQL. But I have an idea that might explain the weird behavior. In Oracle 10g you could define a query as:
SELECT ID
FROM A,B
WHERE A.ID = B.IDNote that you didn't need to specify which table the column should come from if it existed in the join condition. What happens if the ID Columns have different types, will there be an implicit cast? And which table will the selected column come from? In Oracle 11g you always have to specify the table.column
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
Jörgen Andersson wrote:
you always have to specify the table.column
Yes, but that's not the issue, so I left it out for simplicity.