System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: efdaed93a201f7f5ca07829ad7d638f806b87385
Title: "BinaryGUID=False" and "Case" clause
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Not_A_Bug
Last Modified: 2013-06-03 18:26:27
Version Found In: 1.0.86.0
User Comments:
anonymous added on 2013-06-03 01:17:35:
When using "BinaryGUID=False" in connection string, as we knows, a Guid type data will be stored as string in sqlite and System.Data.SQLite auto convert between Guid and string.

It meens,when get a Guid data from sqlite stored as string, System.Data.SQLite convert implicitly it from string to Guid.But when using "Case" clause in "Select" to get a Guid data stored as string,System.Data.SQLite will directly return a string data! Is it a designed behavior?

mistachkin added on 2013-06-03 02:02:58:
Do you have an example SQL query that demonstrates the issue?  Perhaps a small C# example as well?

anonymous added on 2013-06-03 03:26:14:
select
v.ID,
v.Version,
v.DName,
case
when
ov.OperationID = '31c474fa-9ca5-44c6-b139-de7b4943b0bb' 
then
ov.ID
else
null
end RelateID
from
View v
left join OperationView ov on
v.ID = ov.ViewID                       
where                    
v.PrismModuleID = '85f16f29-ac5f-4df0-89e9-4f383fc1b650'                        
order by
v.Position

anonymous added on 2013-06-03 03:30:14:
Table View

Name	Declared Type	Type	Size	Precision	Not Null	Not Null On Conflict	Default Value	Collate	Position	Old Position
ID	guid	guid	0	0	True	""	""	""	0	0
Version	int	int	0	0	True	""	""	""	1	1
Name	varchar(25)	varchar	25	0	True	""	""	""	2	2
DName	nvarchar(50)	nvarchar	50	0	True	""	""	""	3	3
PrismModuleID	guid	guid	0	0	True	""	""	""	4	4
Position	int	int	0	0	True	""	""	""	5	5






Table Operationview

Name	Declared Type	Type	Size	Precision	Not Null	Not Null On Conflict	Default Value	Collate	Position	Old Position
ID	guid	guid	0	0	True	""	""	""	0	0
OperationID	guid	guid	0	0	True	""	""	""	1	1
ViewID	guid	guid	0	0	True	""	""	""	2	2

mistachkin added on 2013-06-03 07:58:59:
The BinaryGUID connection string properly currently only impacts the parameter
binding for parameter values declared to be of type GUID.  Changing this behavior
would not be backwards compatible.

In theory, a brand new connection string property could be added to support some
data type conversion to/from GUID; however, this is the first time somebody has
requested such a feature.

anonymous added on 2013-06-03 08:18:44:
I can't understand what you said.

I just want the rerurn ov.ID(as RelateID) data type is "Guid" not a "string",because ov.ID is declared to be of type GUID(though stored as string inner sqlite engine).

If using "union",things is ok.

select
*
from
(select
v.ID,
v.Version,
v.DName,
v.Position,
null RelateID
from
View v
left join OperationView ov on
v.ID = ov.ViewID 
where                    
v.PrismModuleID = '85f16f29-ac5f-4df0-89e9-4f383fc1b650'
and
((ov.OperationID <> '31c474fa-9ca5-44c6-b139-de7b4943b0bb')
  or  
  (ov.OperationID is null))  
union
select
v.ID,
v.Version,
v.DName,
v.Position,
ov.ID RelateID
from
View v
left join OperationView ov on
v.ID = ov.ViewID                       
where                    
v.PrismModuleID = '85f16f29-ac5f-4df0-89e9-4f383fc1b650'                        
and
ov.OperationID = '31c474fa-9ca5-44c6-b139-de7b4943b0bb') u
order by
u.Position


So,I think System.Data.SQLite doesn't execute a implicitly convention from a string to Guid in "case" clause as normal.If you think is a designed behavior,that's all.