Select of text that matches scientific notation format automatically converted to number
I am extracting data from an existing SQLite 3 database. The database was built with the affinity type for a given column set to NONE. One column contains an 3 - 4 digit airport code. In most cases, select statements return the data from this column as text. However, in a few cases where the ID matches the scientific notation format (nEn) the identifiers are returned as integers.
An example using the Dell City, Texas airport - identifier '2E5':
sqlite> SELECT location_identifier FROM APT_APT WHERE landing_facility_site_number = '23741.21*A'; 200000
That should have returned
2E5. And even trying to force cast the column doesn't work:
sqlite> SELECT cast(`location_identifier` as text) FROM APT_APT WHERE landing_facility_site_number = '23741.21*A'; 200000
Is this an SQLite bug? Is there another cast format that might give me what I'm expecting? Thanks!
I think it's not a bug.
Before the cast function, the result has been recorded as 20000. So you are convering 20000 to text.
I think you'd better rebuild the table, and change the column attribute to text.
Docs for Determination of Column Affinity, says that if SQLite doesn't otherwise recognize the name, the affinity is NUMERIC. NONE doesn't appear to be recognized.
If you want TEXT, say TEXT (or some alternate spelling, like CHAR).
If it's not defined, shouldn't it be BLOB?
(4.1) By Keith Medcalf (kmedcalf) on 2021-04-13 18:08:07 edited from 4.0 in reply to 1 [link] [source]
You need to show your table definition.
This is the first of the "twenty questions" process. Once this question is answered you may be required to answer 19 more before an answer can be provided.
Here's the schema. It's rather ugly as it's automatically generated by a process that pulls the data from the FAA's source text files.
sqlite> .schema APT_APT CREATE TABLE APT_APT(_id INTEGER PRIMARY KEY AUTOINCREMENT,master_record_row_id INTEGER,aeronautical_sectional_chart_on_which_facility_appears NONE,agency_group_performing_physical_inspection NONE,air_taxi NONE,air_traffic_control_tower_located_on_airport NONE,airframe_repair_service_availability_type NONE,airport_activation_date_mm_yyyy NONE,airport_airspace_analysis_determination NONE,airport_arff_certification_type_and_date NONE,airport_elevation_determination_method NONE,airport_elevation_nearest_tenth_of_a_foot_msl NONE,airport_elevation_source NONE,airport_elevation_source_date_mm_dd_yyyy NONE,airport_has_entered_into_an_agreement_that_grants_landing_rights_to_the_military NONE,airport_inspection_method NONE,airport_lighting_schedule NONE,airport_ownership_type NONE,airport_position_source NONE,airport_position_source_date_mm_dd_yyyy NONE,airport_reference_point_determination_method NONE,airport_reference_point_latitude_formatted NONE,airport_reference_point_latitude_seconds NONE,airport_reference_point_longitude_formatted NONE,airport_reference_point_longitude_seconds NONE,airport_status_code NONE,alternate_fss_identifier NONE,alternate_fss_name NONE,apt_latitude NONE,apt_longitude NONE,associated_city_name NONE,associated_county_or_parish_name NONE,associated_countys_state_post_office_code NONE,associated_state_name NONE,associated_state_post_office_code NONE,availability_of_notam_d_service_at_airport NONE,beacon_lighting_schedule NONE,boundary_artcc_faa_computer_identifier NONE,boundary_artcc_identifier NONE,boundary_artcc_name NONE,commercial_services NONE,common_traffic_advisory_frequency_ctaf NONE,commuter_services NONE,contract_fuel_available NONE,direction_of_airport_from_central_business_district_of_associated_city NONE,distance_from_central_business_district_of_the_associated_city_to_the_airport NONE,ending_date_on_which_annual_operations_data_is_based_mm_dd_yyyy NONE,faa_district_or_field_office_code NONE,faa_region_code NONE,facility_has_been_designated_by_the_us_treasury_as_a_customs_landing_rights_airport NONE,facility_has_been_designated_by_the_us_treasury_as_an_international_airport_of_entry_for_customs NONE,facility_has_military_civil_joint_use_agreement NONE,facility_managers_name NONE,facility_owners_name NONE,facility_use NONE,fuel_types_available_for_public_use_at_the_airport NONE,general_aviation_helicopter NONE,general_aviation_itinerant_operations NONE,general_aviation_local_operations NONE,icao_identifier NONE,identifier_of_the_facility_responsible_for_issuing_notices_to_airmen_notams_and_weather_information_for_the_airport NONE,information_effective_date_mm_dd_yyyy NONE,jet_engine_general_aviation_aircraft NONE,land_area_covered_by_airport_acres NONE,landing_facility_is_used_for_medical_purposes NONE,landing_facility_site_number NONE,landing_facility_type NONE,landing_fee_charged_to_non_commercial_users_of_airport NONE,last_date_information_request_was_completed_by_facility_owner_or_manager_mmddyyyy NONE,last_physical_inspection_date_mmddyyyy NONE,lens_color_of_operable_beacon_located_on_the_airport NONE,local_phone_number_from_airport_to_fss_for_administrative_services NONE,location_identifier NONE,magnetic_variation_and_direction NONE,magnetic_variation_epoch_year NONE,managers_address NONE,managers_city_state_and_zip_code NONE,managers_phone_number NONE,military_aircraft_operations NONE,minimum_operational_network NONE,multi_engine_general_aviation_aircraft NONE,npias_federal_agreements_code NONE,official_facility_name NONE,operational_gliders NONE,operational_military_aircraft_including_helicopters NONE,other_airport_services_available NONE,owners_address NONE,owners_city_state_and_zip_code NONE,owners_phone_number NONE,power_plant_engine_repair_availability_type NONE,record_type_indicator NONE,responsible_artcc_faa_computer_identifier NONE,responsible_artcc_identifier NONE,responsible_artcc_name NONE,segmented_circle_airport_marker_system_on_the_airport NONE,single_engine_general_aviation_aircraft NONE,tie_in_flight_service_station_fss_identifier NONE,tie_in_fss_name NONE,tie_in_fss_physically_located_on_facility NONE,toll_free_phone_number_from_airport_to_alternate_fss_for_pilot_briefing_services NONE,toll_free_phone_number_from_airport_to_fss_for_pilot_briefing_services NONE,traffic_pattern_altitude_whole_feet_agl NONE,transient_storage_facilities NONE,type_of_bottled_oxygen_available NONE,type_of_bulk_oxygen_available NONE,ultralight_aircraft NONE,unicom_frequency_available_at_the_airport NONE,wind_indicator NONE); CREATE INDEX airports_location_index on APT_APT (apt_latitude, apt_longitude); CREATE INDEX airports_identifier_index on APT_APT (location_identifier); CREATE INDEX landing_facility_site_number_index on APT_APT (landing_facility_site_number); sqlite>
The code that creates this is from here: https://github.com/jlmcgraw/processFaaData/blob/master/parse_nasr.pl
I've opened an issue with the author/maintainer.
The column type is declared as
NONE which means that the column data (and comparands) will be converted to NUMERIC (INTEGER or REAL) if they "look like a number".
So when the value '2E5' is stored in the database, the INTEGER 200000 is stored, not the text string.
The type keyword meaning "don't do any conversions" is either "BLOB" or not having a type keyword.
When I use a column declared as BLOB to save a number like "1.234567890E9", when I output it, why it turns out to be "1234567890.0"?
How to change the format so that SQLite can output it as how I input?
And why it's followed by ".0"?
Well, firstly is "1.234567890E9" a text string or a number?
Assuming that your quotes do not indicate text, then the inherent type of that ASCII character sequence is floating point. This is because it contains (1) a decimal point; and, (2) an exponent.
The "number" is stored as a floating point number exactly and precisely as specified. When it is output, the floating point number is output exactly.
However, some UI function is "converting" the actual floating point binary number into something that your eyes can consume in a manner that is "pretty" for your sensibilities. If you do not like the prettification then you are free to prettify the IEEE-754 binary64 floating point bytes in whatever fashion you find scratches your itches.
The actual stored byte sequence is x'41D26580B4800000'. It could be output in this format, but that would cause consternation to a wider degree than using "common output prettification".
This is simply how computers work. If you want the computer to work differently you may cause it to do so by programming it differently.
Don't use BLOB for anything to do with this. BLOBs are neither numbers nor text and you'll be introducing additional processing and complexity.
If you want to store the characters "1.234567890E9" use TEXT affinity. Of course, since these values will be text, you can't do maths on them, so you won't be able to things like calculate a sum. It's worth noting that the strings "1.234567890E9" and "1.23456789E9" are different, but they represent the same number. So don't expect to be able to search for values.
SQLite doesn't have output. There are no printing functions in SQLite. The value you get from SQLite depends on which API you're using to access it. You may get text, a float, a double, an integer, etc. depending on what library you're using. If you want to see what you'll get, check the documentation of whatever function you're calling.
If you want SQLite to represent a number as text for you, you can use the printf() function as described here:
If you deliberately specify a text format for output you cannot be sure you're going to get back something identical to the value stored. You're asking for something that looks pretty, not for something accurate to the last bit. This is not a problem just with SQLite, it's a problem with representing binary numbers as a scientific decimal strings and occurs with almost every computing language.
The number you see ends in '.0' either because you asked for it, or to signify that whatever system you're using is processing that value as if it's floating point, not an integer.
The basic underlying question for the question which started this thread is whether you care about a numeric value, or a particular string which must be reproduced precisely.
You are confused as to the BLOB datatype (an arbitrary bucket-o-bytes) and BLOB (or no) affinity.
The BLOB data storage type means an arbitrary bucket-o-bytes with no meaning to SQLite3.
The BLOB column affinity (which can be achieved by a column declaration of type BLOB or an empty affinity) means
do not convert the datatype of storage -- store what you are given without molestation.
do_not_molest_me in table
x can be specified to store whatever datatype is given by either of the following declarations:
create table x ( do_not_molest_me blob );
create table x ( do_not_molest_me );
Any value at the intersection of any column and row in any table can store data of any storage type. The column affinity (if not BLOB or empty) specifies what storage type is preferred if the data can be converted to that storage type.
The root cause of the issue is failing to comprehend section 3.1 of that documentation. The column affinity NONE has type NUMERIC, not BLOB.
The OP is using an existing SQLite db. The problem came up when this db was created as already discussed in several of the replies, and fixes were suggested.
If, however, the db cannot be obtained in some corrected form, then the only way would be to try to fix when SELECTing from the db. typeof to indicate when the undesired conversion to numeric had occured and printf would convert back to the 3-4 digit airport code. But this seems ambiguous: For example 200000 was probably '2E5' (Dell City Municipal Airport), but could also have been '20E4'. The latter does not seem to exist as an airport code, so the database entry should be updated to '2E5'. But whether an ambiguity can be resolved in all cases is uncertain. And even if ambiguities could be resolved programming the update in SQL would be quite a project and involve another Sqlite database of all existing airport codes. Would this be available?