sql - Using With clause in sqlite3; are the results of with clause saved and reused? -

with  printercheck (select exists (select 1 available_material_printmode_config  printer_name ='123' limit 1) p), materialcheck (select case when material_name null  0 else 1 end m  available_material_printmode_config printer_name ='123'  , printmode_name = '2' limit 1)  select m.name material m   left join available_material_printmode_config ac on ac.material_name = m.name  , (select p printercheck limit 1)  , (select m materialcheck limit 1)  left join available_materials am1 on am1.material_name = m.name  , ((select p printercheck limit 1) != 1  or ((select p printercheck limit 1)  , (select m materialcheck limit 1) !=1 ))  case when (select p printercheck limit 1) , (select m materialcheck limit 1) ac.printer_name = '123' , ac.printmode_name = '2' else am1.printer_name = '123' end 

i have sqlite3 query looking this, wanted know if printercheck , materialcheck executed/ fetched each row of main query or executed once , result stored , reused.

if printercheck , materialcheck executed each row of main query how avoid , reuse result of printercheck , materialcheck executing them once.

this question, because different databases handle ctes differently. instance, sql server never materializes ctes. oracle does.

according sqlite documentation:

an ordinary common table expression works if view exists duration of single statement. ordinary common table expressions useful factoring out subqueries , making overall sql statement easier read , understand.

based on documentation, sqlite not materialize ordinary ctes (recursive ctes different beast). if want temporary table, explicitly use one.