Android Sqlite After Adding Column Select entries base on conditions

Question

So this happened when I tried to mark deleted entries to have a 'deleted' value of '1' instead of deleting it.

I added a column to a database in my app using ALTER TABLE "+TABLE_NAME+" ADD COLUMN "+NEW_COLUMN_NAME+" "+type+";"); and made other appropriate changes in the database provider (when insert 0 is inserted), however, something weird happened.

After adding it, the following things happen:

SELECT * FROM TABLE_NAME - Returns all rows (as expected)
SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME=1 returns rows with NEW_COLUMN_NAME=1 (as expected). However,
SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME=0 returns newly inserted rows (as expected).
SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME!=1 returns ONLY newly inserted rows (not the old rows)
SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME=null returns nothing.

does Android not comply with http://www.sqlite.org/lang_expr.html or did I miss something?

ran in Android 2.2 emulator.

Thanks.

mu is too short · Accepted Answer · 2011-06-02 05:57:37Z

4

This one:

SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME = null

Will return an empty row set in pretty much any datebase because in SQL, the expression NULL = x is false for all x (even an x of NULL). Try this instead:

SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME IS NULL

Similar NULL issues will apply to this one:

SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME != 1

so try:

SELECT * FROM TABLE_NAME WHERE NEW_COLUMN_NAME != 1 OR NEW_COLUMN_NAME IS NULL

instead.

This isn't anything specific to SQLite or Android, this is just normal SQL behavior. If you don't absolutely need to allow NULL values in your columns, create them with NOT NULL (and possibly a default value) to avoid some confusion and frustration.

answered Jun 2, 2011 at 5:57

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

Android Sqlite After Adding Column Select entries base on conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related