Base substring problem in where clause

mathteacher · November 8, 2020, 2:20am

Hello

I have been having issues with a query in LO base that has the substring function in a where clause. I think I have managed to isolate the problem and would like to submit a bug report. I was reading through the documentation on bugzilla, and it says it is best to check here first before filing.

So here goes:

The following query initially works when created in SQL view

SELECT "Subjects"."Subject", "Classes"."Class" 
FROM "Classes", "Subjects" 
WHERE "Subjects"."Subject" = SUBSTRING ( "Classes"."Class" FROM 1 FOR 5 )

However, if I open the query in design view and then save it, the query does not work anymore. It seems the design view interpretter modifies the SQL slightly. If I open the now broken query in SQL view it reads as:

SELECT "Subjects"."Subject", "Classes"."Class" 
FROM "Classes", "Subjects" 
WHERE "Subjects"."Subject" = SUBSTRING ( "Classes"."Class" FROM '1' FOR '5' )

The design view interpreter puts apostrophes around the integers 1 and 5 which the substring function cannot then process.

Having the substring function elsewhere in the query does not seem to have the same problem. In the case below, toggling between the design and SQL views does not put apostrophes around the integers.

SELECT SUBSTRING ( "Classes"."Class" FROM 1 FOR 5 ) 
FROM "Classes"

Below is the database I was using to generate the problem.

substring.odb

Would this behavior better be attributed to either a design feature or user error? Or is it a bug that I should file?

Libreoffice 7.0.2.2, Firebird Embedded, macOS 10.15.7

Thanks a lot

Ratslinger · November 8, 2020, 2:36am

Hello,

Turn on Run SQL command directly:

Also available from menu → Edit->Run SQL command directly

It is a toggle.

mathteacher · November 8, 2020, 4:27am

Thanks @Ratslinger. I did try that initially, however I wanted to use the result of the query in a subform. When I ran the query in direct SQL mode, I wasn’t able to filter the form based on the master field. What would be best in this situation?

Ratslinger · November 8, 2020, 5:20pm

@mathteacher,

Don’t use design mode. In SQL mode you can set the statement correctly and not need to use Edit->Run SQL command directly. The statement only get corrupted when switching from Design to SQL mode (Bug? - possibly). If you stick in SQL, all is OK.

Personally I avoid Design mode unless attempting to answer a question. There are some problems there especially when dealing with other than HSQLDB embedded databases.

mathteacher · November 10, 2020, 5:20am

Thanks @Ratslinger, the query does everything I need it to do now. Seeing as libreoffice is migrating to firebird, do you think it is worth submitting a bug report for this?

Ratslinger · November 10, 2020, 5:24am

Can’t hurt.

mathteacher · November 11, 2020, 11:17am

OK, thanks @Ratslinger, appreciate your help.