Possible Python Sqlite3 Bug

Question

i have a table which has a column "cup" inside table "coffee"

update coffee set cup=cup||'test_add'

this doesnt raise an error but it doesnt add the string 'test_add' to the column! also, the value in column cup is currently nothing. it took me forever that you can't use this syntax adding text when the column has no value to it. this doesn't make sense to me cause the column is defined as a TEXT column, and it has no value, so it seems equivalent to me to

a=""
a+="test_add"

and this syntax works. on the other hand if it is

a=None
a+="test_add"

it throws an error. so it seems to me the code i put at the very top should either add the text or throw an error!!!

mu is too short · Accepted Answer · 2013-06-04 05:31:30Z

1

In SQL, doing pretty much anything with NULL gives you NULL back. In particular, this:

NULL || 'test_add'

is NULL. You say that the cup column has no value, that together with the symptoms you describe means that cup IS NULL is true. So your cup || 'test_add' actually does add your string to the column's value, the problem is that NULL || any_string_you_want is NULL.

If you want to treat NULL like an empty string, then use COALESCE:

coalesce(X,Y,...)

The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must be at least 2 arguments.

So you'd do something like this:

update coffee set cup = coalesce(cup, '') || 'test_add'

Python and SQL are different languages, you shouldn't expect Python's None to behave like SQL's NULL. You're not seeing a bug, you're just seeing standard NULL behavior (which often seems like a bug when you're not used to it but that's another story).

answered Jun 4, 2013 at 5:31

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

appleLover Over a year ago

oh oops, thanks i am new to sql (and python) that explains it perfectly

mu is too short Over a year ago

@appleLover: Don't feel bad, NULL throws everyone for a loop at first.

Collectives™ on Stack Overflow

Possible Python Sqlite3 Bug

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related