It was released, where were you able to reproduce it?

MMax (@rozenmd)It was released, where were you able to reproduce it?

F

F0rce•2/22/24, 3:53 PM

D1 and using Drizzle.

F

F0rce•2/22/24, 3:53 PM

so it could be an drizzle error / they didnt update their part yet

M

Max (@rozenmd)OP•2/22/24, 3:53 PM

D1 as in through a Worker, or via wrangler d1 execute?

F

F0rce•2/22/24, 3:53 PM

through a worker

M

Max (@rozenmd)OP•2/22/24, 3:54 PM

using

.raw()

.raw()

?

F

F0rce•2/22/24, 3:54 PM

I dont know what drizzle uses at the end, 1 sec let me check

M

Max (@rozenmd)OP•2/22/24, 3:54 PM

Oh, yeah they'd need to update on their end

F

F0rce•2/22/24, 3:55 PM

probably they are not using

.raw()

.raw()

M

Max (@rozenmd)OP•2/22/24, 3:55 PM

To include an array of column names in the results when using raw(), use
raw({columnNames: true})
raw({columnNames: true})
.

F

F0rce•2/22/24, 3:55 PM

ahhhh let me bump the issue on their end again, sorry for the false alarm

F

F0rce•2/22/24, 3:55 PM

thanks for the fast answer

M

Max (@rozenmd)OP•2/22/24, 4:00 PM

Actually let me double check with the workerd team internally

FF0rce ahhhh let me bump the issue on their end again, sorry for the false alarm

M

Max (@rozenmd)OP•2/22/24, 4:12 PM

It's on us actually, it's live in main but not released in the workerd package + workers-types

F

F0rce•2/22/24, 4:13 PM

wait, so the fix has not been released yet ?

F

F0rce•2/22/24, 4:13 PM

and thats probably why the issue is still open ?

M

Max (@rozenmd)OP•2/22/24, 4:13 PM

if you use raw directly, you're interacting with the live runtime, which has the fix

M

Max (@rozenmd)OP•2/22/24, 4:14 PM

we reopened that issue because the workers-types package hasn't been bumped yet

F

F0rce•2/22/24, 4:14 PM

thats probably why it hasent been fixed in drizzle yet, because they wait for the types

F

F0rce•2/22/24, 4:14 PM

as the latest comment in the issue states

F

F0rce•2/22/24, 4:15 PM

then is there an ETA on when the package & types will be bumped ?

M

Max (@rozenmd)OP•2/22/24, 4:15 PM

I've nudged the workerd folks for a release

TTony Leung and ```sql CREATE INDEX project_tiktok_link_is_empty_and_id_idx ON project (tikt...

M

Max (@rozenmd)OP•2/22/24, 4:31 PM

what if

id

id

is first in your index?

MMax (@rozenmd)what if `id` is first in your index?

T

Tony Leung•2/22/24, 4:32 PM

sorry, my actual query is:

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

T

Tony Leung•2/22/24, 4:33 PM

i've tried swapping the position of

id

id

and the expression

(tiktok_link IS NULL OR tiktok_link = '')

(tiktok_link IS NULL OR tiktok_link = '')

T

Tony Leung•2/22/24, 4:33 PM

both in the query and the index

TTony Leung sorry, my actual query is: ```sql SELECT ... FROM project WHERE id < '01HQ81GRDX...

M

Max (@rozenmd)OP•2/22/24, 4:33 PM

it's the less than

T

Tony Leung•2/22/24, 4:33 PM

but none of them work

T

Tony Leung•2/22/24, 4:34 PM

i've tried

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

i do aware that sqlite

id <

id <

will not use other columns in the index after that

T

Tony Leung•2/22/24, 4:35 PM

I also tried

INDEXED BY

INDEXED BY

INDEXED BY

INDEXED BY, it does not work neither

M

Max (@rozenmd)OP•2/22/24, 4:35 PM

because you're asking sqlite to scan your table

T

Tony Leung•2/22/24, 4:36 PM

oh...

T

Tony Leung•2/22/24, 4:36 PM

so the expression

id <

id <

will make sqlite scan the table?

T

Tony Leung•2/22/24, 4:37 PM

btw I also tried a new index:

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

and do the query

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

and it seems to use the index now

S

Steved•2/22/24, 4:37 PM

/subscribe D1

T

Tony Leung•2/22/24, 4:38 PM

not a good practice right...

TTony Leung btw I also tried a new index: ```sql CREATE INDEX project_tiktok_link_id_idx ON ...

M

Max (@rozenmd)OP•2/22/24, 4:38 PM

that's pretty much how you do it, yeah

T

Tony Leung•2/22/24, 4:38 PM

sorry I'm pretty new with SQLite

MMax (@rozenmd)that's pretty much how you do it, yeah

T

Tony Leung•2/22/24, 4:39 PM

sorry to bother you

hope you don't mind

M

Max (@rozenmd)OP•2/22/24, 4:39 PM

that's alright i learned SQL via postgres myself

M

Max (@rozenmd)OP•2/22/24, 4:39 PM

https://sqlite.org/queryplanner.html is a very good guide btw

T

Tony Leung•2/22/24, 4:39 PM

I'm just curious that why the expression

(tiktok_link IS NULL OR tiktok_link = '')

(tiktok_link IS NULL OR tiktok_link = '')

in index does not work like mysql

T

Tony Leung•2/22/24, 4:39 PM

can't seem to use it anywhere

M

Max (@rozenmd)OP•2/22/24, 4:40 PM

indexing by

tiktok_link

tiktok_link

tiktok_link

tiktok_link covers that anyway

T

Tony Leung•2/22/24, 4:40 PM

thanks, I was overthinking

T

Tony Leung•2/22/24, 4:41 PM

i does not work as expected... this is much different from mysql

T

Tony Leung•2/22/24, 4:42 PM

i was nuking sqlite all the time... thanks for your support

MMax (@rozenmd)indexing by `tiktok_link` covers that anyway

T

Tony Leung•2/22/24, 4:44 PM

btw does using

indexed by

indexed by

indexed by

indexed by reduces my rows read?

TTony Leung btw does using `indexed by` reduces my rows read?

M

Max (@rozenmd)OP•2/22/24, 4:53 PM

From what I understand of https://www.sqlite.org/lang_indexedby.html, only if the index you tell the query planner to use is more efficient

M

Max (@rozenmd)OP•2/22/24, 4:53 PM

It's not a hint, so it will ignore better indexes if you tell it

indexed by

indexed by

indexed by

indexed by

MMax (@rozenmd)It's not a hint, so it will ignore better indexes if you tell it `indexed by`

T

Tony Leung•2/22/24, 5:19 PM

thanks for clarification. I will check for rows read in d1 query meta

It was released, where were you able to reproduce it?

Similar Threads

Similar Threads

Similar Threads