[MDEV-20509] select count(*) shows errors, maybe it's invalid Created: 2019-09-05  Updated: 2019-09-12  Resolved: 2019-09-12

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: 10.4.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Philip orleans Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux


 Description   

This select statement

SELECT count(*) FROM asterisk.npadata n LEFT JOIN america.americarocks a ON a.number=n.did WHERE n.state='TX' AND wireless=0 AND (n.reachable=1 OR n.residential=1) AND a.recordtype='Residential';

should never produce thousands of warnings like this:

show warnings
 
 Warning | 1292 | Truncated incorrect INTEGER value: ''

(thousands of times)
Both tables joined are RocksDB
one of the joined fields is bigint(20) unsigned while the oher is varchar(50).



 Comments   
Comment by Elena Stepanova [ 2019-09-11 ]

Why do you consider it invalid, if you are indeed comparing varchar with int?

Comment by Philip orleans [ 2019-09-11 ]

The comparison is allowed, conversion is transparent. A zero length string
should be ignored.

On Wed, Sep 11, 2019, 9:07 AM Elena Stepanova (Jira) <jira@mariadb.org>

Comment by Alexander Barkov [ 2019-09-12 ]

This script:

CREATE OR REPLACE TABLE t1 (a BIGINT);
 
INSERT INTO t1 VALUES (10),(20);
 
CREATE OR REPLACE  TABLE t2 (a VARCHAR(50));
 
INSERT INTO t2 VALUES ('10'),('20'),('');
 
SELECT * FROM t1, t2 WHERE t1.a=t2.a;
 
SHOW WARNINGS;

produces the following output:

+------+------+
 
| a    | a    |
 
+------+------+
 
|   10 | 10   |
 
|   20 | 20   |
 
+------+------+
 
2 rows in set, 2 warnings (0.000 sec)

with these warnings:

+---------+------+--------------------------------------+
 
| Level   | Code | Message                              |
 
+---------+------+--------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: '' |
 
| Warning | 1292 | Truncated incorrect DOUBLE value: '' |
 
+---------+------+--------------------------------------+

This is behavior is intentional. It gives a chance to the user to see that some records did not really contain numbers.

If you don't want to see these warnings, you can suppress them using a CONTINUE HANDLER:

DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE CONTINUE HANDLER FOR 1292 SET @w1292count=@w1292count+1;
 
  SET @w1292count=0;
 
  SELECT * FROM t1, t2 WHERE t1.a=t2.a;
 
END;
 
$$
 
DELIMITER ;

It produces the same result set but without warnings:

+------+------+
 
| a    | a    |
 
+------+------+
 
|   10 | 10   |
 
|   20 | 20   |
 
+------+------+
 
2 rows in set (0.002 sec)
 
 
 
Query OK, 0 rows affected (0.002 sec)

Note, you can see the number of warnings suppressed:

SELECT @w1292count;


+-------------+
 
| @w1292count |
 
+-------------+
 
|           1 |
 
+-------------+

If you don't even want to know the number of warnings suppressed, you can simplify the script to this:

DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE CONTINUE HANDLER FOR 1292 BEGIN END;
 
  SELECT * FROM t1, t2 WHERE t1.a=t2.a;
 
END;
 
$$
 
DELIMITER ;

Generated at Thu Feb 08 09:00:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.