SQLite Forum

Timeline
Login

4 forum posts by user pythonizer

2021-11-09
14:31 Reply: Odd behaviour of UNION (artifact: de98a07811 user: pythonizer)

Ah! Right.

You can clearly see here that the ORDER BY and LIMIT clauses are applied after the UNION (compound-operator).

Thanks!

12:46 Post: Odd behaviour of UNION (artifact: 1c3480e403 user: pythonizer)

Consider the following simple scenario:

CREATE TABLE A (
  c1 INTEGER PRIMARY KEY,
  c2 INTEGER
);

INSERT INTO A VALUES (1, 100), (2, 90), (3, 100);

I want to get the highest and second highest values in c2. I know this is very simple, but I was explaining this to someone else and they asked how they could use UNION for this.
I gave them the following example:

SELECT max(c2) FROM A
UNION
SELECT DISTINCT c2 FROM A ORDER BY c2 DESC LIMIT 1 OFFSET 1;

However, this just gives me:

c2        
----------
90        

I would expect it to give me 90 and 100.
Again, I understand that there are much easier ways to do this, but I just would like to understand why this does not work.

Thanks!

2021-05-10
10:20 Reply: Statement that fires trigger is not executed when the trigger causes an error (artifact: 9c8232c1b7 user: pythonizer)

Very clear and concise. I get it now. Thank you!

2021-05-09
12:34 Post: Statement that fires trigger is not executed when the trigger causes an error (artifact: 05f1133f7c user: pythonizer)

Hi,

I'm trying to understand what is the default behaviour of SQLite when there is an error in the BEGIN-END section of a trigger.

For example:

  CREATE TABLE my_table (id INTEGER PRIMARY KEY);
  INSERT INTO my_table VALUES (1);

  CREATE TRIGGER R1
  AFTER DELETE ON my_table
  BEGIN
    DELETE FROM Non_existent_table WHERE id=OLD.id;
  END;

So I created a table and then added a trigger that is doomed to fail. If I try to remove something from my_table I get an error, of course:

  DELETE FROM my_table WHERE id=1;
  Error: no such table: main.Non_existent_table

Now, since I'm using AFTER DELETE on the trigger, I would expect that the original DELETE statement on my_table would have been executed, but that is not the case:

  SELECT * FROM my_table;
  id       
---------- 1

Why is the original statement that caused the trigger to fire, not executed when there is an error on the trigger?
Is it rolling back the changes by default?
Or does SQLite parse the trigger's actions before executing anything? If so, why wasn't this parsing done when the trigger was created? Seems like that would make more sense.

Anyway, this may be a stupid question, but it's really bugging me that I can't figure it out.

Thanks