Firedac Sqlite bug?

[John Terwiske 4]

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.

Project1.dpr

Project1.dproj

Unit1.dfm

Unit1.pas

[Koru 2]

In 10.4.1 and 10.2 update 3, it executes ok

Edited November 14, 2020 by Koru

[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 November 14, 2020 by Guest

[John Terwiske 4]

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. 

[John Terwiske 4]

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.

[Guest]

   

Edited November 16, 2020 by Guest

[John Terwiske 4]

I've reported this as a bug: 

https://quality.embarcadero.com/browse/RSP-31670