0000177: Type mismatch error on runtime - Common Table Expression (CTE)

Notes
(0000579) cipto_kh (developer) 2009-09-26 05:51	Is there any solution for this problem with CTE (Common Table Expression) query in Firebird?

(0000581) mdaems (administrator) 2009-10-03 01:08	cipto, I just took some time to look at your CTE's. But let's start with a disclaimer : I'm not running, nor planning tu run a FB server. So I didn't try the sample. That's for a FB user to try! (I might try a complete FB embedded case, including everything (including dll, extra needed files, ...) so I could just hit F9. At first look there's nothing wrong with it. I tried to add some sqlanalyzer test. procedure TZTestInterbaseStatementAnalyser.TestCTESelect; var SelectSchema: IZSelectSchema; ss:string; begin SelectSchema := FAnalyser.DefineSelectSchemaFromQuery(FTokenizer, 'with recursive detail as (select a from b) SELECT Field FROM Table'); CheckNotNull(SelectSchema); ss := SchemaToString(SelectSchema); CheckEquals('SS:[FR:Field][TR:Table]', SchemaToString(SelectSchema)); SelectSchema := FAnalyser.DefineSelectSchemaFromQuery(FTokenizer, 'with recursive t2 as (select a from b) SELECT f1, f2, t1.f3 from t2 inner join t1 on t2.f3 = t1.f3, t3'); CheckNotNull(SelectSchema); CheckEquals('SS:[FR:f1,FR:f2,FR:t1.f3][TR:t2,TR:t1,TR:t3]', SchemaToString(SelectSchema)); end; These are 2 selects from the test suite that I 'CTE-ized' by adding the 'with...(...)' part. Hoping this would result in the parser going mad, so I could fix that. Unfortunately, this wasn't true. So the metadata will be searched correctly. BUT there will be no metadata available on the CTE-table. That's the main difference between your qr2 and qr3. In qr3 the field metadata of all tables can be retrieved because they are real tables. For qr2 the default string field type will be chosen. This shouldn't be an issue when only doing readonly operations. Well, Where could you start finding the reason for the error? Right at the place where it shows up. What field types is he trying to compare? On what field? Where do these types come from? Metadata? Data descriptions from the resultset itself? (ResultsetMetadata or DatabaseMetadata) Mark

(0000587) cipto_kh (developer) 2009-10-14 06:41	I upload again the Delphi file and the database to produce that error, sorry if you cannot just run that file because the fbembed.dll must be rename to fbclient.dll I think, so in this new attachment I also included fbclient.dll file. The other embed file I cannot upload because the size of upload file is limited in this mantis bugtracker (it was 2MB). The new file I upload is "All In One.7z" The error is raise when we go to second record in master grid data (the master grid data is the TDBGird in top). Anyway thank's for the respond Mark :) I know you are not firebird user, but maybe the other firebird user here can try too.

(0000617) mdaems (administrator) 2009-12-07 11:19	Hi cipto, I already managed to reproduce the problem yesterday evening. Now i'll have to debug this in depth. No fun... Seems the problem happens after the prepared query qr2 is executed again with the different bind variables coming from qr1. First impression is the structure of the data coming from the prepared query may differ between executions. (Making refresh logic react like this) I hope this isn't true as that would mean two exactly the same queries might return differently structured resultsets depending on the parameters bound. Mark

(0000618) cipto_kh (developer) 2009-12-08 05:30	Ok, it's nice if you also can reproduce that error. Yes I know it's difficult because it seem not consistent datatype, and also maybe it's the way that we can speed up Zeos performance coz it looks like zeos always retrieve the metadata each time we move to the other record although the dataset already opened.

(0000633) mdaems (administrator) 2009-12-20 21:27	Cipto, I may have found a solution. You know, it seems only a Delphi problem, where initfielddefs is also called while doing the bindfields procedure inside InternalOpen. Try this patch: Index: ZAbstractRODataset.pas =================================================================== --- ZAbstractRODataset.pas (revision 738) +++ ZAbstractRODataset.pas (working copy) @@ -1623,7 +1623,8 @@ end; { Initializes field and index defs. } - InternalInitFieldDefs; + if not FRefreshInProgress then + InternalInitFieldDefs; if DefaultFields and not FRefreshInProgress then CreateFields; It doesn't break the current test suite and the error is gone. must admit I don't know what EXACTLY is going wrong, but it only went wrong on Delphi. Tried on Lazarus, but there the error didn't exist. Mark

(0000635) mdaems (administrator) 2009-12-21 20:58	SVN Rev 746. Please confirm after testing this small patch. http://fisheye2.atlassian.com/changelog/zeos/?cs=746 [^] So I can add it to 6.6-patches as well. Mark

Issue History
Date Modified	Username	Field	Change
2009-04-08 12:03	cipto_kh	New Issue
2009-04-08 12:03	cipto_kh	File Added: Zeos Bug.7z
2009-09-26 05:51	cipto_kh	Note Added: 0000579
2009-10-03 01:08	mdaems	Note Added: 0000581
2009-10-14 06:33	cipto_kh	File Added: All In one.7z
2009-10-14 06:41	cipto_kh	Note Added: 0000587
2009-11-27 01:10	mdaems	Summary	Type mismatch error on runtime => Type mismatch error on runtime - Common Table Expression (CTE)
2009-12-07 11:19	mdaems	Note Added: 0000617
2009-12-08 05:30	cipto_kh	Note Added: 0000618
2009-12-20 21:27	mdaems	Note Added: 0000633
2009-12-21 20:58	mdaems	Status	new => resolved
2009-12-21 20:58	mdaems	Fixed in Version	=> Testing Branch (SVN)
2009-12-21 20:58	mdaems	Resolution	open => fixed
2009-12-21 20:58	mdaems	Assigned To	=> mdaems
2009-12-21 20:58	mdaems	Note Added: 0000635
2009-12-23 01:14	mdaems	Fixed in Version	Testing Branch (SVN) => Trunk (SVN)
2009-12-27 20:15	mdaems	Fixed in Version	Trunk (SVN) => 6.6-Patches Branch (SVN)
2009-12-30 21:59	mdaems	Fixed in Version	6.6-Patches Branch (SVN) => 6.6.6 Stable
2009-12-30 22:01	mdaems	Status	resolved => closed

Relationships