Jump to content
John Terwiske

Firedac Sqlite bug?

Recommended Posts

In Delphi Rio 10.3.3 (I've not tested this on other versions) there seems to be a parsing bug with this recursive CTE:

WITH RECURSIVE example1_cte (Id, No, ParsedText, Unparsed) AS
(
  SELECT Id,    
         0 as No, 
         '' as ParsedText,
         TextToParse || ',' as TextToParseWithTrailingComma 
    FROM test1
    
  UNION ALL       -- "ALL" is not strictly necessary in this example
    SELECT Id,    
           No+1,  -- increment the counter for this row
           SUBSTR(Unparsed, 0, INSTR(Unparsed, ',') ),
           SUBSTR(Unparsed, INSTR(Unparsed, ',') + 1) 
      FROM example1_cte
      WHERE Unparsed <> ''  
)
    -- SELECT from the recursive cte
  SELECT Id, No, TRIM(ParsedText) as ParsedText
    FROM example1_cte
    WHERE ParsedText <> ''
    ORDER BY Id ASC, No ASC;

When executed a Firedac error message appears: [FireDac][Phys][SQLite] Error: no such table column: example1_cte.Id.

 

And yet, if the line below the UNION ALL is changed to SELECT Id+0,  (I.e. just adding a zero to Id), the error message goes away, and the CTE works correctly.

 

btw, this query works fine without the "+0" in other SQLite query processors (SQLiteStudio).

 

I think this is a bug, but cannot locate it in JIRA.

 

Attached is a quick and dirty example project.  (Change the FDQuery1 to remove the "+0" to see the error.)

 

I don't have 10.4 so not sure this still exists.

image.png

Project1.dpr

Project1.dproj

Unit1.dfm

Unit1.pas

Share this post


Link to post

In 10.4.1 and 10.2 update 3, it executes ok

Edited by Koru

Share this post


Link to post
Guest

fail... interesting.. using this syxtax works too!

...
WITH RECURSIVE example1_cte (Id, No, ParsedText, Unparsed) AS
(
  SELECT Id,    
         0 as No, 
         '' as ParsedText,
         TextToParse || ',' as TextToParseWithTrailingComma 
    FROM test1
    
  UNION ALL       -- "ALL" is not strictly necessary here
    SELECT Id,  -- 
           No+1,  -- increment the counter for this row
           SUBSTR(Unparsed, 0, INSTR(Unparsed, ',') ),
           SUBSTR(Unparsed, INSTR(Unparsed, ',') + 1) 
      FROM example1_cte
      WHERE Unparsed <> ''  
)

    -- SELECT from the recursive cte   ( now, ID+'' here )
  SELECT Id+'', No, TRIM(ParsedText) as ParsedText  
    FROM example1_cte
    WHERE ParsedText <> ''
    ORDER BY Id ASC, No ASC;

  caption:= GetEnumName(TypeInfo(TFieldType), ord(FDQuery1.FieldByName('ID+''''').DataType));  // = LongInt

Edited by Guest

Share this post


Link to post
14 hours ago, emailx45 said:

fail... interesting.. using this syxtax works too!

  caption:= GetEnumName(TypeInfo(TFieldType), ord(FDQuery1.FieldByName('ID+''''').DataType));  // = LongInt

Thanks for the confirmation in 10.3.3. 

Share this post


Link to post
21 hours ago, Koru said:

In 10.4.1 and 10.2 update 3, it executes ok

Odd that there would be a regression in 10.3.3.  I'm looking forward to 10.4.1, most especially for the ability to non-statically link to SQLite library.

Share this post


Link to post
Guest

image.thumb.png.22c631bfe75023da61b767a040675fce.png   image.png.8ac2ad4c4b78b585f90dacf1acd06494.png

Edited by Guest

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×