| Summary: | Add support for 'case insensitive' operator ILIKE for SQL queries | ||
|---|---|---|---|
| Product: | [Applications] KEXI | Reporter: | Ian Balchin <inksi> |
| Component: | Queries | Assignee: | Kexi Bugs <kexi-bugs> |
| Status: | CONFIRMED --- | ||
| Severity: | wishlist | CC: | adam, inksi, staniek |
| Priority: | NOR | ||
| Version: | 2.9.3 | ||
| Target Milestone: | --- | ||
| Platform: | Mint (Ubuntu based) | ||
| OS: | Linux | ||
| Latest Commit: | Version Fixed In: | ||
| Sentry Crash Report: | |||
@Ian Some research here https://mail.kde.org/pipermail/kexi-devel/2015-May/000343.html Actually quite a lot. For this very wish: except for PostgreSQL, ILIKE is already available: just use LIKE which is case-insensitive. For PostgreSQL we have a bug to fix. I concluded in the thread linked above: [2.3] <<So we add parsing of the ILIKE keyword and react in the same way as for LIKE. [..] BTW, the docs for Kexi SQL shall say "ILIKE works exactly as LIKE and is provided for compatibility and completeness". >> We can set this report's status as invalid or change its summary to the meaning of [2.3]. Whooooah! I am not sure that I read you correctly. I don't want to achieve case-insensitive searching at the expense of case-sensitive searching. If that is the case I wish I had not mentioned it <sob>. I have plenty of reasons to use case-sensitive searching in my database. I do not have knowledge of the underlying Kexi structures, but I realise that Kexi has to have some point where it checks the SQL query written by the user for validity and this would be more complex where a given sql product may not use a function that another product does. ie. Kexi concentrates on core functions common to supported databases. It looks like ILIKE falls into the category of a non-ansi-standard postgresql extension? But LIKE is ANSI standard. I do prefer writing my own queries. That said why does the failure of an SQL check have to prejudice the acceptance of a hand-written sql query - especially when the aim of Kexi is to have the user design the query graphically and to whom the SQL window will be a mystery best left alone. In any case, someone using SQLite is not likely to latch onto the ILIKE query, and if they do can this actually break anything? Will SQLite not just return its own error message? It seems to me that case in searching is a very basic requirement. Every search box in every application has a tick-box for that - even the kexi Find box (which searches the data in memory of course). However implemented users will surely expect this as a given? Am I barking up the wrong tree here? Feel free to put me right with some tuition or references. Ian Hi there, i have been following the bugmails on this issue closely and see that the solution is to force postgresql into downward compliance to case-insensitive searches by default. I naively thought that any query written in the SQL mode would be passed through as written, sqlite queries being checked for sqlite-correctness, and postgresql queries being checked for postgresql-correctness, and so on. As a corollary I similarly imagined that the Design mode would generate an SQL query in accordance with its programmed capabilities - which might or might not include case-sensitive search. I feel that it is a pity that we have to go this way, after all choice of case selection can be a big help in text searches. But I understand that there are upstream design choices already implemented, and that if this has to be then this has to be. We'll get by. But looking to the future, the upshot of all this is that when the Design mode is fully operational will the SQL mode be dispensed with? I don't see what purpose it will then serve since its functionality will be limited to the capabilities coded into the Design mode. A small box at the bottom could show the generated sql query (non-editable) for those users seeking confirmation that the Design mode 'magick' has interpreted their intentions correctly. I hate the thought of this, I have become very adept at quick changes to my basic saved sql queries as sometimes needed and a line of sql speaks to me more eloquently than a visual representation. bestest regards Ian @Ian Two things #1. Consistency between backends is by design, and is important for tools like Kexi, which isn't a frontend to a specific backend but rather a complete integrated stack. We had to pick something and if I read [http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt] correctly, collation is based on what column has defined, and on default collation, and so on (there are many levels), so there's no one particular behaviour of LIKE worldwide. If you copy a query with LIKEs to another database (the same backend!) and there are the same tables, but some different colations, the LIKEs can behave differently. It's more explicitly stated for example in MSSQL FAQs. I've been reading this often about MySQL too. No idea about Oracle but I can guess... #2. I agree that so called native SQL support could be useful for two reason: give the power user power they deserve (and right to shot in the feet to others), have a solution until KEXISQL is relatively feature-complete. #2.1. I also completely agree that there's a big value in having the KEXISQL mode; so don't give up :) Having that in Kexi costs tons of development, without this we wouldn't need the whole parser in the first place. LO Base does not go that far and then the users have to study the behaviour of the backend before they enter some SQL for forms. (it's good if they know they have to study that...) In contrast, MS Access has one db engine plus ADO/ODBC linking just for the same consistency. So exactly thanks to KEXISQL we can and want to add case sensitive LIKE, see https://mail.kde.org/pipermail/kexi-devel/2015-May/000343.html -- #1 does not mean we reject the need for case-sensitive LIKEs. We can do that in 2.9! Now a question or an exercise: what grammar for that would be OK? My proposal: one that could fit the SQL known so far. <column> COLLATE CASE LIKE <pattern> 'CASE' would be the magical case-sensitive collation. We can go with CS or SQL_CS or anything like at https://msdn.microsoft.com/en-us/library/ms144250%28v=sql.105%29.aspx for example. But we fight for readability. SInce LIKE is case insensitive, I propose to close this wish or change it to "Add support for 'case sensitive' LIKE operator for SQL queries". |
When writing an SQL query in the SQL mode, a query using the keyword 'ILIKE' is declared invalid. The word 'ILIKE' is not bolded, unlike the word 'LIKE' which is bolded, nor does Kexi accept this as a valid query This is using, in my case, Postgresql 8.4 Reproducible: Always Steps to Reproduce: 1. open a new query in a database 2. construct a query on a table to use the term 'LIKE' (instead of the usual '=') 3. run the query or click the 'Check Query' button to validate it 4. change the term 'LIKE' to 'ILIKE' 5. run the query or click the 'Check Query' button to validate it Actual Results: In the case of the query containing 'ILIKE', then '5' above produces the following output EITHER The query does not run, returning the Warning box advising "The query you entered is incorrect" OR, respectively, The 'Check Query' button returns "The query is incorrect, syntax error" Expected Results: The query would perform, satisfactorily, returning results based upon a case-insensitive search. The postgresql operators ~~, *, !, are not accepted either. I have not yet tried the noted kludge: SELECT id FROM groups WHERE LOWER(name)=LOWER('Administrator') ILIKE would be preferred as a solution, being minimally editable from established queries. Operator data error means that a case-insensitive search is a desired solution in tracking down certain records. I cannot vouch for what happens using the visual query approach in the 'Design' mode since I never use this.