SELECT COUNT(*) ... only returns NULL ?

[Strange-Knoll]

I have an issue with statements like SELECT COUNT(*) and SELECT SUM(*). I expected these queries to return INTEGER or REAL but instead they return a table whos column is COUNT(*) and whos value is NULL. Is there a way to get the data i'm expecting out of this query?

[Strange-Knoll]

Turns out NULL can have hidden data underneath it. the solution is to parse the column again if its initially Null.

[abonander]

@Strange-Knoll can you post a complete example so I can see what you're talking about?

[Strange-Knoll]

so i have this pretty print function to start with that looks like this:

pub fn pretty_print_data(data:Vec<SqliteRow>){
        let mut pretty_print_columns:String = String::new();
        let mut pretty_print:String = String::new();
        
        if data.len() > 0{    
            data[0].columns().iter().for_each(|column|{
                pretty_print_columns.push_str(format!("{} | ", column.name()).as_str());
            });
        }
    
        data.iter().for_each(|row|{
    
            row.columns().iter().for_each(|column|{
                //println!("{}", column.type_info());
                match column.type_info().to_string().as_str(){
                    "BOOLEAN" => pretty_print.push_str(format!("{} | ", row.get::<bool, &str>(column.name())).as_str()),
                    "INTEGER" => pretty_print.push_str(format!("{} | ", row.get::<i32, &str>(column.name())).as_str()),
                    "REAL" => pretty_print.push_str(format!("{} | ", row.get::<f64, &str>(column.name())).as_str()),
                    "TEXT" => pretty_print.push_str(format!("{} | ", row.get::<String, &str>(column.name())).as_str()),
                    "BLOB" => pretty_print.push_str(format!("{} | ", "BLOB").as_str()),
                    "NULL" => pretty_print.push_str(format!("{} | ", "NULL" ).as_str()),
                    _ => println!("Unknown type"),
                }
            });
            pretty_print.push_str("\n");
        });
        println!("-----------------------------------");
        println!("{}", pretty_print_columns);
        println!("{}", pretty_print);
        println!("-----------------------------------");
    }

which performs fine when running select queries, but when you try to run COUNT, SUM, or something like PRAGMA table_info(table) you get nulls across the board:

Count(*) | 
NULL | 

However if you do a second check for data under NULL like in this revised function you get results we expect:

pub fn pretty_print_data(data:Vec<SqliteRow>){
        let mut pretty_print_columns:String = String::new();
        let mut pretty_print:String = String::new();
        
        if data.len() > 0{    
            data[0].columns().iter().for_each(|column|{
                pretty_print_columns.push_str(format!("{} | ", column.name()).as_str());
            });
        }
    
        data.iter().for_each(|row|{
    
            row.columns().iter().for_each(|column|{
                //println!("{}", column.type_info());
                match column.type_info().to_string().as_str(){
                    "BOOLEAN" => pretty_print.push_str(format!("{} | ", row.get::<bool, &str>(column.name())).as_str()),
                    "INTEGER" => pretty_print.push_str(format!("{} | ", row.get::<i32, &str>(column.name())).as_str()),
                    "REAL" => pretty_print.push_str(format!("{} | ", row.get::<f64, &str>(column.name())).as_str()),
                    "TEXT" => pretty_print.push_str(format!("{} | ", row.get::<String, &str>(column.name())).as_str()),
                    "BLOB" => pretty_print.push_str(format!("{} | ", "BLOB").as_str()),
                    "NULL" => {
                        if row.try_get::<&[u8], &str>(column.name()).is_ok(){
                            let data = row.get::<&[u8], &str>(column.name());
                            let data_string = String::from_utf8_lossy(data);
                            pretty_print.push_str(format!("{} | ", data_string).as_str());
                        }else if row.try_get::<String, &str>(column.name()).is_ok(){
                            pretty_print.push_str(format!("{} | ", row.get::<String, &str>(column.name())).as_str());
                        }else if row.try_get::<i32, &str>(column.name()).is_ok(){
                            pretty_print.push_str(format!("{} | ", row.get::<i32, &str>(column.name())).as_str());
                        }else if row.try_get::<f64, &str>(column.name()).is_ok(){
                            pretty_print.push_str(format!("{} | ", row.get::<f64, &str>(column.name())).as_str());
                        }else if row.try_get::<bool, &str>(column.name()).is_ok(){
                            pretty_print.push_str(format!("{} | ", row.get::<bool, &str>(column.name())).as_str());
                        }else{
                            pretty_print.push_str(format!("{} | ", "NULL").as_str());
                        }
                    },
                    _ => println!("Unknown type"),
                }
            });
            pretty_print.push_str("\n");
        });
        println!("-----------------------------------");
        println!("{}", pretty_print_columns);
        println!("{}", pretty_print);
        println!("-----------------------------------");
    }

returns from query SELECT COUNT(*) FROM table

COUNT(*) | 
5 |