Bug 137042 - SQL Error -607, when changing Field type of a table (Firebird)
Summary: SQL Error -607, when changing Field type of a table (Firebird)
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.0.1.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: --> added testcase leads to crash
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2020-09-26 10:18 UTC by Richard Demattio
Modified: 2020-12-06 14:31 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
step by step instructions for reproduction (2.66 KB, text/plain)
2020-11-02 13:59 UTC, Richard Demattio
Details
here you are - the requested Database; (3.32 KB, application/vnd.oasis.opendocument.database)
2020-11-02 20:23 UTC, Richard Demattio
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Richard Demattio 2020-09-26 10:18:46 UTC
Description:
I tried to change a previously defined boolean type column to varchar(30) and got the error "SQL_BOOLEAN does not exist".


Steps to Reproduce:
1.I tried to change the field type of a table column from BOOLEAN to VARCHAR(30)
2.I was asked to confirm, that the column will be deleted and readded as new type
3.I confirmed and got an error message: - see below

Actual Results:
Errormessage:
firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE tlg_Nutzbarkeit failed
*SQL error code = -607
*Invalid command
*Specified domain or source column SQL_BOOLEAN does not exist
caused by
'ALTER TABLE "tlg_Nutzbarkeit" ADD "frei" SQL_BOOLEAN'
 /home/buildslave/source/libo-core/connectivity/source/drivers/firebird/Util.cxx:68

Expected Results:
it should work.



Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
It is possible to select that "Yes/no[boolean]" type in the "table design view".
(I did it this way earlier.)

Similar behaviour, when I tried to change a column type from other type to varchar.

Changing via "SQL ALTER TABLE ..." works
Comment 1 Richard Demattio 2020-10-17 20:48:04 UTC
I testesd this time with LO 7.1.1.0,
Build 
https://gerrit.libreoffice.org/gitweb?p=core.git;a=log;h=17cbc559be6936777904e5cf8a517cac89045264

I could not reproduce the message "SQL_BOOLEAN does not exist" with the daily build, but I could reproduce a crash this time, when I tried to change the type..

TESTCASE: based on a table with two fields I did the following in design view:

1.) Add new field "test1" VARCHAR
2.) add new field "Test2" INTEGER
3.) change Type of "test1" to BOOLEAN
    Error code: 1
    firebird_sdbc error:
    *unsuccessful metadata update
    *ALTER TABLE tla_Kontaktarten failed
    *action cancelled by trigger (1) to preserve data integrity
    *Cannot update index segment used by an Integrity Constraint
    caused by
    'ALTER TABLE "tla_Kontaktarten" ALTER COLUMN "Typ" TYPE BOOLEAN'
     /tinderbox/buildslave/source/libo-master/connectivity/source/drivers/firebird/Util.cxx:68

4.) I confirmed (nevertheless)
    The change happened, although the errormessage said, it failed

5.) I again changed "test1" back to VARCHAR

 ... CRASH (two times ... reproducable)
Comment 2 Richard Demattio 2020-10-17 20:51:35 UTC
I sent two crash reports, but I myself could not see the Report number
sorry
Comment 3 Robert Großkopf 2020-10-18 16:38:35 UTC
First this error appears when changing Boolean field to Varchar:
Conversion from base type BOOLEAN to VARCHAR is not supported.

When I confirm to delete the old Boolean field and add a new Varchar field it will work here. So I could not confirm the buggy behavior.

Tested with LO 7.0.2.2 on OpenSUSE 15.1 64bit rpm Linux
Comment 4 Richard Demattio 2020-10-18 17:00:50 UTC
(In reply to Robert Großkopf from comment #3)
> First this error appears when changing Boolean field to Varchar:
> Conversion from base type BOOLEAN to VARCHAR is not supported.
> 
> When I confirm to delete the old Boolean field and add a new Varchar field
> it will work here. So I could not confirm the buggy behavior.
> 
> Tested with LO 7.0.2.2 on OpenSUSE 15.1 64bit rpm Linux

I agree - it is stupid, changing VARCHAR to BOOLEAN and the other way round.
Your way makes more sense.

But what I did must not lead to a crash, but should be prohibited by logic.
In general: rejecting forbidden conversions by logic will increase stability.

Such kind of inputs can happen accidentally.
Comment 5 Richard Demattio 2020-10-19 08:19:03 UTC
(In reply to Richard Demattio from comment #4)
> (In reply to Robert Großkopf from comment #3)
> > First this error appears when changing Boolean field to Varchar:
> > Conversion from base type BOOLEAN to VARCHAR is not supported.
> > 
> > When I confirm to delete the old Boolean field and add a new Varchar field
> > it will work here. So I could not confirm the buggy behavior.
> > 
> > Tested with LO 7.0.2.2 on OpenSUSE 15.1 64bit rpm Linux

The crash happens, when you continue 
and try to change the now boolean field back to VARCHAR.

(My last answer was based on a misunderstanding of what you said)
Comment 6 Robert Großkopf 2020-10-19 09:27:26 UTC
(In reply to Richard Demattio from comment #5)
> 
> The crash happens, when you continue 
> and try to change the now boolean field back to VARCHAR.

And exactly this I couldn't confirm. The only thing I noticed: The changing of the field type will switch back in the GUI table editor. So I have to change the type again.
Comment 7 Julien Nabet 2020-11-01 09:22:27 UTC
On pc Debian x86-64 with master sources updated today, here what I got with an embedded Firebird:
- I create a table with "id"/INTEGER as primary key + "f1" as BOOLEAN field
- save + quit editing
=> OK
- reopen to edit the table and change BOOLEAN to VARCHAR + click save (arrow icon at the left)
=> message: "the column "f1" could not be changed. Should the column instead be deleted and the new format appended?
Console logs show:
warn:connectivity.firebird:15955:15955:connectivity/source/drivers/firebird/Statement.cxx:114: isc_dsql_execute failed
warn:connectivity.firebird:15955:15955:connectivity/source/drivers/firebird/Util.cxx:57: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE Table3 failed
*Cannot change datatype for f1.  Conversion from base type BOOLEAN to VARCHAR is not supported.
caused by
'ALTER TABLE "Table3" ALTER COLUMN "f1" TYPE VARCHAR(100)'

- click Yes
=> UI shows back BOOLEAN instead of VARCHAR
- click Save icon + quit editing
- editing again, the field shows now VARCHAR (!!)

So for me no crash but a pb of UI refresh, it shouldn't display "BOOLEAN" just before final editing quit.
Comment 8 Richard Demattio 2020-11-01 10:33:52 UTC
(In reply to Julien Nabet from comment #7)
> .....
> 
> So for me no crash but a pb of UI refresh, it shouldn't display "BOOLEAN"
> just before final editing quit.

I can no longer reproduce the crash too - not even, when doing really crazy things.

Meanwhile I changed a lot in the database, where it happened first.
Maybe I forgot to document the one little, but significant step leading to reproducable crash. - sorry
Comment 9 Julien Nabet 2020-11-01 11:11:27 UTC
Let's put this one to WFM then.

Richard: if you can find the way to reproduce the crash or -607 error, don't hesitate to reopen this tracker.
Comment 10 Richard Demattio 2020-11-02 13:59:12 UTC
Created attachment 166937 [details]
step by step instructions for reproduction

now i succeeded with reproducing the error
Comment 11 Julien Nabet 2020-11-02 19:54:31 UTC
The request to create the first table worked but not the second request.

Would it be possible you attach the odb file?
Comment 12 Richard Demattio 2020-11-02 20:23:16 UTC
Created attachment 166953 [details]
here you are - the requested Database;

The database is at the status before that step below

"    - now I entered design mode again by "<right klick> / Edit"
      - and set "Entry required" to "YES"
      - and klicked the save icon for the table"

~~~~~~~~~~~+
I tested the SQLs and it worked.

Maybe you tried to execute all the SQL statements  in one step.
This does not work!

You have to execute every instruction ending by a semicolon separately.
Comment 13 Julien Nabet 2020-11-03 12:35:22 UTC
(In reply to Richard Demattio from comment #12)
> ...
> Maybe you tried to execute all the SQL statements  in one step.
> This does not work!
> ...
Thank you for your feedback but I did try one by one and it failed for me.

Anyway, I gave a try with your DB and tested from
"
    - now I entered design mode again by "<right klick> / Edit"
      - and set "Entry required" to "YES"
      - and klicked the save icon for the table
~~~~~~~~~~~~~~~~~~~~~~~      
I got the Warning: "The column "Unit" could not be changed. 
                    Should the column be deleted and the new format appended?"
and on "More" I got the additional information:
Error code: 1

firebird_sdbc error:
*UPDATE operation is not allowed for system table RDB$RELATION_FIELDS
caused by
'UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1 WHERE RDB$FIELD_NAME = 'Unit' AND RDB$RELATION_NAME = 'BUG137042''
 /tinderbox/buildslave/source/libo-70/connectivity/source/drivers/firebird/Util.cxx:68
=> OK, I got the same

then:
I said "yes" to the warning message
but got:
*Cannot make field Unit of table BUG137042 NOT NULL because there are NULLs present
caused by
'ALTER TABLE "BUG137042" ADD "Unit" VARCHAR(5) NOT NULL'

I think the change of message is due to some recent commits on I did master sources.
About this new problem, it seems LO is stucked because it removed a column where there was a value but when trying to add the column, it has no value whereas it's required.
I don't know how and even if it can be fixed.
Comment 14 Julien Nabet 2020-11-03 12:37:53 UTC
BTW, with LO Debian package 7.0.2.2, I reproduce your pb with exactly the same message.=>NEW
So I confirm the recent commits made the message change.