New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot create TRIGGER as shown in SQLite Example #1470

Closed
kwasmich opened this Issue Jul 10, 2018 · 9 comments

Comments

Projects
None yet
4 participants
@kwasmich
Copy link

kwasmich commented Jul 10, 2018

Details for the issue

What did you do?

I tried to create a trigger on an existing database. But without success. So instead I went over to sqlite.org and tried the examples.

The executed lines are:

CREATE TABLE customer( cust_id INTEGER PRIMARY KEY, cust_name TEXT, cust_addr TEXT);
CREATE VIEW customer_address AS SELECT cust_id, cust_addr FROM customer;
CREATE TRIGGER cust_addr_chng INSTEAD OF UPDATE OF cust_addr ON customer_address BEGIN UPDATE customer SET cust_addr=NEW.cust_addr WHERE cust_id=NEW.cust_id; END;

And it fails on the last line.

What did you expect to see?

I expected to execute the sample code and thus successfully create a trigger.

What did you see instead?

incomplete input: CREATE TRIGGER cust_addr_chng INSTEAD OF UPDATE OF cust_addr ON customer_address BEGIN UPDATE customer SET cust_addr=NEW.cust_addr WHERE cust_id=NEW.cust_id

Useful extra information

The three statements execute successfully in the sqlite3 command line tool which is 3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca.

What operating system are you using?

  • Windows: ( version: ___ )
  • Linux: ( distro: ___ )
  • Mac OS: ( 10.11.6 )
  • Other: ___

What is your DB4S version?

  • 3.10.1
  • 3.10.0
  • 3.9.1
  • Other: ___

Did you also

@justinclift justinclift added the bug label Jul 10, 2018

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jul 10, 2018

Thanks @kwasmich, that does sound like a bug.

@chrisjlocke

This comment has been minimized.

Copy link
Contributor

chrisjlocke commented Jul 10, 2018

I believe this might be corrected in the nightlies.

image

It seems to work OK for me.

image

@kwasmich - Can you try a more recent build from here?
https://nightlies.sqlitebrowser.org/latest/

@kwasmich

This comment has been minimized.

Copy link
Author

kwasmich commented Jul 10, 2018

Yes I've tried the nightly with the same results. BUT to further narrow it down:
I executed each line individually and not in one transaction.
It works fine in one transaction but not when stepped through every line.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jul 10, 2018

Oh, as bugs go that definitely seems like a weird one. It's good it's so easy to reproduce though, as it should at least make it easy to track down. 😄

@chrisjlocke

This comment has been minimized.

Copy link
Contributor

chrisjlocke commented Jul 10, 2018

Aah, that's probably the 'gotcha' then. When executed as one lump, DB4S just 'eats the lot'. When told to parse each 'line', it gets a bit confused with the last line ending with ;END, as a semi-colon is normally used to terminate a SQL statement (as in the top two lines). So DB4S is actually treating the 3rd line as 2 separate commands...

So the workaround is to move the 3rd line in all its glory to a separate Execute SQL tab, so it can be run as a whole. So you can still execute it line by line, but not fully on the last line.....
(I'm guessing that makes NO sense!!)

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Jul 12, 2018

An easier workaround would be to select the current line and click the normal Execute button which will only the current selection if there is one.

But yeah, this is definitely a bug. The easiest way to trigger it is by only having this simple statement and trying to execute it by clicking the Execute current line button:

SELECT ";"

As @chrisjlocke suspected, this is because we split the statement at the semicolon. See #780 for details why. I'll have to think about this a bit more - no idea how to fix this and still have the features from #780.

MKleusberg added a commit that referenced this issue Jul 12, 2018

Rework execution logic in Execute SQL tab
This commit changes the execution logic in the Execute SQL tab. Before
this we were trying to first get the exact part of the SQL text to
execute and then execute it without further checking. After this we're
only trying to find an exact start position for the SQL text to execute
while the end position is only a rough estimate. This way the exact end
position can be determined by SQLite.

This fixes issue #1470.

It also cleans up the code a bit and (hopefully) makes it a bit easier
to read.
@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Jul 12, 2018

I believe this is fixed now. Can you try again with tomorrow's nightly build and check if it's working for you as well, @kwasmich?

This required larger changes in the involved code. So if any strange behaviour shows up, please let me know everybody 😄

@kwasmich

This comment has been minimized.

Copy link
Author

kwasmich commented Jul 14, 2018

To me it looks fine! I can't reproduce the issue with the latest nightly build.

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Jul 14, 2018

Cool, thanks for testing and thank you again for reporting this issue! I'm going to close this then 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment