SQLite Forum

about the result 1='1' that sqlite behave different with mysql or oracle
Login

about the result 1='1' that sqlite behave different with mysql or oracle

(1.3) By els278935 on 2023-01-10 06:03:54 edited from 1.2 [source]

i just try a sql like this:

select * from test where id = 1 and 1='1'

in oracle or mysql ,result will return the record id = 1

but in sqlite ,the record id = 1 will not return .

i find this in docs:

https://www.sqlite.org/datatype3.html#sort_order

An INTEGER or REAL value is less than any TEXT or BLOB value.

STRICT Tables can solve this?

(2.1) By Gunter Hick (gunter_hick) on 2023-01-10 07:01:13 edited from 2.0 in reply to 1.3 [link] [source]

The integer 1 and the string '1' are not the same. This is intentional. See https://sqlite.org/datatype3.html

Edit: No, strict tables cannot change this. Your expression is comparing two literals. 

"Affinity is applied to operands of a comparison operator prior to the comparison according to the following rules in the order shown:

If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.

If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand.

Otherwise, no affinity is applied and both operands are compared as is."

(3) By els278935 on 2023-01-10 08:05:54 in reply to 2.1 [link] [source]

yes, 1 and the string '1' are not the same. previously we use oracle,and now we use sqlite for better perfermance。 in same sql,we usually like this select * from test where id = 1 and '%s'=1 it works in oracle,but when switch some business to sqlite ,problem comes。

actually,code like '%s'=1 is bad,it just works in mysql or oracle.

is there any suggestion? or the only thing we can do is change the code '%s'=1 to '%s'='1'

(4) By Gunter Hick (gunter_hick) on 2023-01-10 08:20:06 in reply to 3 [link] [source]

Why not stop after "where id = 1" and refrain from adding a condition that you hope will always be true to the end of an already perfectly good query?

I see this a lot in queries generated from Oracle forms applications; usually as "and 1=1".

(5) By els278935 on 2023-01-10 09:10:04 in reply to 4 [link] [source]

we use '%s' = 1 or '%s' = other num.the num is usually a dictionary. when the user click button for example ,the %s will be replace to real data。

'%s'=1 is not used to keep a always true condition。our sql is dynamic。

(6) By Chris Locke (chrisjlocke1) on 2023-01-10 09:16:48 in reply to 4 [link] [source]

I see this a lot in queries generated from Oracle

This is just terribly bad lazy programming. Rather than building a valid SQL string, it's 'quicker' to just append OR or AND statements to an existing SQL string. Urgh.

(9) By jose isaias cabrera (jicman) on 2023-01-10 13:55:19 in reply to 6 [link] [source]

This is just terribly bad lazy programming.

Well, I don't agree. Imagine you have a search form with a bunch of textboxes, and you allow users to enter in these textboxes strings to be searched. And the more strings they enter, the more restrictive the query (data-wise would be). I.e. In the last name textbox the user can type 'cabrera' and in the state textbox the user can type 'ny', but the rest of the textboxes are empty, when you write the select your select would start,

SELECT a.* FROM Project_List AS a
WHERE 1 = 1

and then a series of data from textboxes can be added to the select queries by doing something like this,

        if (txtPMLastName.text != null)
        {
          q ~= " AND lower(pmLName) = '" ~ std.string.tolower(txtPMLastName.text) ~ "' ";
        }
        if (txtClarityPM.text != null)
        {
          q ~= " AND lower(techPM) LIKE '%" ~ std.string.tolower(txtClarityPM.text) ~ "%' ";
        }
        if (txtState.text != null)
        {
          q ~= " AND lower(pmState) LIKE '%" ~ std.string.tolower(txtState.text) ~ "%' ";
        }

so the final query would have all of the data where the user entered text as part of the query. However, I do agree that 1 = '1' is not how I would do it.

(10) By Chris Locke (chrisjlocke1) on 2023-01-10 14:22:17 in reply to 9 [link] [source]

Well, I don't agree.

I refer you to my main point. It's lazy.

You can write it perfectly valid without the '1=1' part. Starting with '1=1' is superfluous. It doesn't add value to the query. It works without it. Its unnecessary.

(12) By jose isaias cabrera (jicman) on 2023-01-10 15:26:46 in reply to 10 [link] [source]

Please show me how you would create the WHERE part of the SELECT search without any extra programming code.

SELECT * FROM t WHERE
AND a = 'a'
AND b = 'b'
;
sqlite> select * from t WHERE
   ...> and a = 'a'
   ...> and b = 'b'
   ...> ;
Run Time: real 0.000 user 0.000000 sys 0.000000
Parse error: near "and": syntax error
  select * from t WHERE and a = 'a' and b = 'b' ;
                        ^--- error here

This is would error out. How you fill the query programmatically? I would appreciate it. Thanks.

(15) By Gunter Hick (gunter_hick) on 2023-01-10 16:03:52 in reply to 12 [link] [source]

There are many ways to write this.

Start with the basic SELECT statement. e.g. "SELECT * FROM t"

Note: You should not be using "SELECT *" for production code. This will come back and bite you later, for a multitude of reasons.

Start with an empty constraint clause.

Write a function that accepts a contraint to add:

function add_constraint(clause,constraint) :=
if (clause is empty)
    append "WHERE " (note: trailing space)
else
    append " AND " (note: leading and trailing space)
append constraint

Call add_constraint with the text of the constraint you want to add.

append the constraint clause to the basic statement.

So if nothing is filled in, and therfore nothing added, the statement will be the base statement.
If just one field is filled in, you will get "WHERE <constraint>"
If m or the just 1 field is filled in, you will get a proper clause beginning with "WHERE" and with " AND " between consecutive constraints.

Once you have that working, you can move on to <field> IN (<list>) constraints. Note that <field>=<value> is a much better (faster) constraint than <field> IN (<value>) with just 1 value.

(11.1) By Ryan Smith (cuz) on 2023-01-10 20:17:54 edited from 11.0 in reply to 9 [link] [source]

While "good" programming is highly contested and often personal, the example is precisely an example of bad/lazy programming, more resembling a copy-pasted set of commands.

I would go so far as to say it isn't programming, it's a written-out list of commands, more suited to a typewriter than a computer.

Somewhat more sane (though not perfect) would be something that removes the repetitive nature of it and the unneeded string segments and useless SQL filter checks, perhaps something pseudo-coded like:

var string query = 'SELECT * FROM table';
var string whereClause = '';


...

function void addFilter(string lhs, string rhs, bool anyStr) {
    if (lhs == null || rhs == null || rhs == '') return;
    if (whereClause != '') { whereClause += ' AND ' }
    if (anyStr)  { rhs = '%' + rhs + '%' }
    whereClause += ' lower(' + lhs + ') = ' + std.string.tolower(rhs);
    return;
}

addFilter('pmLName', txtPMLastName.text, False);
addFilter('techPM',  txtClarityPM.text,  True );
addFilter('pmState', txtState.text,      True );

if (whereClause != '') { query += ' WHERE ' + whereClause; }

...

Sure, more effort to make, but now the operational differences between the filtered items are much clearer to you and future viewers of your code, and it produces a query filter without several meaningless ghost items, if any. Adding more filtered items becomes significantly simpler and also, want to change how the where-clause is built? Now you have 1 single place to fix, not 3 places.

"Programming" is how we get a computer to do repetitive work for us. It's not much good for anything else.

(14) By jose isaias cabrera (jicman) on 2023-01-10 15:43:37 in reply to 11.0 [link] [source]

That is the problem. I don't know if last name textbox is filled. It may just the the state field filled, or the project number, or... I understand what you are saying, but, thanks for the explanation.

(18) By Ryan Smith (cuz) on 2023-01-10 20:07:57 in reply to 14 [link] [source]

Well yes, but I believe the proposed code handles that case very well. If any of the boxes are unfilled (making "rhs" either null or empty string), then that filter will not be added, and if in the end there are no filters added at all, the where clause is simply omitted. That is how I believe it should work.

(19) By jose isaias cabrera (jicman) on 2023-01-10 20:10:46 in reply to 18 [link] [source]

Thanks. I will give it a try. Appreciate the help. I have some down time this week.

(20) By Ryan Smith (cuz) on 2023-01-10 20:20:03 in reply to 19 [link] [source]

Oh ok, since you will actually use it, I decided to improve it a bit (edited the original) from my quick-fire initial reply. It's not tested, so prepare for something to glitch, but you should be able to easily correct any such mistake.

(17) By Keith Medcalf (kmedcalf) on 2023-01-10 17:31:32 in reply to 9 [link] [source]

That is pretty lazy (and bad) programming.

One would create a list of conditions for the user variables. These conditions would be joined by ' AND ' and prefixed with ' WHERE '. This means that if there were no conditions, there would be no where clause.

I do and did that all the time. Furthermore, the condition would be var == ? and the actual data would be bound (sanitized to be data).

Anyone who builds a command tail as you described (and it sounds like that is how Oracle is doing it) should be keelhauled and we will all stop for a toddy while the perp is halfway along the keel!

(8) By Ryan Smith (cuz) on 2023-01-10 09:27:24 in reply to 3 [link] [source]

... WHERE id = 1 AND CAST('%s' AS INT) = 1

(23) By anonymous on 2023-01-11 06:43:06 in reply to 8 [link] [source]

I like cast(), especially where clarity may be an issue, or to draw attention to the conversion. Yet there are times when a query could become cumbersome with many casts. In those cases, another option is....

WHERE id = 1 AND '%s'+0; -- make text numeric so it can compare to 1/0

(7) By Aask (AAsk1902) on 2023-01-10 09:22:20 in reply to 1.3 [link] [source]

(13) By Richard Hipp (drh) on 2023-01-10 15:37:38 in reply to 1.3 [link] [source]

I'm sorry this isn't working out for you.

I turns out that most SQL database engines think that 1='1' is true. I don't know why. SQLite is different. An integer is not a string, to SQLite's mind, and so 1='1' is false.

SQLite has been this way for 20 years. I don't think it is reasonable to change it at this point - too much legacy code might break.

The anomalous behavior has now been documented on the Quirks Page.

(16) By Spindrift (spindrift) on 2023-01-10 16:45:03 in reply to 13 [link] [source]

On the quirks page, within that new entry, "Destinguishes" could be spelt more correctly.

(21) By els278935 on 2023-01-11 02:29:36 in reply to 13 [link] [source]

may be oracle pamper programers, and other sql database follow。

now that sqlite engines think that 1='1' is false ,and sqlite results any integer less than any text. can sqlite returns some callback, for example to tell programers interger compare text is meaningless。 in sqlite SQL compare integer with text is wrong ,although sqlite returns results ,it's unpredictable for any business code.

sqlite just give a friendly tip to programers.

(22) By Keith Medcalf (kmedcalf) on 2023-01-11 04:53:18 in reply to 21 [link] [source]

See https://sqlite.org/datatype3.html for the friendly tips.